September 11, 2003 at 2:01 pm
Is there a way to improve the following COUNT(*) query:
SELECT Count(*) AS NumberOfRecords
FROM table1 AS t1
INNER JOIN ((((table2 AS t2
LEFT JOIN table3 AS T3 ON t2.empID = t3.empID)
LEFT JOIN table4 AS t4 ON t2.CompanyID = t4.CompanyID)
LEFT JOIN Table5 AS t5 ON t5.TypeID = t2.TypeID)
LEFT JOIN Table6 as t6 on t6.SampleID = t2.SampleID) ON t1.ID = t2.ID
WHERE
((C1.FEIN LIKE @FEIN) OR (C1.FEIN Is NULL))
AND t2.FirstName LIKE @FirstName
AND ((t2.MiddleInt LIKE @MiddleInit) OR (t2.MiddleInt Is Null))
AND t2.LastName LIKE @LastName
This is really resource intensive and time consuming. Could you suggest how
to better write this to improve the performance?
Arthur Lorenzini
Arthur Lorenzini
September 11, 2003 at 2:51 pm
Does the like have a leading wildcard? That will cost a lot more than just trailing wildcard. Do you have indexes on the join columns? Looked at a query plan? How long does it take to run and how much do you need to improve it?
Andy
September 11, 2003 at 3:33 pm
You might consider splitting this into two queries...one with C1.FEIN LIKE @Fein - the other with C1.FEIN IS NULL.
I have found the OR condition to be the root of many performance problems as it tends to cause scans instead of seeks.
Guarddata-
P.S. Perhaps the same applies to the MiddleInit comparison - depends on your indexes.
Edited by - guarddata on 09/11/2003 3:34:38 PM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply