Command using 'in' subquery is slow | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Command using ‘in’ subquery is slow

Hi all, I have a slow command, and I’m wondering if anyone knows a better way. By slow I mean up to 30 seconds. The slow command is: Delete from Table1
where MyField in
(Select ThyField from Table2); I’ve also tried: Delete from Table1
where MyField = ANY
(Select ThyField from Table2); A similar select version of the same runs pretty fast: Select MyField from Table1
where MyField in
(Select ThyField from Table2); Table1 has a half million records. Table2 generally has less than 1000 records. The query usually returns only a handful of records to delete. So the problem is not that I’m waiting for a huge number of records to delete. I’ve also tried selecting the records, putting them in an array, and deleting them one by one. As you can imagine, this also takes too long. Anyone have any ideas to help me? Thanks!
Table2 t2
INNER JOIN Table1 t1 ON t2.ThyField = t1.MyField What are your indexes like on these tables? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
What if you use-
WHERE ThyField = MyField) Gaurav
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Hi Gaurav & Derrick, Both of your options actually took longer to process. Derrick’s took 2.4 times as long, whereas Gaurav’s took 1.2 times as long. I have a single index on MyField and ThyField on their respective tables. I have solved the problem, in a way. I realized that I could add further constraints to the delete command, reducing the number of records it had to consider. Thanks again for your help. It was good to be exposed to alternative subquery methods. I’m sure I will use these in the future. –Bob