SQL Server中的批量更新和提交频率

Question

我的数据库背景主要是Oracle，但我最近一直在帮助一些SQL Server工作。我的团队继承了一些SQL server DTS包，这些包每天都会加载和更新大量数据。目前它在SQL Server 2000上运行，但很快就会升级到SQL Server 2005或2008。批量更新运行得太慢。

关于代码，我注意到的一件事是，一些大的更新是在循环中的过程代码中完成的，因此每个语句在单个事务中只更新表的一小部分。这是一种在SQL server中进行更新的可靠方法吗？并发会话的锁定应该不是问题，因为在进行大容量装载时，用户对表的访问是禁用的。我在谷歌上搜索了一些，发现一些文章表明，这样做可以节省资源，并且每次更新提交时都会释放资源，从而提高效率。在Oracle中，这通常是一种糟糕的方法，我在Oracle中成功地使用了单个事务进行非常大的更新。频繁提交会降低进程速度，并使用Oracle中的更多资源。

我的问题是，对于SQL Server中的大量更新，使用过程代码并提交多个SQL语句，或者使用一个大语句来完成整个更新，通常是一种好的做法吗？

Answer 1

抱歉，各位，

以上都不能回答这个问题。它们只是你如何做事情的例子。答案是，频繁提交会使用更多的资源，然而，事务日志在提交点之前无法截断。因此，如果您的单跨事务非常大，它将导致事务日志增长并可能聚合，如果未检测到，这将在以后导致问题。此外，在回滚情况下，持续时间通常是原始事务的两倍。因此，如果您的事务在1/2小时后失败，则需要1小时才能回滚，并且您无法停止它:-)

我使用过SQLServer2000/2005，DB2，ADABAS，上面的都是真的。我真的看不出Oracle有什么不同的工作方式。

您可以使用bcp命令替换T-SQL，在那里您可以设置批处理大小，而不必编写代码。

在单次表扫描中发出最频繁的commits命令比运行处理次数较少的多个扫描更可取，因为通常情况下，如果需要表扫描，即使只返回很小的子集，也会扫描整个表。

远离快照。快照只会增加IO数量，并会竞争IO和CPU

Answer 2

一般来说，我发现批量更新更好--通常在100到1000之间。这完全取决于表的结构:外键？触发器？或者只是更新原始数据？您需要进行实验，看看哪种方案最适合您。

如果我使用的是纯SQL，我会这样做来帮助管理服务器资源：

SET ROWCOUNT 1000
WHILE 1=1 BEGIN
    DELETE FROM MyTable WHERE ...
    IF @@ROWCOUNT = 0
        BREAK
END
SET ROWCOUNT 0

在本例中，我正在清除数据。只有当您可以限制或选择性地更新行时，这才适用于UPDATE。(或者只将xxxx行插入到您可以连接的辅助表中。)

但是，请不要一次更新xx百万行。如果发生错误，所有这些行都将被回滚(这需要额外的永久时间)。

Answer 3

一切都要视情况而定。

但是……假设你的数据库处于单用户模式或，你有针对所有相关表的表锁 (tablockx)，批处理的性能可能会更差。尤其是在批处理强制表扫描的情况下。

需要注意的是，非常复杂的查询通常会消耗tempdb上的资源，如果tempdb耗尽了空间(因为执行计划需要一个令人讨厌的复杂哈希连接)，那么您就麻烦大了。

批处理是SQL Server (当SQL Server不处于快照隔离模式时)中经常使用的一种常规做法，用于增加并发性并避免由于死锁而导致的大量事务回滚(在更新处于活动状态的1000万行表时，您往往会遇到大量死锁)。