在迁移项目期间,我面临着SQL Server中400万条记录的更新。
更新非常简单;一个布尔字段需要设置为true / 1并且我输入的内容是必须填写此字段的所有ID的列表。(每行一个ID)
对于这种大小的SQL任务,我并不是专家,所以我开始尝试1个包含“ WHERE xxx IN ( {list of ids, separated by comma} )”的UPDATE语句。首先,我尝试了一百万条记录。在测试服务器上的一个小型数据集上,这就像一个超级按钮,但是在生产环境中却出现了错误。因此,我几次缩短了ID列表的长度,但无济于事。
WHERE xxx IN ( {list of ids, separated by comma} )
我尝试的下一件事是将列表中的每个id转换为UPDATE语句(“ UPDATE yyy SET booleanfield = 1 WHERE id = '{id}'”)。在某个地方,我读到每x行有一个GO很好,所以我每100行插入一个GO(使用了从Unix移植的出色的“ sed”工具)。
UPDATE yyy SET booleanfield = 1 WHERE id = '{id}'
因此,我将400万条更新语句的列表分成了250.000条,将它们保存为sql文件,然后开始将第一个语句加载并运行到SQL Server Management Studio(2008)中。请注意,我也尝试了SQLCMD.exe,但是令我惊讶的是,它的运行速度比SQL Studio慢10到20倍。
它花费了大约1.5个小时来完成,并导致“查询已完成,但有错误”。但是,消息列表包含一个不错的列表,其中列出了“受影响的1行”和“受影响的0行”,后者用于未找到ID的情况。
接下来,我使用COUNT(*)检查了表中的更新记录数量,发现更新语句数量和更新记录数量之间有几千条记录的差异。
然后,我认为这可能是由于不存在的记录所致,但是当我减去输出中“受影响的0行”的数量时,存在895条记录的神秘缺口。
我的问题:
有什么办法可以找到“错误完成查询”中的描述和错误原因。
如何解释895条唱片的神秘差距?
什么是进行此更新的更好或最好的方法?(因为我开始认为自己的工作效率很低和/或容易出错)
解决此问题的最佳方法是将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)。
TheIds(id)
这是一个很大的更新,全部作为一个事务执行。这可能会影响性能,并开始填充日志。您可以使用以下列将其分解为较小的交易rownum:
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。主要区别在于,这种相关的子查询语法应在其他数据库中工作,这些数据库中的更新联接是特定于数据库的。
left outer join
使用该rownum列,您可以选择要更新的任意多行。因此,如果整体更新太大,则可以将更新放入循环中:
where rownum < 100000 where rownum between 100000 and 199999 where rownum between 200000 and 299999
等等。您不必执行此操作,但是如果出于某种原因要批处理更新,则可以这样做。
关键思想是将ID列表放入数据库的表中,以便可以将数据库的功能用于后续操作。