主页 / dba / 问题 / 115366
Accepted
Jonesome Reinstate Monica
Asked: 2015-09-18 07:14:49 +0800 CST

大表上的大滚动更新：越来越慢

  • 772

SQL Server 2014 标准版

我们有一个包含 1 亿多行的表。

我们需要更新几列中的值。

我们做了下面的，现在已经运行了 18 个小时，只做了 1700 万行。它越来越慢。

它是一个相当热的服务器和磁盘系统（EMC RAID10 等等）。CREATE INDEX 花费了大约 20 分钟（我们不得不容忍一次不幸的中断）。

我们可以使用什么方法来更快地解决这个问题？（在线强烈推荐）

CREATE NONCLUSTERED INDEX RECORD_DELETED_INDEX ON [dbo].[huge-table] ( [deleted] ASC, [deletedDate] ASC );
GO


DECLARE @CHUNK_SIZE int
SET @CHUNK_SIZE = 4000  -- to stay under lock escalation threshhold

UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
where deleted is null or deletedDate is null

WHILE @@ROWCOUNT > 0
BEGIN
    UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
    where deleted is null or deletedDate is null
END
sql-server transaction

1 个回答

  1. Best Answer

    将索引创建为过滤器（即使用 WHERE 子句）可能会更好，因为当您更新更多记录时索引会变小，然后从中过滤掉：

    CREATE NONCLUSTERED INDEX RECORD_DELETED_INDEX
  ON [dbo].[huge-table] ( [deleted] ASC, [deletedDate] ASC )
  WHERE deleted is null
  OR    deletedDate is null;

    但是，每次循环运行时，您仍然会遇到扫描该索引的问题。表中有 1 亿行并且每次迭代更新 4000 行，即您扫描表（或索引）以查找要更新的行的 25,000 次。最好减少查询表的次数。

    请参阅我对您关于此项目的其他问题之一的回答，以了解应该使此过程更快的设置：

    sql server：以小块更新大表上的字段：如何获得进度/状态？

    在那个答案中，我展示了如何只查询大表 100 次。每次它获取聚簇索引键字段并将它们用于所有UPDATE查询，以便快速更新。而且该设置还允许快速获取操作的当前进度（并且无需查询表！）并彻底取消该过程。

    需要注意的是，这个问题涉及以下问题（按时间顺序排列）：

    • 4

相关问题