在 SQL Server 中删除 100 万行 [英] Deleting 1 millions rows in SQL Server

问题描述

我正在处理客户的数据库，由于软件中的错误，大约有 100 万行需要删除.除了删除它们之外，还有没有有效的方法:

I am working on a client's database and there is about 1 million rows that need to be deleted due to a bug in the software. Is there an efficient way to delete them besides:

DELETE FROM table_1 where condition1 = 'value' ?

推荐答案

这里是上面建议的批量删除的结构.不要一次尝试1M...

Here is a structure for a batched delete as suggested above. Do not try 1M at once...

批处理的大小和等待延迟显然是可变的，这取决于您的服务器能力以及您缓解争用的需要.您可能需要手动删除一些行，测量它们需要多长时间，并将批量大小调整为您的服务器可以处理的大小.如上所述，任何超过 5000 的值都可能导致锁定(我不知道).

The size of the batch and the waitfor delay are obviously quite variable, and would depend on your servers capabilities, as well as your need to mitigate contention. You may need to manually delete some rows, measuring how long they take, and adjust your batch size to something your server can handle. As mentioned above, anything over 5000 can cause locking (which I was not aware of).

这最好在下班后完成……但 100 万行对于 SQL 来说真的不是很多.如果您在 SSMS 中查看您的消息，打印输出可能需要一段时间才能显示，但经过几批后就会显示，请注意它不会实时更新.

This would be best done after hours... but 1M rows is really not a lot for SQL to handle. If you watch your messages in SSMS, it may take a while for the print output to show, but it will after several batches, just be aware it won't update in real-time.

添加停止时间@MAXRUNTIME &@BSTOPATMAXTIME.如果您将 @BSTOPATMAXTIME 设置为 1，脚本将在所需时间自行停止，例如 8:00AM.这样你就可以每晚安排它在午夜开始，它会在早上 8 点生产之前停止.

Added a stop time @MAXRUNTIME & @BSTOPATMAXTIME. If you set @BSTOPATMAXTIME to 1, the script will stop on it's own at the desired time, say 8:00AM. This way you can schedule it nightly to start at say midnight, and it will stop before production at 8AM.

答案非常受欢迎，所以我在每个评论中添加了 RAISERROR 代替 PRINT.

Answer is pretty popular, so I have added the RAISERROR in lieu of PRINT per comments.

DECLARE @BATCHSIZE INT, @WAITFORVAL VARCHAR(8), @ITERATION INT, @TOTALROWS INT, @MAXRUNTIME VARCHAR(8), @BSTOPATMAXTIME BIT, @MSG VARCHAR(500)
SET DEADLOCK_PRIORITY LOW;
SET @BATCHSIZE = 4000
SET @WAITFORVAL = '00:00:10'
SET @MAXRUNTIME = '08:00:00' -- 8AM
SET @BSTOPATMAXTIME = 1 -- ENFORCE 8AM STOP TIME
SET @ITERATION = 0 -- LEAVE THIS
SET @TOTALROWS = 0 -- LEAVE THIS

WHILE @BATCHSIZE>0
BEGIN
    -- IF @BSTOPATMAXTIME = 1, THEN WE'LL STOP THE WHOLE JOB AT A SET TIME...
    IF CONVERT(VARCHAR(8),GETDATE(),108) >= @MAXRUNTIME AND @BSTOPATMAXTIME=1
    BEGIN
        RETURN
    END

    DELETE TOP(@BATCHSIZE)
    FROM SOMETABLE
    WHERE 1=2

    SET @BATCHSIZE=@@ROWCOUNT
    SET @ITERATION=@ITERATION+1
    SET @TOTALROWS=@TOTALROWS+@BATCHSIZE
    SET @MSG = 'Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR)
    RAISERROR (@MSG, 0, 1) WITH NOWAIT
    WAITFOR DELAY @WAITFORVAL 
END

这篇关于在 SQL Server 中删除 100 万行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！