1. 首页
  2. 问题
  3. 使用记录ID列表作为输入来更新SQL Server中的400万条记录
一尘不染

使用记录ID列表作为输入来更新SQL Server中的400万条记录

sql

在迁移项目期间，我面临着SQL Server中400万条记录的更新。

更新非常简单；一个布尔字段需要设置为true / 1并且我输入的内容是必须填写此字段的所有ID的列表。（每行一个ID）

对于这种大小的SQL任务，我并不是专家，所以我开始尝试1个包含“ WHERE xxx IN ( {list of ids, separated by comma} )”的UPDATE语句。首先，我尝试了一百万条记录。在测试服务器上的一个小型数据集上，这就像一个超级按钮，但是在生产环境中却出现了错误。因此，我几次缩短了ID列表的长度，但无济于事。

我尝试的下一件事是将列表中的每个id转换为UPDATE语句（“ UPDATE yyy SET booleanfield = 1 WHERE id = '{id}'”）。在某个地方，我读到每x行有一个GO很好，所以我每100行插入一个GO（使用了从Unix移植的出色的“ sed”工具）。

因此，我将400万条更新语句的列表分成了250.000条，将它们保存为sql文件，然后开始将第一个语句加载并运行到SQL Server Management
Studio（2008）中。请注意，我也尝试了SQLCMD.exe，但是令我惊讶的是，它的运行速度比SQL Studio慢10到20倍。

它花费了大约1.5个小时来完成，并导致“查询已完成，但有错误”。但是，消息列表包含一个不错的列表，其中列出了“受影响的1行”和“受影响的0行”，后者用于未找到ID的情况。

接下来，我使用COUNT（*）检查了表中的更新记录数量，发现更新语句数量和更新记录数量之间有几千条记录的差异。

然后，我认为这可能是由于不存在的记录所致，但是当我减去输出中“受影响的0行”的数量时，存在895条记录的神秘缺口。

我的问题：

  1. 有什么办法可以找到“错误完成查询”中的描述和错误原因。

  2. 如何解释895条唱片的神秘差距？

  3. 什么是进行此更新的更好或最好的方法？（因为我开始认为自己的工作效率很低和/或容易出错）


阅读 177

收藏
2021-05-23

共1个答案

一尘不染

解决此问题的最佳方法是将400万条记录插入表中。实际上，您可以通过在视图中“批量插入”将它们放入带有标识列的表中。

create table TheIds (rownum int identity(1,1), id int);

create view v_TheIds (select id from TheIds);

bulk insert into v_TheIds . . .

有了数据库中的所有数据，您现在有了更多选择。尝试更新：

update t
    set booleanfield = 1
    where exists (select 1 from TheIds where TheIds.id = t.id)

您还应该在上创建索引TheIds(id)。

这是一个很大的更新，全部作为一个事务执行。这可能会影响性能，并开始填充日志。您可以使用以下列将其分解为较小的交易rownum：

update t
    set booleanfield = 1
    where exists (select 1 from TheIds where TheIds.id = t.id and TheIds.rownum < 1000)

这里的exist子句的作用等同于left outer join。主要区别在于，这种相关的子查询语法应在其他数据库中工作，这些数据库中的更新联接是特定于数据库的。

使用该rownum列，您可以选择要更新的任意多行。因此，如果整体更新太大，则可以将更新放入循环中：

where rownum < 100000
where rownum between 100000 and 199999
where rownum between 200000 and 299999

等等。您不必执行此操作，但是如果出于某种原因要批处理更新，则可以这样做。

关键思想是将ID列表放入数据库的表中，以便可以将数据库的功能用于后续操作。

2021-05-23