SQL Server Performance Forum – Threads Archive

looking for perf number between IN and OR

what is better in performance –this …. where
x in (1, 2) –or .. where
(x = 1 or x= 2) can you give me some link that give some information about performance numbers

ONe might think, that both are actually the same, since IN is a shortcut for multiple ORs. Do you see any difference in the execution plans?
I’m not aware of any performance number readily available. But see if this helps:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;843534
http://support.microsoft.com/default.aspx?scid=kb;EN-US;815183 —
Frank
http://www.insidesql.de

UNION ALL would be faster than either one. select column1
from table1
where x = 1
UNION ALL
select column1
from table1
where x = 2
MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Interesting, Derrick,
can you give a prove for this?
A quick test here showed a query cost of 50% for your suggestion, while IN and ORs took both 25%
??? —
Frank
http://www.insidesql.de

And what time did it run in on a big set? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Hm, interestingly your UNION ALL doesn’t run slower than the others, as one might think. However I can’t observe any consistent results here. Neither method does run always faster or not. Maybe you can share yours? —
Frank
http://www.insidesql.de

I’ll put some work into it tonight. We’ve used this at work a few times for really big result sets that were using IN or OR statements. It didn’t make any sense to me either, but a DBA I worked with shoved it down my throat and made me eat crow. It was rather disgusting. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Yes, it is suprising and at the same of limited practical use when you have a longer list of values of proceed. [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] – Frank http://www.insidesql.de</a>

]]>