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

Question

我的数据库背景主要是Oracle，但最近我一直在帮助一些SQL Server的工作。我们的团队继承了一些SQL Server的DTS包，用于每天加载和更新大量数据。目前它正在运行SQL Server 2000，但很快将升级到SQL Server 2005或2008。大规模的更新速度太慢了。

我注意到代码中有一些大型更新是在循环中使用过程化代码完成的，因此每个语句仅在单个事务中更新表的一小部分。这种在SQL Server中进行更新的方法是否可靠？并发会话的锁定不应该成为问题，因为在批量加载期间禁用了用户对表的访问。我查阅了一些资料，在其中找到了一些文章建议这样做可以节省资源，并且每次更新提交后都会释放资源，从而提高效率。在Oracle中，这通常是一种不好的方法，我在Oracle中使用单个事务成功地进行了非常大的更新。在Oracle中频繁提交会减慢进程并使用更多资源。

我的问题是，在SQL Server中进行大规模更新，通常是使用过程化代码并提交许多SQL语句，还是使用一个大语句完成整个更新？

- RussellH

Answer 1

抱歉，各位，

以上回答都没有解决问题。它们只是展示了如何做某些事情的例子。答案是，频繁提交会使用更多资源，但是在提交点之前无法截断事务日志。因此，如果您的单个跨度事务非常大，它将导致事务日志增长并可能分段，如果未检测到这种情况，以后会出现问题。此外，在回滚情况下，持续时间通常是原始事务的两倍长。因此，如果您的事务在半小时后失败，将需要1小时来回滚，而且您无法停止它 :-)

我曾经使用过SQL Server2000/2005、DB2、ADABAS，并且以上内容对所有数据库都适用。我真的不知道Oracle可以有什么不同。

您可以使用bcp命令替换T-SQL，并在其中设置批处理大小，而无需编写代码。

在单个表扫描中发出频繁提交比运行具有小处理数字的多个扫描更可取，因为通常情况下，如果需要表扫描，则整个表将被扫描，即使您只返回了一个小子集。

避免使用快照。快照只会增加IO次数并竞争IO和CPU。

- Peter K

---

Oracle在某种意义上是不同的，它使用"undo"和"redo"。 "undo"用于回滚操作和允许读一致性查询。 - RussellH

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数量的行插入到辅助表中，您可以针对其进行JOIN。)

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

- beach

---

我从未想过你可以这样做 - 真是天才！谢谢你的建议！ - SqlRyan

我同意我需要做很多实验。我需要测量更新速度和对其他数据库用户（访问其他表）的影响。 - RussellH

SET ROWCOUNT在SQL Server 2005及以上版本中已被弃用：http://msdn.microsoft.com/en-us/library/ms143729.aspx。现在推荐使用TOP语法：DELETE TOP 1000 FROM MyTable WHERE ... - lambacck

Answer 3

嗯，一切都取决于情况。

但是...假设您的数据库处于单用户模式或您对涉及的所有表进行了表锁定(tablockx)，批处理可能会表现得更差。特别是如果批处理强制执行表扫描。

唯一的例外是非常复杂的查询通常会消耗tempdb上的资源，如果tempdb用完空间(因为执行计划需要一个恶心的复杂哈希连接)，那么你就有大麻烦了。

分批处理是SQL Server中经常使用的一种通用实践(当它不处于快照隔离模式时)，以增加并发性并避免由于死锁而导致的巨大事务回滚(在更新活动的1000万行表时往往会出现死锁问题)。

- sambo99

---

我原本以为 "取决于情况" 会是答案。 - RussellH

Answer 4

当你转移到SQL Server 2005或2008时，你需要在SSIS中重新制作所有那些DTS包。我认为你会惊喜地发现SSIS可以快多了。

一般来说，在SQL Server 2000中，如果整个数据集占用表太长时间，你需要按记录批次运行。如果你在系统没有使用的夜间运行包，你可能可以通过整个数据集的基于集合的插入来完成。逐行插入始终是最慢的方法，因此尽可能避免（特别是如果所有逐行插入都在一个巨大的事务中！）。如果你有24小时无停机时间访问，你几乎肯定需要分批运行。

- HLGEM

---

特别是一个巨大的事务！这似乎在SQL Server中会带来麻烦。那么，在READ COMMITTED SNAPSHOT模式下呢？事务是否仍然使用大量锁定？对于大批量交易来说，该模式是否值得考虑？ - RussellH

特别是...一个巨大的事务！在SQL Server中，看起来非常大的事务会带来麻烦。那么在READ COMMITTED SNAPSHOT模式下呢？事务仍然使用很多锁吗？这种模式是否值得用于处理大批量事务？ - RussellH