CODEWITHSUNDEEP
sqlsql-servert-sql
gd000
gd000

Reputation: 205

Batch deletion correctly formatted?

I have multiple tables with millions of rows in them. To be safe and not overflow the transaction log, I am deleting them in batches of 100,000 rows at a time. I have to first filter out based on date, and then delete all rows less than a certain date.

To do this I am creating a table in my stored procedure which holds the ID's of the rows that need to be deleted:

I then insert into that table and delete the rows from the desired table using loops. This seems to run successfully but it is extremely slow. Is this being done correctly? Is this the fastest way to do it?

DECLARE @FILL_ID_TABLE TABLE (
  FILL_ID varchar(16)
)

DECLARE @TODAYS_DATE date
SELECT
  @TODAYS_DATE = GETDATE()

--This deletes all data older than 2 weeks ago from today 
DECLARE @_DATE date
SET @_DATE = DATEADD(WEEK, -2, @TODAYS_DATE)

DECLARE @BatchSize int
SELECT
  @BatchSize = 100000


BEGIN TRAN FUTURE_TRAN
  BEGIN TRY

    INSERT INTO @FILL_ID_TABLE
      SELECT DISTINCT
        ID
      FROM dbo.ID_TABLE
      WHERE CREATED < @_DATE

    SELECT
      @BatchSize = 100000
    WHILE @BatchSize <> 0
    BEGIN
      DELETE TOP (@BatchSize) FROM TABLE1
      OUTPUT DELETED.* INTO dbo.TABLE1_ARCHIVE
      WHERE ID IN (SELECT
          ROLLUP_ID
        FROM @FILL_ID_TABLE)
      SET @BatchSize = @@rowcount
    END

    SELECT
      @BatchSize = 100000
    WHILE @BatchSize <> 0
    BEGIN
      DELETE TOP (@BatchSize) FROM TABLE2
      OUTPUT DELETED.* INTO dbo.TABLE2_ARCHIVE
      WHERE ID IN (SELECT
          FILL_ID
        FROM @FILL_ID_TABLE)
      SET @BatchSize = @@rowcount
    END

    PRINT 'Succeed'
  COMMIT TRANSACTION FUTURE_TRAN
END TRY
BEGIN CATCH
  PRINT 'Failed'
  ROLLBACK TRANSACTION FUTURE_TRAN
END CATCH

Upvotes: 1

Views: 232

Answers (1)

Slime recipe
Slime recipe

Reputation: 2273

Try join instead of subquery

DELETE  TOP (@BatchSize) T1 
OUTPUT DELETED.* INTO dbo.TABLE1_ARCHIVE
FROM  TABLE1 AS T1
JOIN @FILL_ID_TABLE AS FIL ON FIL.ROLLUP_ID = T1.Id

Upvotes: 3

Related Questions