Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm looking at a stored proc that another guy has written and every WHEN clause is using an IN statement instead of =. My gut tells me this isn't as fast, efficient, etc.

For example:

CASE WHEN a.Indicator in ('Yes') then 'something'
     WHEN a.STATUS in (0) then 'something 0'
     WHEN a.STATUS in (1) then 'something 1'
     WHEN a.STATUS in (2) then 'something 2'
     WHEN a.STATUS in (3,4,5,6,7,8) then 'something big'
END

Is it faster to use IN instead of =?

share|improve this question
3  
Check the query plan for both, and I can just about guarantee that you'll see the same result. It's just syntax, not semantics. –  Jon of All Trades May 2 '12 at 16:45
    
I agee with@Jon. You may get improvement in performance though, if you can convert those CASE clauses into joins. –  ypercube May 2 '12 at 16:46
1  
@ypercube I think performance difference depends on a lot of factors - I don't think you should make a blanket statement that joins are always going to outperform constant scans (I'm assuming you're referring to a case like the above where maybe Status values are stored in a table - will depend on indexes, stats, size of status table, etc). –  Aaron Bertrand May 2 '12 at 16:52
    
@Erik just a semantic thing - CASE is an expression, not a statement. Thinking about it as a statement gets people into trouble, as it implies that it can be used for control of flow (as it is in other languages like VB). –  Aaron Bertrand May 2 '12 at 16:53
1  
@ypercube yes, but if you read it a certain way you seem to imply that performance can't get worse, and it certainly could depending on the scenario. I've often removed superfluous joins to static, 5-row lookup tables and hard-coded the values in CASE instead, and it simplified the plan and improved query performance. So I'm just suggesting that moving those constants out to a join may be better, but it may be worse. –  Aaron Bertrand May 2 '12 at 16:58

1 Answer 1

up vote 9 down vote accepted

The following construct:

c IN (x, y, z)

Gets extrapolated to:

c = x OR c = y OR c = z

It will logically follow that c IN (x) extrapolates to c = x. Therefore the first comment is absolutely right: you will not see a difference in performance except at the very edge case (where the size of the query text is actually impacted sufficiently by the choice), but even there it is hard to fathom the performance difference being worth mentioning.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.