Store procedure to delete huge records

Question

1.00/5 (1 vote)

See more:

What I am trying to do here is to delete 7 million records.
Since its huge number of records, I am trying to delete
small chunks of data (condition that we are only going to delete
those records which are older than past 6 months) at a time.
What could be the best way to implement it?

What I have tried:

SQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE spDeleteRecord
@DeleteBatchSize INT,
@DelayTime DATETIME
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DeleteRowCount INT
SET @DeleteRowCount = 1
DECLARE @createDate DATETIME;
DECLARE DelteteRecords_Cursor CURSOR FOR 
SELECT TOP(@DeleteBatchSize) CreateDate
FROM [StoreSystems].[dbo].[tblDept]---------------- Table from which records has to be deleted
--WHERE CreateDate <= DATEADD(month, -6, GETDATE()) 
--order by CreateDate desc; 
OPEN DelteteRecords_Cursor; 
--FETCH FROM DelteteRecords_Cursor
FETCH FROM DelteteRecords_Cursor INTO @createDate; 
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@createDate <= DATEADD(month, -6, GETDATE()))
BEGIN
DELETE FROM [tblDept] ---- Table Name
WHERE CURRENT OF DelteteRecords_Cursor; 
END
END
CLOSE DelteteRecords_Cursor; 
DEALLOCATE DelteteRecords_Cursor; 
--SET @DeleteRowCount = @@ROWCOUNT;
--PRINT @DeleteRowCount;
END
GO

Posted 26-Sep-16 6:58am

s23user

Updated 4-Oct-16 18:05pm

v3

Add a Solution

Comments

Richard Deeming 26-Sep-16 16:05pm

REPOST
This is essentially the same question you asked earlier:
http://www.codeproject.com/Questions/1131829/Is-it-possible-to-pass-schema-name-and-table-name[^]

Why bother asking a question if you're just going to ignore the answers?!

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2016-09-26T07:29:00

If I understand your question correctly, the first thing you want to avoid is using cursors. Those will seriously slow you down.

Instead try deleting in reasonable sets using a date range. The size of the range depends on the amount of data per each day versus how much you want to delete using a single run.

For example you can delete rows in chunks of months like following:

SQL

DELETE FROM [CampaignUpcDept] WHERE DateColumnName <= DATEADD(month, -50, GETDATE()));
DELETE FROM [CampaignUpcDept] WHERE DateColumnName <= DATEADD(month, -49, GETDATE()));
DELETE FROM [CampaignUpcDept] WHERE DateColumnName <= DATEADD(month, -48, GETDATE()));
...

Just select the starting point so that the first run will delete only one month. In other words set the condition far enough to the past.

Some other things to take into consideration:

Of course you back up the data first
If the amount of data to be deleted is large, consider committing in between calls in order to avoid unnecessary growth of the log file
consider using bulk logged or simple recovery model to speed up the operation. HOWEVER, before changing the recovery model, carefully consider the consequences. Read Recovery Models (SQL Server)[^]