Deletion with join/Exists clause

Question

Hi,

I have two tables A and B. Table A is having 55 Million records and B is having 0.3 Million records. I am deleting records from A by joining with B like below.

DELETE
FROM A
INNER JOIN B
ON A.ID = B.ID

In both the tables ID have Indexes.

The above query running longer. I am planning to use Exists clause in that rather than join . Is it improves performance? Else can you guys any other better way.

DELETE  
FROM A
WHERE   EXISTS (
    SELECT  B.DID
    FROM    B
    WHERE   B.DID = A.DID
    )

Thanks in advance,

Gangadhar

Answer 1

The EXISTS should improve it, but the biggest problem is normally the number of affected rows and the necessary log IO. When this number is really high, then use a batch delete:

DECLARE @Sample TABLE ( ID INT );
DECLARE @Counter INT = 0;

INSERT INTO @Sample
	SELECT TOP ( 1000000 )
		ROW_NUMBER() OVER ( ORDER BY A.number) 
	FROM master.dbo.spt_values A, master.dbo.spt_values B;

WHILE EXISTS( SELECT * FROM @Sample WHERE ID > 10 )
BEGIN
  SET @Counter += 1;
  SELECT @Counter, COUNT(*) FROM @Sample;
  BEGIN TRANSACTION;   
  DELETE TOP ( 2500000 ) FROM @Sample WHERE ID > 10;
  COMMIT TRANSACTION;
END;

SELECT COUNT(*) FROM @Sample;

Answer 2

I guess both queries perform equal, and have the exact same execution plan.

Al also guess SQL Server will scan table A entirely. SQL Server has two options to execute the query:

• Build a hash table from B table. and perform a hash join with A. This would require a full scan on A and a full scan on B.
• Perform a merge join. This would require a full scan on A and B too.
• Scan B table, and for each row in B seek the matching row in A. Performing a nested loop.

I think first  and second options are cheaper. Because, 0.3 million seeks would be more expensive.