I have a query in T-SQL (for SQL Server 2012) that works, but this clause in WHERE is causing the query to run really slow. The rest of the query is very simple (I have tested it without having this clause and it is fast without it but we need it):

AND (@ViewAll = 1 
     OR EXISTS (SELECT TOP 1 1 
                FROM CustomerUser
                WHERE CustomerUser.UserId = @Id
                  AND (CustomerUser.AllCustomer = 1 OR 
                       CustomerUser.CustomerId = SalesOrder.CustomerId)
                  AND (CustomerUser.AllFinalCustomer = 1 OR 
                       CustomerUser.FinalCustomerId = SalesOrder.FinalCustomerId))        
     OR Customer.ResponsibleUserId = @Id)
    

BUT it is extremely slow when CustomerUser.AllCustomer = 1 or CustomerUser.AllFinaCustomer = 1 are false, because query must evaluate many results for the other conditions. Yes, the customerid is indexed.

I have tried with a temporary table that shows the same results but it is not working, it is still slow even to show only 14 records.

INSERT INTO #CustomerUserAssociations (CustomerId, FinalCustomerId)
SELECT
    CU.[CustomerId],
    CU.[FinalCustomerId]
FROM
    {CustomerUser} CU
WHERE
    CU.UserId = @Id
    AND (CU.AllCustomer = 1 OR CU.CustomerId IS NOT NULL)
    AND (CU.AllFinalCustomer = 1 OR CU.FinalCustomerId IS NOT NULL)

and in WHERE clause:

AND (@ViewAll = 1 
     OR EXISTS (SELECT 1 FROM #CustomerUserAssociations CA 
                WHERE (CA.CustomerId = SalesOrder.CustomerId
                       OR CA.FinalCustomerId = SalesOrder.FinalCustomerId)
        )
        OR Customer.ResponsibleUserId = @Id
    )

DROP TABLE #CustomerUserAssociations

I have tried to use LEFT JOIN and it is slow too. Any ideas to boost the query? Thanks.

I will add later the execution plan with some data. It will take some time as I do not have the SQL Management Studio.

INDEXES:
AutoIndex_CustomerId – Sales Order table and CustomerUser table
AutoIndex_FinalCustomerId – Sales Order table and CustomerUser table

Unfortunately I do not have a way to see a execution plan. I know how to do it in SQL Managemenent Studio.
Nevertheless, it is pretty clear that to read the query takes a while (over 10 seconds for 14 records) even with the temporary table.

Using a LEFT JOIN like this:

LEFT JOIN CustomerUser CU ON
    CU.UserId = @Id
    AND (CU.AllCustomer = 1 OR CU.CustomerId = SalesOrder.CustomerId)
    AND (CU.AllFinalCustomer = 1 OR CU.FinalCustomerId = SalesOrder.FinalCustomerId)

AND IN where…

WHERE
 
        @ViewAll = 1
        OR CU.UserId IS NOT NULL
        OR Customer.ResponsibleUserId = @Id

it is still slow.

FULL QUERY with temporary table:

INSERT INTO #CustomerUserAssociations (CustomerId, FinalCustomerId)
SELECT
    CU.[CustomerId],
    CU.[FinalCustomerId]
FROM
    {CustomerUser} CU
WHERE
    CU.UserId = @Id
    AND (CU.AllCustomer = 1 OR CU.CustomerId IS NOT NULL)
    AND (CU.AllFinalCustomer = 1 OR CU.FinalCustomerId IS NOT NULL)

SELECT SalesOrder.*


FROM SalesOrder
WHERE SalesOrder.Company = @CompanyId

    AND (
        @ViewAll = 1 
        OR EXISTS (
            SELECT 1 FROM #CustomerUserAssociations CA 
            WHERE 
                (CA.CustomerId = SalesOrder.CustomerId
                 OR CA.FinalCustomerId = SalesOrder.FinalCustomerId)
        )
        OR Customer.ResponsibleUserId = @Id
    )

DROP TABLE #CustomerUserAssociations