存储过程以删除大量记录 [英] Store procedure to delete huge records

问题描述

我在这里要做的是删除700万条记录。

由于记录数量庞大，我试图删除

小块数据（条件是我们一次只会删除

那些超过6个月的记录）。

实施它的最佳方式是什么？



我尝试过的方法：

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:

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

推荐答案

如果我理解你的问题，你想要避免的第一件事是使用游标。那些会严重减慢你的速度。



而是尝试使用日期范围在合理的集合中删除。范围的大小取决于每天的数据量与您想要使用单次运行删除的数量。



例如，您可以删除块中的行几个月如下：

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:

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()));
...

只需选择起点，这样第一次运行只会删除一个月。换句话说，设置条件远远超过过去。



其他一些需要考虑的事项：

• 当然首先备份数据
• 如果要删除的数据量很大，请考虑在调用之间进行调试，以避免不必要的日志文件增长
• 考虑使用批量记录或简单恢复模型来加速操作。但是，在更改恢复模型之前，请仔细考虑后果。阅读恢复模型（SQL Server） [ ^ ]


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)[^]




这篇关于存储过程以删除大量记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！