locked
Deletion with join/Exists clause RRS feed

  • 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


    Friday, January 31, 2014 11:39 AM

All replies

  • 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;

    Friday, January 31, 2014 12:46 PM
  • 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.



    Friday, January 31, 2014 12:47 PM