WHERE clause optimization

9 Jun 2006 8:09 PM

Sericinus hunter

Consider the following WHERE clause:

   ...WHERE (Col = 1) OR (Col IN (SELECT OtherCol FROM OtherTable))

Would it be different performance-wise from the following:

   ...WHERE Col IN (SELECT 1 UNION SELECT OtherCol FROM OtherTable)

    Thank you.

9 Jun 2006 8:27 PM

Maxi

Hi, compare the Stimated planned into Query Analizer.
Using OR clausule is generaly most slowly in query :(

--

--------------------------
Salu2
Maxi [MVP SQL SERVER]
www.sqlgurus.org

Show quote

"Sericinus hunter" <serh***@flash.net> escribió en el mensaje
news:8Akig.145584$F_3.126501@newssvr29.news.prodigy.net...
> Consider the following WHERE clause:
>
>   ...WHERE (Col = 1) OR (Col IN (SELECT OtherCol FROM OtherTable))
>
> Would it be different performance-wise from the following:
>
>   ...WHERE Col IN (SELECT 1 UNION SELECT OtherCol FROM OtherTable)
>
>
>    Thank you.

9 Jun 2006 10:32 PM

Erland Sommarskog

Sericinus hunter (serh***@flash.net) writes:

> Consider the following WHERE clause:
>
> ...WHERE (Col = 1) OR (Col IN (SELECT OtherCol FROM OtherTable))
>
> Would it be different performance-wise from the following:
>
> ...WHERE Col IN (SELECT 1 UNION SELECT OtherCol FROM OtherTable)

There is a chance that SQL Server will avoid the subquery if Col is 1,
if you use the former. In any case, being obscure is rarely a good
idea.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

10 Jun 2006 3:35 PM

Sericinus hunter

Erland Sommarskog wrote:

> Sericinus hunter (serh***@flash.net) writes:
>> Consider the following WHERE clause:
>>
>> ...WHERE (Col = 1) OR (Col IN (SELECT OtherCol FROM OtherTable))
>>
>> Would it be different performance-wise from the following:
>>
>> ...WHERE Col IN (SELECT 1 UNION SELECT OtherCol FROM OtherTable)
>
> There is a chance that SQL Server will avoid the subquery if Col is 1,
> if you use the former. In any case, being obscure is rarely a good
> idea.

Thank you, Erland, that's what I originally thought too: engine
may not even get to the subquery at all.
And I agree, clean writing is important also.