SQL Server中非常大的表的UPDATE或MERGE操作

Question

我需要每天更新一张非常大（300M记录）而且比较宽的TABLE1表。更新的源数据位于另一张表UTABLE中，该表的行数只有TABLE1的10%-25%，但是该表比较窄。这两个表都使用record_id作为主键。

目前，我正在使用以下方法重新创建TABLE1：

<!-- language: sql -->
    1) SELECT (required columns) INTO TMP_TABLE1 
    FROM TABLE1 T join UTABLE U on T.record_id=U.record_id  
    2) DROP TABLE TABLE1  
    3) sp_rename 'TMP_TABLE1', 'TABLE1'

然而在我的服务器上执行这个操作需要将近40分钟（SQL Server占用了60GB的RAM）。我希望能够获得50%的性能提升 - 我还可以尝试哪些其他选项呢？

1. MERGE and UPDATE - something like the code below works faster only for a very small UTABLE table - at full size, everything just hangs:

   <!-- language: SQL -->
   MERGE TABLE1 as target  
   USING UTABLE as source  
   ON target.record_id = source.record_id   
     WHEN MATCHED THEN   
       UPDATE SET Target.columns=source.columns
   

2. I heard that I can perform a batch MERGE by using ROWCOUNT - but I don't think it can be fast enough for a 300M row table.

3. Any SQL query hints that can be helpful?

- Sergio Kozlov

---

嗨@chris，查询计划非常简单：1表扫描TABLE1，2表扫描UTABLE，3哈希连接，4合并。第一步和最后一步占用了90%的时间。但是我已经明白如何解决我的问题-请看我的回答。 - Sergio Kozlov

Answer 1

实际上，我已经找到了针对这种查询的通用建议：使用SQL Merge或Update的想法非常聪明，但是当我们需要更新大量记录（即75M）在一个大而广泛的表格（即240M）时它就失效了。

通过查看下面查询的查询计划，我们可以说，TABLE1的TABLE SCAN和最终的MERGE占用了90%的时间。

MERGE TABLE1 as Target  
USING UTABLE as source  
ON Target.record_id = source.record_id   
WHEN MATCHED AND (condition) THEN   
    UPDATE SET Target.columns=source.columns

要使用MERGE，我们需要：

1. 减少需要更新的行数，并正确地将此信息传递给SQL Server。这可以通过使UTABLE更小或指定其他缩小待合并部分的condition来实现。
2. 确保待合并的部分适合内存，否则查询运行速度会变慢。将TABLE1缩小一半使我的实际查询时间从11个小时缩短到40分钟。

正如Mark所提到的，您可以使用UPDATE语法并使用WHERE子句来缩小待合并的部分-这将产生相同的结果。此外，请避免对TABLE1进行索引，因为这将导致在MERGE期间重建索引的额外工作。

- Sergio Kozlov

Answer 2

首先，我会找出您的瓶颈在哪里 - 您的CPU是否被占满或处于空闲状态？换句话说，您的IO子系统是否能够适当地处理负载？

重新创建整个表会产生大量的IO负载，更不用说它会占用很多空间来临时存储基本上已经存储了两次的表。

您是否需要执行MERGE - 从我所看到的情况来看，简单的更新就足够了。例如：

UPDATE
    TABLE1
SET
    ColumnX = UTABLE.ColumnX
    ...
FROM
    TABLE1
INNER JOIN
    UTABLE ON TABLE1.record_id = UTABLE.record_id

您可以使用ROWCOUNT批量更新，但这不会加速执行，只有在减少锁定方面有帮助。

此外 - 您的表上有什么类型的索引？禁用更新之前可能会更快，并在更新后从头开始重建它们（只重新创建非聚集索引）。

- Mark S. Rasmussen

---

嗨，马克，谢谢你的回复。我没有索引，因为它们会减慢MERGE或UPDATE查询。至于IO - 基于平均磁盘队列计数器 - 磁盘非常忙碌，似乎TABLE1只是不适合内存。现在我正在尝试使用CTE预过滤输入，然后再执行MERGE，我将根据结果回答自己的问题。 - Sergio Kozlov