主页 / dba / 问题 / 1750
Accepted
tuseau
Asked: 2011-03-16 05:53:12 +0800 CST

不使用子句加速巨大的 DELETE FROM <table> 的方法

  • 772

使用 SQL Server 2005。

我正在执行一个没有 where 子句的巨大 DELETE FROM。它基本上等同于 TRUNCATE TABLE 语句——除了我不允许使用 TRUNCATE。问题是表很大——有 1000 万行，需要一个多小时才能完成。有没有办法让它更快，没有：

  • 使用截断
  • 禁用或删除索引？

t-log 已经在单独的磁盘上。

欢迎任何建议！

sql-server performance

5 个回答

  1. Best Answer

    你可以做的是这样的批量删除：

    SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
    DELETE TOP (xxx) MyTable

    例如，xxx 是 50000

    对此的修改，如果您想删除非常高百分比的行...

    SELECT col1, col2, ... INTO #Holdingtable
           FROM MyTable WHERE ..some condition..

SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
    DELETE TOP (xxx) MyTable WHERE ...

INSERT MyTable (col1, col2, ...)
           SELECT col1, col2, ... FROM #Holdingtable
    • 41

  2. 您可以使用 TOP 子句轻松完成此操作：

    WHILE (1=1)
BEGIN
    DELETE TOP(1000) FROM table
    IF @@ROWCOUNT < 1 BREAK
END
    • 22

  3. 如果您无法使用 TRUNCATE，我同意将您的删除批处理成可管理块的建议，并且我喜欢 drop/create 建议的独创性，但我对您问题中的以下评论感到好奇：

    它基本上等同于 TRUNCATE TABLE 语句 -除了我不允许使用 TRUNCATE

    我猜这个限制的原因与需要授予直接截断表的安全性有关，并且它允许您截断您所关心的表以外的表。

    假设是这种情况，我想知道创建一个使用 TRUNCATE TABLE 并使用“EXECUTE AS”的存储过程是否被认为是提供直接截断表所需的安全权限的可行替代方案。

    希望这将为您提供所需的速度，同时解决您的公司在将您的帐户添加到 db_ddladmin 角色时可能遇到的安全问题。

    以这种方式使用存储过程的另一个优点是存储过程本身可以被锁定，以便只允许特定帐户使用它。

    如果由于某种原因这不是一个可接受的解决方案，并且您需要删除此表中的数据是需要每天/每小时/等执行一次的事情，我会要求创建一个 SQL 代理作业来截断表在每天的预定时间。

    希望这可以帮助！

    • 7

  4. 除了截断..只有批量删除可以帮助你。

    当然，您可以删除该表并使用所有约束和索引重新创建它。在 Management Studio 中，您可以选择编写要删除和创建的表的脚本，因此它应该是一个简单的选项。但这只有在您被允许执行 DDL 操作时才这样做，我认为这不是一个真正的选择。

    • 5

  5. 由于这个问题是如此重要的参考，我发布了这段代码，它真正帮助我理解了循环删除以及循环内的消息传递以跟踪进度。

    查询是根据这个重复的问题修改的。感谢@RLF查询库。

    CREATE TABLE #DelTest (ID INT IDENTITY, name NVARCHAR(128)); -- Build the test table
INSERT INTO #DelTest (name) SELECT name FROM sys.objects;  -- fill from system DB
SELECT COUNT(*) TableNamesContainingSys FROM #deltest WHERE name LIKE '%sys%'; -- check rowcount
go
DECLARE @HowMany INT;
DECLARE @RowsTouched INT;
DECLARE @TotalRowCount INT;
DECLARE @msg VARCHAR(100);
DECLARE @starttime DATETIME 
DECLARE @currenttime DATETIME 

SET @RowsTouched = 1; -- Needs to be >0 for loop to start
SET @TotalRowCount=0  -- Total rows deleted so far is 0
SET @HowMany = 5;     -- Variable to choose how many rows to delete per loop
SET @starttime=GETDATE()

WHILE @RowsTouched > 0
BEGIN
   DELETE TOP (@HowMany)
   FROM #DelTest 
   WHERE name LIKE '%sys%';

   SET @RowsTouched = @@ROWCOUNT; -- Rows deleted this loop
   SET @TotalRowCount = @TotalRowCount+@RowsTouched; -- Increment Total rows deleted count
   SET @currenttime = GETDATE();
   SELECT @msg='Deleted ' + CONVERT(VARCHAR(9),@TotalRowCount) + ' Records. Runtime so far is '+CONVERT(VARCHAR(30),DATEDIFF(MILLISECOND,@starttime,@currenttime))+' milliseconds.'
   RAISERROR(@msg, 0, 1) WITH NOWAIT;  -- Print message after every loop. Can't use the PRINT function as SQL buffers output in loops.  

END; 
SELECT COUNT(*) TableNamesContainingSys FROM #DelTest WHERE name LIKE '%sys%'; -- Check row count after loop finish
DROP TABLE #DelTest;
    • 3

相关问题