[AccessD] Optimizing queries with in() - SQL Server 2005

John,

I think that an IN() and a bunch of ORs are roughly equivalent.

Try selecting the values you want in a sub query and (inner) joining it to
the table you want to limit. E.g.

select foo.*
from foo 
inner join (select value_field from foo2 where choice = 'Y') lim
on foo.value_field = lim.value_field

If you use the limitation in more than one place, or just for readability,
try the With clause. E.g.

With limiting_values 
as (select value_field from foo2 where choice = 'Y')
select foo.*
from foo 
inner join limiting_values lim
on foo.value_field = lim.value_field

If you check the cost of both approaches I think you'll find that the IN/OR
approach causes a full table scan while limiting through a join avoids it.

I frequently do this when I've set the foo2.choice field using code and use
the values to limit another larger table. (Note: foo2 holds all possible
selection values for the value_field.)

Hope this helps,
Fred

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, November 21, 2007 4:05 PM
To: dba-sqlserver at databaseadvisors.com; 'Access Developers discussion and
problem solving'
Subject: [AccessD] Optimizing queries with in() - SQL Server 2005

I have a lot of queries where I have to look for multiple values.  I have
been using an IN() clause but I am advised that is very slow.  OTOH I built
two queries that use an IN() in one query and a bunch of ORs in the other
and over a 56 million record table they were both so fast I couldn't see the
difference (Indexed column, containing values 1-9 and A-T (under 2 sec)

Any comments?

John W. Colby
Colby Consulting
www.ColbyConsulting.com 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com