6

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 =?

  • 4
    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
  • 1
    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
  • 1
    @Aaron: I was careful to use "you may get", not always. I agree with all the rest. – ypercubeᵀᴹ May 2 '12 at 16:54
  • 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
11

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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