0

1- what is the performance difference of below commands? 

 ... Where ID in (1,2,3)


... where ID = 1 OR ID = 2 OR ID = 3

2- where is performance difference of below commands(condition priority)? 

... where Condition1 and condition2  ->  ... where condition2 and condition1
... where Condition1 OR condition2  ->  ... where condition2 OR condition1
mehdi lotfi
  • 11,194
  • 18
  • 82
  • 128

2 Answers2

3

There is no difference between the below two type; since anyways, at runtime your DB engine will flatten the IN clause to an OR'd stack. So IN(1,2,3) will get expanded to col = 1 OR col = 2 OR col = 3

... Where ID in (1,2,3)   

... where ID = 1 OR ID = 2 OR ID = 3
Rahul
  • 76,197
  • 13
  • 71
  • 125
1

I don't think there is a signficant difference in performance. If you look at the execution plans, SQL Server embeds the filtering in the data loading step. This is occurring when the data is read from disk and loaded into the disk pages. A few cycles spent making one faster than the other is going to have basically no effect compared to the vagaries of I/O.

One typical implementation of the "in" operator would be as a small hash table. For three elements, this could easily be slower than sequential comparisons. However, for more comparisons the "in" approach would be faster. This would be especially true for numbers, since built in hardware comparisons wouild be quite fast. Also remember, though, that the compiler knows what is in the "in" list, so it can choose to do sequential comparisons if the database designers think that is a worthwhile optimization. I doubt they would think so, though.

To respond to Aaron's comment. My belief is based on the two errors that IN can return:

Error 8623: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

Error 8632: Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

(http://msdn.microsoft.com/en-us/library/ms177682.aspx)

I am not familiar with these errors on logical formulations on the WHERE clause. Hence, I made the perhaps rash assumption that the underlying implementation is using hash tables.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Why do you think an IN and OR aren't translated to the same underlying operation by SQL Server, regardless of the number of values being compared? Do you have any evidence to show these differences in performance? – Aaron Bertrand Jun 08 '12 at 12:22
  • It is a faint recollection about handling large "in" sets. Am I wrong? – Gordon Linoff Jun 08 '12 at 12:28
  • Why do you think if you write OR 15,000 times it can't produce the same error? Remember too that we're talking about the performance difference between queries that are actually able to run. – Aaron Bertrand Jun 08 '12 at 12:34
  • [See Remus's answer here](http://stackoverflow.com/questions/1869753/maximum-size-for-a-sql-server-query-in-clause-is-there-a-better-approach/1869810#1869810) RE: error message. – Martin Smith Jun 08 '12 at 12:38
  • The errors are documented for the in clause and not for other clauses. In any case, the point of my answer is that even if there is a difference, it is likely to have little noticeable effect on performance. Do you know of a good source of information on the SQL Server optimizer_ – Gordon Linoff Jun 08 '12 at 12:39
  • 1
    @GordonLinoff - Are you familiar with [SQL Kiwi's blog?](http://sqlblog.com/blogs/paul_white/archive/2012/04/28/query-optimizer-deep-dive-part-1.aspx). One of the most informative sources I have found. – Martin Smith Jun 08 '12 at 12:44