sql - 使用记录 ID 列表作为输入更新 SQL 服务器中的 400 万条记录

Question

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

更新非常简单；一个 bool 字段需要设置为 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 条记录的神秘差距。

我的问题:

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

895条记录的神秘缺口怎么解释？

进行此更新的更好或最佳方法是什么？ (因为我开始认为我正在做的事情可能非常低效和/或容易出错)

Answer 1

解决这个问题的最好方法是将 400 万条记录插入到一​​个表中。事实上，您可以通过“批量插入”到 View 中，将它们放入带有标识列的表中。

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)

这里的exists子句相当于 left outer join .主要区别在于这种相关的子查询语法应该适用于其他数据库，其中更新的连接是特定于数据库的。

与 rownum列，您可以选择任意数量的行进行更新。因此，如果整体更新太大，您可以将更新置于循环中:

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

等等。您不必这样做，但如果您出于某种原因想要批量更新，则可以这样做。

关键思想是将id列表放入数据库中的一个表中，这样就可以利用数据库的强大功能进行后续的操作。