[thelist] SQL query execution order

[Judah McAuley]

rudy wrote:
> it would help if i could cut & paste without making so many errors
>
> try this --
>
>         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 )

Thanks.  Didn't actually work, but I appreciate the effort.  Looking at
the estimated execution plan, SQL Server seems determined to do the
CONVERT's before trimming down the data set because none of my
conditional logic involves the responses table (the table I'm selecting
from).  I'm guessing that the optimization engine assumes that normal
queries have conditionals about the table you're selecting from.  In my
case, all of my conditional logic applies to tables that are only
indirectly related to the table I'm selecting from.  Probably a sign of
poor database design.  I didn't design the db and I'm not always happy
with how it does things, but oh well.

I did end up solving my problem, although at the cost of adding an
additional statement to the query.  I know that all of the valid numeric
responses are in the 1 to 9 range, so I added a clause to the beginning
of my WHERE statement like so:

WHERE responses.response LIKE '[0-9]' AND (blah blah blah)

So now the query engine does a filter on the responses table prior to
running the CONVERT function.  Not an optimal solution since the
responses table is the largest in the db, but it works for now.

Thanks,
Judah