Performance Q : IN Statement

This may be a "how long's a piece of string"-type question but I'm trying to
get a feel for the performance of the IN statement.
Broadly, we've some software which generates SQL for counting but we've hit
a situation where we could generate either re-engineer the SQL or simply
wrap an existing Select generated into a subselect and counting using an IN.
It's a minor change whereas re-engineering would be much more significant
piece of work
As a rule of thumb - Is IN slow?
I know it's probably a bit vague but I'm just after thoughts or perhaps a
link or 2 discussing this.
Thanks
SimonIN can be slower than using one of the alternatives, either a JOIN or an
EXISTS clause:
You can rewrite:
WHERE column IN (SELECT column FROM ...)
as either
INNER JOIN (SELECT column FROM ...) a
ON t.column = a.column
or
WHERE EXISTS (SELECT NULL FROM ... WHERE a.column = t.column)
You can then test which solution performs the best in your situation.
Note that the inner join will return multiple rows if the values in column
are not unique in the derived table.
Jacco Schalkwijk
SQL Server MVP
"Simon Woods" <simonDELETECAPSjwoods@.hotmaiIl.com> wrote in message
news:eq3oGjECFHA.4028@.TK2MSFTNGP15.phx.gbl...
> This may be a "how long's a piece of string"-type question but I'm trying
> to
> get a feel for the performance of the IN statement.
> Broadly, we've some software which generates SQL for counting but we've
> hit
> a situation where we could generate either re-engineer the SQL or simply
> wrap an existing Select generated into a subselect and counting using an
> IN.
> It's a minor change whereas re-engineering would be much more significant
> piece of work
> As a rule of thumb - Is IN slow?
> I know it's probably a bit vague but I'm just after thoughts or perhaps a
> link or 2 discussing this.
> Thanks
> Simon
>|||You're right - it is a bit "how long's a piece of string"!
Something that you may find useful is that positive criteria is much faster
than negative, so IN (1,2,3,6,7,8,9,10) should always be quicker than NOT IN
(4,5).
IN (1,2,3) should not be any slower than =1 OR =2 OR =3, in fact, it should
actually be quicker!
Key to quick querying with criteria is your indexing.
Hope this helps
Paula
"Simon Woods" wrote:

> This may be a "how long's a piece of string"-type question but I'm trying
to
> get a feel for the performance of the IN statement.
> Broadly, we've some software which generates SQL for counting but we've hi
t
> a situation where we could generate either re-engineer the SQL or simply
> wrap an existing Select generated into a subselect and counting using an I
N.
> It's a minor change whereas re-engineering would be much more significant
> piece of work
> As a rule of thumb - Is IN slow?
> I know it's probably a bit vague but I'm just after thoughts or perhaps a
> link or 2 discussing this.
> Thanks
> Simon
>
>|||Paula,
IN (1,2,3) is exactly the same as =1 OR =2 OR =3. IN is just short hand for
multiple OR statements. You can see that if you create a table with a CHECK
constraint that contains an IN clause. When you generate the script from
Query Analyzer, the IN clause will be changed into multiple OR statements.
Jacco Schalkwijk
SQL Server MVP
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:ECC51DC8-3BB3-45B3-A73B-954F9642D157@.microsoft.com...
> You're right - it is a bit "how long's a piece of string"!
> Something that you may find useful is that positive criteria is much
> faster
> than negative, so IN (1,2,3,6,7,8,9,10) should always be quicker than NOT
> IN
> (4,5).
> IN (1,2,3) should not be any slower than =1 OR =2 OR =3, in fact, it
> should
> actually be quicker!
> Key to quick querying with criteria is your indexing.
> Hope this helps
> Paula
> "Simon Woods" wrote:
>