CODEWITHSUNDEEP
sql-serverazure-sql-server
Kamran
Kamran

Reputation: 1380

Slow query when having too many conditions

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.

Upvotes: 0

Views: 549

Answers (1)

Uueerdo
Uueerdo

Reputation: 15941

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)

Upvotes: 2

Related Questions