[thelist] SQL query execution order
rudy
r937 at interlog.com
Mon Mar 25 16:32:01 CST 2002
> So, the question is (I suppose), how do I make sure that the
> conditional clauses run *before* the CONVERT statements
> happen so that my stored procedure won't try and convert the
> wrong data?
hi judah
i have this deal with database vendors -- i don't try to guess how their
optimizers work, and they don't try to guess what i really want in my
result set
i have two suggestions -- try moving row selection from WHERE into ON
conditions, and try parenthesizing your inner joins
e.g. change
FROM responses
INNER JOIN form_lookup
INNER JOIN questions
ON form_lookup.questionid = questions.questionid
ON responses.questionid = form_lookup.questionid
WHERE userid IN
( SELECT userid FROM usergroup_users
WHERE usergroupid = @usergroupid )
AND form_lookup.formid = @formid
AND ( questions.questionnumber BETWEEN @num1 AND @num2
AND questions.status = 1 )
to
FROM responses
INNER JOIN
form_lookup
INNER JOIN questions
ON form_lookup.questionid = questions.questionid
AND form_lookup.formid = @formid )
AND ( questions.questionnumber BETWEEN @num1 AND @num2
AND questions.status = 1 )
)
ON responses.questionid = form_lookup.questionid
WHERE userid IN
( SELECT userid FROM usergroup_users
WHERE usergroupid = @usergroupid )
rudy
More information about the thelist
mailing list