使用联接有效地更新表

Question

我有一张表，上面有住户的详细资料，另一张桌子上有所有与家庭有关的人的详细资料。对于家用表，我使用其中的两个列- [tempId,n]定义了一个主键。对于person表，我使用它的3列[tempId,n,sporder]定义了一个主键

使用主键群集索引所指示的排序，我为每个家庭[HHID]和每个人[PERID]记录生成了一个唯一的ID (下面的片段用于生成PERID)：

 ALTER TABLE dbo.persons
 ADD PERID INT IDENTITY
 CONSTRAINT [UQ dbo.persons HHID] UNIQUE;

现在，我的下一步是将每个人与相应的家庭相关联，即:将一个[PERID]映射到一个[HHID]。两个表之间的人行横道基于两列[tempId,n]。为此，我有以下内部联接语句。

UPDATE t1
  SET t1.HHID = t2.HHID
  FROM dbo.persons AS t1
  INNER JOIN dbo.households AS t2
  ON t1.tempId = t2.tempId AND t1.n = t2.n;

我一共有1928783份家庭记录和5239842份个人记录。目前的执行时间非常长。

现在，我的问题：

1. 是否有可能进一步优化此查询？更普遍地说，优化联接查询的经验规则是什么？
2. 是否有另一个查询结构可以以更好的执行时间实现我想要的结果？

我将Server 2008生成的上传了执行计划用于整个脚本到SQLPerformance.com

Answer 1

我很确定表的定义与此非常接近：

CREATE TABLE dbo.households
(
    tempId  integer NOT NULL,
    n       integer NOT NULL,
    HHID    integer IDENTITY NOT NULL,

    CONSTRAINT [UQ dbo.households HHID] 
        UNIQUE NONCLUSTERED (HHID),

    CONSTRAINT [PK dbo.households tempId, n]
    PRIMARY KEY CLUSTERED (tempId, n)
);

CREATE TABLE dbo.persons
(
    tempId  integer NOT NULL,
    sporder integer NOT NULL,
    n       integer NOT NULL,
    PERID   integer IDENTITY NOT NULL,
    HHID    integer NOT NULL,

    CONSTRAINT [UQ dbo.persons HHID]
        UNIQUE NONCLUSTERED (PERID),

    CONSTRAINT [PK dbo.persons tempId, n, sporder]
        PRIMARY KEY CLUSTERED (tempId, n, sporder)
);

我没有这些表或您的数据的统计数据，但下面将至少设置表基数正确(页面计数是猜测)：

UPDATE STATISTICS dbo.persons 
WITH 
    ROWCOUNT = 5239842, 
    PAGECOUNT = 100000;

UPDATE STATISTICS dbo.households 
WITH 
    ROWCOUNT = 1928783, 
    PAGECOUNT = 25000;

查询计划分析

您现在的查询是：

UPDATE P
SET HHID = H.HHID
FROM dbo.households AS H
JOIN dbo.persons AS P
    ON P.tempId = H.tempId
    AND P.n = H.n;

这就产生了一个相当低效的计划：

该计划中的主要问题是散列、连接和排序。两者都需要内存授权(散列连接需要构建哈希表，排序需要空间来存储行，同时进行排序)。计划资源管理器显示此查询已被授予765 MB：

这是相当多的服务器内存专用于一个查询！更重要的是，在根据行计数和大小估计开始执行之前，这个内存分配是固定的。

如果在执行时内存不足，则至少会将散列和/或排序的一些数据写入物理tempdb磁盘。这就是所谓的“泄漏”，它可能是一个非常缓慢的操作。您可以使用Profiler 散列警告和排序警告跟踪这些溢出(在Server 2008中)。

对于哈希表的构建输入的估计非常好：

对排序输入的估计不太准确：

您必须使用Profiler进行检查，但在这种情况下，我怀疑这种类型会溢出到tempdb。哈希表也有可能溢出，但不那么清晰。

请注意，为此查询保留的内存是在哈希表和排序之间分割的，因为它们同时运行。内存分数计划属性显示每个操作预期使用的内存授予的相对数量。

为什么排序和散列？

查询优化器引入排序，以确保行以群集键顺序到达聚集索引更新运算符。这促进了对表的顺序访问，这通常比随机访问效率高得多。

哈希连接是一个不太明显的选择，因为它的输入是类似的大小(无论如何，第一近似)。当一个输入(构建哈希表的输入)相对较小时，哈希连接是最好的。

在这种情况下，优化器的成本计算模型确定散列连接是三种选项(散列、合并、嵌套循环)中比较便宜的。

改进性能

成本模型并不总是正确的。它倾向于高估并行合并连接的成本，特别是随着线程数量的增加。我们可以使用查询提示强制合并连接：

UPDATE P
SET HHID = H.HHID
FROM dbo.households AS H
JOIN dbo.persons AS P
    ON P.tempId = H.tempId
    AND P.n = H.n
OPTION (MERGE JOIN);

这会产生一个不需要太多内存的计划(因为合并联接不需要哈希表)：

有问题的排序仍然存在，因为合并连接只保留其连接键(tempId，n)的顺序，但是集群键是(tempId、n、sporder)。您可能会发现合并连接计划的执行情况并不比散列连接计划好。

嵌套循环加入

我们还可以尝试嵌套循环连接：

UPDATE P
SET HHID = H.HHID
FROM dbo.households AS H
JOIN dbo.persons AS P
    ON P.tempId = H.tempId
    AND P.n = H.n
OPTION (LOOP JOIN);

此查询的计划是：

这个查询计划被优化器的成本计算模型认为是最糟糕的，但它确实有一些非常理想的特性。首先，嵌套循环联接不需要内存授予。其次，它可以保留Persons表中的键顺序，这样就不需要显式排序。你可能会发现这个计划执行得比较好，甚至足够好。

并行嵌套循环

嵌套循环计划的最大缺点是它运行在单个线程上。这个查询很可能从并行性中受益，但是优化器认为在这里这样做没有好处。这也不一定正确。不幸的是，没有内置查询提示来获得并行计划，但是有一种无文档化的方法：

UPDATE t1
  SET t1.HHID = t2.HHID
  FROM dbo.persons AS t1
  INNER JOIN dbo.households AS t2
  ON t1.tempId = t2.tempId AND t1.n = t2.n
OPTION (LOOP JOIN, QUERYTRACEON 8649);

使用QUERYTRACEON提示启用跟踪标志8649将产生以下计划：

现在，我们有了一个避免排序、不需要额外内存的连接并有效地使用并行性的计划。您应该会发现，这个查询的性能比其他查询要好得多。

在我的文章强制并行查询执行计划中，有关并行性的更多信息：

Answer 2

查看您的查询计划，可能真正的问题可能不是连接本身，而是实际的更新过程。

根据我所看到的，您很可能正在更新数据库中的所有person记录和更新索引(我不知道这有哪些其他索引，所以我不知道这是否是一个因素)

如果这是一次性任务，可以禁用索引、运行更新和重建索引吗？

一旦填充了数据，就可以向查询中添加where子句，只更新那些需要更新的记录。