How to increase performance of this query?

Question

I have an SQL query, it is running on MSSQL 2008 R2 View vMobileLastMobileHistory has about 1000 rows and select * from vMobileLastMobileHistory is taking 0.2 sec

but this query is taking 5 seconds, how can I optimize this code? (I think the problem is INTERSECT but I dont know how change this)

SELECT DISTINCT *
FROM
    (
        SELECT  vMobileLastMobileHistory.* 
        FROM    vMobileLastMobileHistory
            LEFT OUTER JOIN MobileType_DomainAction ON 
                MobileType_DomainAction.tiMobileType = vMobileLastMobileHistory.tiMobileType
        LEFT OUTER JOIN MobileType_User ON 
            MobileType_User.MobileID = MobileType_DomainAction.ID
        WHERE MobileType_User.UserID = @UserID OR @UserID = - 1 

        INTERSECT

        SELECT  vMobileLastMobileHistory.*  
        FROM    vMobileLastMobileHistory
            LEFT OUTER JOIN dbo.Region_User ON 
                dbo.vMobileLastMobileHistory.strRegion = dbo.Region_User.strRegion
        WHERE Region_User.iSystemUser = @UserID OR @UserID = - 1

        INTERSECT

        SELECT vMobileLastMobileHistory.* 
        FROM vMobileLastMobileHistory
        LEFT OUTER JOIN Contractor_User ON 
            vMobileLastMobileHistory.strContractor = Contractor_User.strContractor
        WHERE Contractor_User.iSystemUser = @UserID OR @UserID = - 1
    )

Answer 1

Most of time Intersect and Inner Join will be same. You are not share your data, so based on my knowledge and this link, I just replace intersect query into Inner join query as :

--I think you don't need distinct upper query. If you have issue inform me.

        SELECT DISTINCT vml.* 
        FROM   vMobileLastMobileHistory vml
            LEFT OUTER JOIN MobileType_DomainAction mtda ON mtda.tiMobileType = vml.tiMobileType
            LEFT OUTER JOIN MobileType_User ON MobileType_User.MobileID = mtda.ID
            LEFT OUTER JOIN dbo.Region_User ON dbo.vml.strRegion = dbo.Region_User.strRegion
            LEFT OUTER JOIN Contractor_User ON vml.strContractor = Contractor_User.strContractor
        WHERE 
        (MobileType_User.UserID = @UserID 
        and Region_User.iSystemUser = @UserID or Contractor_User.iSystemUser = @UserID
        ) OR @UserID = - 1        

Answer 2

The problem is that if you have any indexes on your iSytemUser columns, the optimise is unable to use them because it has to account for a specific userID being passed, or returning all results, it would be better to logically separate your two cases. In addition, since you don't care about any columns in the auxiliary tables, you could use EXISTS in your case of specific users to take advantage of a semi join:

IF (@UserID = -1)
BEGIN
    SELECT  DISTINCT *
    FROM    vMobileLastMobileHistory;
END
ELSE
BEGIN
    SELECT  DISTINCT *
    FROM    vMobileLastMobileHistory AS mh
    WHERE   EXISTS 
            (   SELECT  1 
                FROM    Contractor_User AS cu 
                WHERE   cu.strContractor = mh.strContractor 
                AND     cu.iSystemUser = @UserID
            )
    AND     EXISTS 
            (   SELECT  1 
                FROM    Region_User AS ru 
                WHERE   ru.strRegion = mh.strRegion 
                AND     ru.iSystemUser = @UserID
            )
    AND     EXISTS 
            (   SELECT  1 
                FROM    MobileType_DomainAction AS da 
                        INNER JOIN MobileType_User AS mu
                            ON mu.MobileID = da.ID
                WHERE   da.tiMobileType = mh.tiMobileType 
                AND     mu.iSystemUser = @UserID
            );

END 

Now you can have two execution plans for each case (returning all results, or for a specific user), in each case you only need to read from vMobileLastMobileHistory once, and you also limit the sorts required by removing the INTERSECT and replacing with 3 EXISTS clauses.

If they don't already exist then you may also which to consider some indexes on your tables. A good way of finding out what indexes would help is to run the query in SQL Server Management Studio with the option "Show Actual Execution Plan" enabled, this will then show you any missing indexes.