Edit Question - KnowledgeHub

Title Body

I have problems with SQL performance. For sudden reason the following queries are very slow: I have two lists which contains Id's of a certain table. I need to delete all records from the first list if the Id's already exists in the second list: DECLARE @IdList1 TABLE(Id INT) DECLARE @IdList2 TABLE(Id INT) -- Approach 1 DELETE list1 FROM @IdList1 list1 INNER JOIN @IdList2 list2 ON list1.Id = list2.Id -- Approach 2 DELETE FROM @IdList1 WHERE Id IN (SELECT Id FROM @IdList2) It is possible the two lists contains more than 10.000 records. In that case both queries takes each more than 20 seconds to execute. The execution plan also showed something I don't understand. Maybe that explains why it is so slow: I Filled both lists with 10.000 sequential integers so both list contained value 1-10.000 as starting point. As you can see both queries shows for @IdList2 Actual Number of Rows is 50.005.000!!. @IdList1 is correct ( Actual Number of Rows is 10.000) I know there are other solutions how to solve this. Like filling a third list instaed of removing from first list. But my question is: Why are these delete queries so slow and why do I see these strange query plans?

Tags (comma-separated) Cancel