Very slow DELETE query

Question

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?

Answer 1

Add a Primary key to your table variables and watch them scream

DECLARE @IdList1 TABLE(Id INT primary Key not null)
DECLARE @IdList2 TABLE(Id INT primary Key not null)

because there's no index on these table variables, any joins or subqueries must examine on the order of 10,000 times 10,000 = 100,000,000 pairs of values.