sql - 为什么分解这个相关子查询 vaSTLy 可以提高性能？

Question

我尝试对两个大小非常不同的表运行此查询 - #temp 大约有 15,000 行，Member 大约有 70,000,000 行，其中大约 68,000,000 行没有 ID 307。

SELECT COUNT(*) 
FROM #temp
WHERE CAST(individual_id as varchar) NOT IN (
        SELECT IndividualID
        FROM Member m
        INNER JOIN Person p ON p.PersonID = m.PersonID
        WHERE CompanyID <>  307)

这个查询运行了 18 个小时，然后我终止了它并尝试了其他方法，即:

SELECT IndividualID
INTO #source
FROM Member m
INNER JOIN Person p ON p.PersonID = m.PersonID
WHERE CompanyID <> 307

SELECT COUNT(*)
FROM #temp
WHERE CAST(individual_id AS VARCHAR) NOT IN (
        SELECT IndividualID
        FROM #source)

在给我结果之前，它运行了不到一秒钟。

我对此感到非常惊讶。我是一个中间层开发人员而不是 SQL 专家，我对幕后发生的事情的理解有点模糊，但我会假设，因为我第一次尝试中的子查询是完全相同的代码，要求与第二次尝试完全相同的数据，这些数据大致相同。

但这显然是错误的。我无法查看原始查询的执行计划来了解 SQL Server 正在尝试执行的操作。那么有人可以解释一下为什么将数据拆分到临时表中要快得多吗？

编辑:表架构和索引

#temp 表有两列:Individual_ID int 和 Source_Code varchar(50)

Member 和 Person 更为复杂。它们分别有 29 和 13 栏，所以我不想完整地发布它们。 PersonID 是一个 int，是 Person 上的 PK 和 Member 上的 FK。 IndividualID 是 Person 的一列 - 这在所写的查询中并不清楚。

在提出问题之前，我尝试使用 LEFT JOIN 而不是 NOT IN。第二个查询的性能没有明显不同——两者都是亚秒级的。在第一个查询中，我让它运行一个小时然后停止它，假设它不会产生显着差异。

我还在#source 上添加了一个索引，就像在原始表上一样，因此性能影响应该是相同的。

Answer 1

首先，您的查询有两个非常明显的失礼之处。您正在转换为 varchar()，但不包含长度参数。这是不应该允许的!默认长度因上下文而异，您需要明确说明。

其次，您正在匹配不同表中的两个键，并且它们似乎具有不同的类型。外键引用应始终具有相同的类型。这会对性能产生很大的影响。如果您正在处理具有数百万行的表，那么您需要注意数据结构。

要了解性能差异，您需要了解执行计划。这两个查询有非常不同的执行计划。我(有根据的)猜测是第一个版本使用嵌套循环连接算法。第二个版本使用更复杂的算法。在您的情况下，这可能是由于 SQL Server 能够维护表的统计信息。因此，实例化中间结果实际上可以帮助优化器生成更好的查询计划。

如何最好地编写这个逻辑的主题已经被研究了很多。 Here Aaron Bertrand 对这个主题进行了非常好的讨论。

我确实同意 Aaron 的观点，在这种情况下优先选择不存在:

SELECT COUNT(*) 
FROM #temp t
WHERE NOT EXISTS (SELECT 1
                  FROM Member m JOIN
                       Person p
                       ON p.PersonID = m.PersonID
                  WHERE MemberID <>  307 and individual_id = t. individual_id
                 );

但是，我不知道在这种特殊情况下这是否会有更好的性能。