如何优化SQL Server合并语句，使其能够处理数百万条记录

Question

我使用 SQL Server 2014，需要更新一个表中新添加的 datetime 类型列。这两个相关的表（都有超过3000万条记录）如下：

TableA：

CategoryID, itemID, dataCreated, deleted, some other string properties. 

这个表格包含每个项目的多条记录，具有不同的datecreated日期。

TableB:

CategoryID, itemID, LatestUpdatedDate (This is the new added column)

“categoryID”和“itemID”都是该表索引的一部分。

为了更新表B中与表A匹配的“CategoryID”和“ItemID”的“LatestUpdatedDate”，我使用了以下合并语句：

merge [dbo].[TableB] with(HOLDLOCK) as t
using 
(
    select CategoryID,itemID, max(DateCreated) as LatestUpdatedDate 
    from dbo.TableA 
    where TableA.Deleted = 0
    group by CategoryID,itemID
) as s on t.CategoryID = s.CategoryID and t.itemID = s.itemID

when matched then
    update
    set t.LatestUpdatedDate = s.LatestUpdatedDate

when not matched then
    insert (CategoryID, itemID, LatestUpdatedDate)
    values (s.CategoryID, s.itemID)

鉴于两个表中有数百万条记录，我该如何优化这个脚本？或者有没有其他更好的方法来提高表格更新的性能？

注意：这是一个一次性的脚本，数据库正在运行中，将来会添加一个触发器到tableA中，在插入时更新tableB中的日期。

- xingkong

---

关键问题是：更改是否需要原子性？是否有一段时间可以独占锁定此表？如果不需要原子性，那么我会将其拆分为单独的“update”和“insert”语句。这样，您就可以将工作分成两半。我以前在性能方面曾经被“merge”所困扰，所以现在我避免使用它。您还可以使用“set rowcount”技巧，以便一次只更新较小的批次，而不是一次锁定整个表。 - Nick.McDermaid

我应该提到这是一个实时数据库，尽管在交通量较少的夜间/周末运行脚本肯定没问题。你之前在合并方面遇到了什么性能问题？我不太确定在这些级别的表上执行需要多长时间。几分钟还是几小时？ - xingkong

使用合并时，性能与“未匹配时”的部分有关。您需要确定更改是否需要原子性。也就是说：如果表在几个小时内只进行了逐步的更改，这样是否可以，还是必须一次性完全更新表（以保持一致性）。如果行y被更新而不是行x，您的应用程序会崩溃吗？请注意，如果连接列上没有索引，则无论如何都会很慢。添加索引不会破坏应用程序的表更改，您可以在之后删除它们。 - Nick.McDermaid

子表格s中有多少行？TableA中有多少行？最好先将其放入表格中。此外，您是否了解HOLDOCK的作用？您知道它是否会影响应用程序吗？ - Nick.McDermaid

谢谢Nick。更改不需要原子性，因为在数据准备好之前我不会启用与代码相关的内容。两个表都有超过3000万行，其中tableA大约是tableB的两倍。在这里使用HOLDOCK来防止并发问题，如此处所述：链接。我使用大约5000万行的一些本地虚假数据进行了测试，脚本在10分钟内完成。看起来对于一次性操作是可以接受的。 - xingkong

Answer 1

根据 优化MERGE语句的性能，最好的方法是：

• 创建一个在源表中连接列上唯一且包含所有列的索引。
• 在目标表的连接列上创建一个唯一的聚集索引。

在MERGE1期间，通过在TableA上创建一个索引(Deleted, CategoryID, itemID) INCLUDE(DateCreated)可以获得性能提升。但是，由于这是一次性操作，创建此索引所需的资源（时间、CPU、空间）可能不会抵消运行当前查询并依赖现有索引所带来的性能收益。

- Serge

---

是的，由于这是一次性操作，我会避免对表本身进行任何更改。如果我将其拆分为多个带有条件的合并，例如categoryID < 500，categoryID> 500，是否有帮助？ - xingkong

测试了一张有超过5000万行的表格，进行了索引构建，完成时间约为2分钟。看起来很好。谢谢。 - xingkong

我有一个问题：聚集索引需要排序的输入，因此查询优化器可能会执行排序操作，这对于数百万条记录来说并不是最好的选择。此外，即使在SQL预览中，聚集索引也会使插入/更新操作变慢。那么，聚集索引的好处是什么？ - Alessandro C

聚集索引指定了表中数据存储的顺序。除非有充分的理由将表保留为堆，否则大多数表都应该有一个经过精心选择的聚集索引。当目标表具有聚集索引时，数据库引擎可能能够更快地找到（潜在的）匹配行。索引查找比全表扫描快得多。 - Serge

是的，INSERT和UPDATE数据写入可能会变慢。然而，大多数表已经有了聚集索引。在设计聚集索引时，请考虑表的典型用例。 - Serge