I have a query in T-SQL 2012 (sql server) that works but this clause in WHERE is causing query running 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.

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:

    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.