Slow query when having too many conditions

Question

I have this simple query:

SELECT distinct top 100  A.[Number]
FROM [Section] AS A
    LEFT JOIN [Customers] AS c  ON c.ARef = A.Number
    LEFT JOIN [Guides] AS G  ON G.CustomerId = c.CustomerId
    LEFT JOIN [Telephones] AS T  ON T.CustomerId = c.CustomerId
    LEFT JOIN [Epts] AS E  ON E.CustomerId = c.CustomerId
    LEFT JOIN [Emails] AS Em  ON Em.CustomerId = c.CustomerId
    LEFT JOIN [Addresses] AS Ad ON Ad.CustomerId=C.CustomerId
WHERE 
 A.SaloonId= 400
 AND (         
    A.Number= @term OR c.Surname = @term OR c.FirstName = @term 
    ----
    OR Ad.Postcode = @term
    OR G.CategoryRef= @term 
    OR T.PhoneNumber = @term 
    OR E.Code= @term 
    OR Em.EmailAddress = @term 
 )

All the fields included in the where section have indexes which very low fragmentation percentage.

If we execute the query for a term, it takes more than 20 seconds but if I remove any random line in the last section (after "----") it takes less than 1 seconds.

Of course you have reviewed the execution plan and you have appropriate indexes..... — Missy, Feb 04 '20 at 00:31

score 2 · Accepted Answer · answered Feb 03 '20 at 23:52

I would try moving those conditions to their respective JOINs and then replacing them in the WHERE with conditions like OR Ad.CustomerId IS NOT NULL. This would reduce the join matches and decrease the amount of intermediate results those join conditions must be evaluated against.

Alternatively, since none of the data from most of those tables is actually returned in the results, I would consider replacing things like

...
LEFT JOIN [Guides] AS G  ON G.CustomerId = c.CustomerId 
... 
WHERE 
... 
OR G.CategoryRef = @term

with OR c.CustomerId IN (SELECT CustomerId FROM Guides AS G WHERE G.CategoryRef= @term)

If the criteria on A significantly reduces the number of C, I would even consider using correlated subqueries like OR EXISTS (SELECT * FROM Guides AS G WHERE G.CategoryRef= @term AND G.CustomerId = C.CustomerId)

Slow query when having too many conditions

1 Answers1