0

I have three tables: Records, ChildTable, and ParentTable. ChildTable and ParentTable both have RecordId as the foreign key. My aim is to delete all records older than 300 days from the Records table, along with corresponding entries from the other two tables. The Records table contains nearly a million records to be deleted, while ChildTable and ParentTable collectively hold around 3 million rows for all the RecordIds being deleted. I'm executing these deletions in batches of 4000 and would like to verify if the logic I'm employing is correct. Any assistance would be greatly appreciated. Thank you!

Declare @errorMessage   Varchar(1000)
Declare @counter        Int = 0
Declare @BatchSize      Int = 1000

Set NoCount On

WHILE (1=1)
BEGIN
    Begin Try
        Begin Tran

            /****** Get all the deletions from the records  ******/
            Select Top (@BatchSize) RecordId
              Into #TempDeletions
              From [dbo].[Records] With(NoLock)
             Where LastDate < Convert(date,DateAdd(day,-300,getdate()))
            
            If @@RowCount = 0
            Begin
                Drop Table If Exists #TempDeletions 
                Commit Tran
                Break
            End

            /****** Delete the Rows ******/
            Delete t_c
            From [dbo].[ChildTable]         t_c
            Join #TempDeletions             tmp_del
            On t_c.RecordId                 = tmp_del.RecordId
        
            Delete t_p
            From [dbo].[ParentTable]        t_p
            Join #TempDeletions             tmp_del
            On t_p.RecordId                 = tmp_del.RecordId
        
            Delete t_r
            From [dbo].[Records]            t_r
            Join #TempDeletions             tmp_del
            On t_p.RecordId                 = tmp_del.RecordId

            /* Drop the temp table */
            Drop Table If Exists #TempDeletions

        Commit Tran
    End Try
    Begin Catch
        If @@trancount > 0
            Rollback Tran

        Select Error_Message()
    End Catch
END
3
  • Do you have an index on the LastDate column? Deleting a million records should only take a few seconds tops, if so. It's not a lot of data.
    – J.D.
    Commented Feb 12 at 21:23
  • @J.D. Yes, I have an index on that column. This is a critical table and we cannot have any downtime. That is why i'm doing it in batches to avoid that. Commented Feb 12 at 22:00
  • You're probably creating more downtime with batching it. It's less efficient to do things in smaller batches, but the benefit is it gives breathing room on longer running queries. The level of deletes you're doing doesn't appear long enough to warrant it, and will potentially be the less efficient route. Alternatively, the less locking solution would be to insert only the remaining rows you want to keep into a new table (assuming it's less than what you're deleting), and then cutover the tables (renaming if needed) at an applicable point in time.
    – J.D.
    Commented Feb 12 at 22:16

1 Answer 1

0

Are there more records to keep or to delete? If there are more records to delete than to keen, then it maybe more easy and faster to

  1. create new tables
  2. insert the records to keep from the old tables to the new tables
  3. delete or rename the old tables
  4. rename the new tables to old names

If there is possibility of insertions of new records in the tables while you perform the above operations and if you have access to modify programming/SQL code of insertion or you can create stored procedure then you can insert new records in the new tables as well.

1
  • 1
    Each table contains over 60 million rows, and I will need to remove approximately one million rows from each. Creating new tables for insertion is not feasible. Commented Feb 13 at 14:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.