[dba-SQLServer] Processing diverse where clauses

INs are bad policy, typically, unless you have a very small number of
targets. I wrote about this problem at TechRepublic a while ago. I may not
have the article handy, but you can probably find it there. Go to
www.techrepublic.com and search for "Arthur Fuller IN()". That might work.
Else just search for my name and then browse.

Anyway, the article concerns a better way to do the IN part. The problem
with IN is that it almost always forces a table-scan, which in your case
will give you enough time to move to another country and back before it's
done. The article shows how to construct a temp-table from the IN clause and
then do a join to the actual table using the temp table. Especially when
your IN clause could have lots of values, this approach is way faster.

I'll dig now for the article, but I'm fairly certain that it was one of the
things wiped out when I lost a hard disk a month ago or so. If I find it
I'll send it off-list.

Arthur


On 5/15/07, jwcolby <jwcolby at colbyconsulting.com> wrote:
>
> I am processing a query where a set of N fields have any value, AND one
> specific field has an IN() clause, i.e. a ton of codes possible.
>
> Is it more efficient to build up a pair of queries, SELECT PKID from tblX
> WHERE FldA in('A','B'...), and another query where the "OR" fields are
> gathered?  Or just use one big query?  I hate to even press the
> button...  I
> am having to test with a TOP 100 kind of thing as it is.
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>