Performance Of IN

Daniel Janik looks at how the IN clause behaves differently based on the number of items in the list:

As you can see the second query is much slower and the extra value in the IN caused late filtering. This is a limitation on some types of operators such as this clustered index scan.

There isn’t just a limitation of 15 input values. There’s also one at 64. On the 65th input value the list will be converted to a constant scan which is then sorted and joined. Most interestingly enough is that the list in my demo query is already sorted ascending.

Read the whole thing.

Related Posts

Character Columns And MAX Vs TOP+ORDER Differences

Kendra Little digs into a tricky performance problem: Most of the time in SQL Server, the MAX() function and a TOP(1) ORDER BY DESC will behave very similarly. If you give them a rowstore index leading on the column in question, they’re generally smart enough to go to the correct end of the index, and […]

Read More

Row Goals And Anti-Joins

Paul White continues his row goals series: The optimizer assumes that people write a semi join (indirectly e.g. using EXISTS) with the expectation that the row being searched for will be found. An apply semi join row goal is set by the optimizer to help find that expected matching row quickly. For anti join (expressed e.g. using NOT EXISTS) the optimizer’s assumption is that […]

Read More


November 2016
« Oct Dec »