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

Thanks Fred.  I have situations where for example 

Where Income IN('A','B','C','D','E')

The values in the field range from 0-9 and A-T.  The obvious solution is to
"prep" the field by changing the 'A' to 10, 'B' to 11 etc and then doing a
numeric comparison.  Unfortunately the data owner needs to come on board and
do a conversion as well so that they can understand and ask for 11 when they
were used to asking for 'A'.

In any case, this is a common occurrence in my data.  The values are always
characters (ascii) as opposed to numbers, even when they are numeric
characters.  This is that huge table I deal with where there are 600 fields
of data, pulled in from text files.  I have other fields like (boat) hull
type where the values are 'A' (aluminum), 'R' (Rubber), 'F' (Fiberglass) etc
so there is just no getting around some sort of IN() or =X or =Y kind of
solution.

I am doing the subquery route however.  Today I had a case where I had to do
an Income IN('A','B'..'T') and then AND that with another field where there
were ORs - PresenceOfChildren11-13 IN('1','3','5','7') OR
PresenceOfChildren16-17 IN('1','3','5','7').  It just SEEMS to make sense to
do the Income IN() as a separate query and then inner join to the
PresenceOfChildren query to create the AND effect.

Whoever created the data obviously didn't understand databases and how to
optimize the data for the ability to retrieve results efficiently.  It's
just a PITA, the table is huge (600 fields wide) and over 50 million records
to sort through.  "Prepping" the table over time (one field at a time as it
is called for) will work and I will probably do that.

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Fred Hooper
Sent: Wednesday, November 21, 2007 10:25 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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


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