SQL Server Performance Forum – Threads Archive

IN (SELECT …) slow

I’m producing a dynamic query. For purposes of modularity, I’d prefer to do a query using WHERE MyField IN (SELECT [ID] FROM #MyTable) but as a byproduct, the execution plan shows a table scan performed on a very large table. When I list out values in the IN clause… WHERE MyField IN (1,2,3) the query is very fast, no table scan. Is this just the way it’s gotta be?

---

Yep the IN (select…) is not guaranteed to use an index on MyField Try using a) assuming ID is not unique in #MyTable from MyTable, (select distinct [ID] from #MyTable) MyTempTable
where MyTempTable.ID = MyTable.MyField b) assuming it is from MyTable, #MyTable
where #MyTable.ID = MyTable.MyField
Cheers
Twan

---

Execution plan or Index Tuning, can’t work with auxiliars tables.
In those cases only you have your head. BTW:Agree with Twan, Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

---

I agree too, but I would change the join syntax to the ansi one (easier to read, more support) from mytable join #mytable on mytable.myfield = #mytable.id The way you were looking at it, it would likely run the subquery each for each record in the myfield table. Explicitly listing the fields eliminates the need to ready anything from a second table. The join is faster. I think, but you would have to try it, but exists is a bit faster than in with a subquery too. Chris

---

]]>