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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy