David Fowler still has a list:
This post comes off the back of my last, where I looked at issues caused by explicitly declaring a large number of values in an IN clause. The query processor ran out of internal resources and could not produce a query plan – When You’ve Managed to Confuse SQL With a Crazy IN Clause.
My suggestion was to put the values into a temp table and carry out a semi-join.
That got me thinking, which of the two methods would perform better.
I’m going to look at the two methods for different amounts of values and have a look at how they get on in terms of reads and total time.
Read on for the answer. If I’m surprised by anything in it, it’s that the threshold where temp tables out-perform the IN() clause is so high.