July 20, 2004 at 9:26 am
Hello everyone, I would appreciate help anyone can provide. I have a few stored procedures I am attempting to rewrite to optimize performance, and would like to know if there are other ways to write this query.
I am working with existing code as follows:
select rtrim(asmt.AssessmentAnswerVarchar) as "Data"
from acw_assessmentanswer asmt, acw_answerinfo ans
where asmt.answerinfoid = ans.answerinfoid
and assessmentid=@assessmentID and ans.fpwRecID=@fpwRecID
and multiid=0
UNION (
select rtrim(asmt.AssessmentAnswerVarchar)
from acw_assessmentanswer asmt, acw_multianswers mans
where asmt.multiid = mans.multiid
and assessmentid=@assessmentID and mans.fpwRecID=@fpwRecID
and mans.multiid<>0 )
The problem I have identified with these is the "asmt.answerinfoid = ans.answerinfoid" and "asmt.multiid = mans.multiid" lines, which require SQL Server to internally generate the cartesian product of the two tables and evaluate it, which takes longer and longer to perform as the size of the tables grows.
I've come up with what I think is a better way to get the same information:
SELECT RTRIM(asmt.AssessmentAnswerVarchar) as "Data"
FROM Acw_AssessmentAnswer asmt
WHERE asmt.AssessmentID=@assessmentID
AND asmt.AnswerInfoID IN
(SELECT AI.AnswerInfoID from Acw_AnswerInfo AI WHERE AI.FpwRecID=@fpwRecID
UNION
SELECT MA.AnswerInfoID from Acw_MultiAnswers MA WHERE MA.FpwRecID=@fpwRecID)
All the tests I've done come up with the same results, and on SQL 2000 we see greatly improved performance: execution times drop from 500-650 milliseconds down to 65-75 milliseconds. This is only for SQL 2000, however. When I tested the same code on SQL 7, it took almost twice as long to complete as the original code.
Can anyone think of a better way to write this?
Thanks, AaronChr
July 21, 2004 at 1:57 am
When you use a UNION clause it does an implicit SELECT DISTINCT.
If your two selects produce completely different records then change the UNION to UNION ALL.
Also, qualify the multiid in the first query.
I tend to use ANSI Standard SQL because I like to keep my relationship clauses separate from my filtering clauses but I don't think it makes any difference to the performance.
select rtrim(asmt.AssessmentAnswerVarchar) as "Data"
from acw_assessmentanswer AS asmt INNER JOIN acw_answerinfo AS ans
ON asmt.answerinfoid = ans.answerinfoid
where assessmentid=@assessmentID
and ans.fpwRecID=@fpwRecID
and asmt.multiid=0
What values can multiid take?
If it can only take positive values then use > rather than <>0 because it is faster.
If multiid is a simply 1 or 0 then use =
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply