sql-server - INNER/RIGHT/LEFT JOIN 怎么会比 FULL JOIN 慢 14 倍？

Question

我有一个查询，使用 FULL JOIN 需要 2.5 秒，使用 INNER、RIGHT 或 LEFT JOIN 需要 40 秒。

这是查询。子查询(完成两次)本身只需要 1.3 秒。

SELECT T1.[time], T1.Total, T1.rn, T2.[time], T2.Total, T2.rn
FROM
(
select [time], MAX(ComputedValue) as Total, row_number() over (order by [time]) as rn
FROM
(
    select SUBSTRING(CONVERT(CHAR(10), IntervalStartTime, 108), 0, 6) as [time], ComputedValue
    from LoadTestTransactionSample
    where LoadTestRunId=285
    and CounterName='Total Transactions' 
    and TransactionName='Export'
) foo
group by [time]
) T1
_____ JOIN
(
select [time], MAX(ComputedValue) as Total, row_number() over (order by [time]) as rn
FROM
(
    select SUBSTRING(CONVERT(CHAR(10), IntervalStartTime, 108), 0, 6) as [time], ComputedValue
    from LoadTestTransactionSample
    where LoadTestRunId=285
    and CounterName='Total Transactions' 
    and TransactionName='Export'
) foo
group by [time]
) T2
ON T1.rn = T2.rn - 1

select SUBSTRING 位只是从 DateTime 中获取 HH:MM 字符串。 LoadTestTransactionSample 实际上是一个跨 8 个表连接的 VIEW。 (仅供引用，该数据库是 Visual Studio 负载测试结果存储)。以下是其(相关)列:

LoadTestRunId INT NOT NULL
CounterName NVARCHAR(255) NOT NULL
TransactionName NVARCHAR(64) NOT NULL
IntervalStartTime DATETIME NOT NULL
IntervalEndTime DATETIME NOT NULL
ComputedValue REAL

完整连接会返回额外不需要的行，因此我确实需要执行 RIGHT JOIN 才能获得正确的答案。

我并不是真的在寻找解决方案(我有一个: 将子查询预取到表变量 使用 SQL Server 2012 分析函数“LAG”，感谢@a1ex07)，只是对什么可能导致这些连接类型之间的性能存在巨大差异有一些了解。

<小时/>

编辑:这是slow right join query plan和 fast full join query plan 。它们太大了，无法发布屏幕截图。

编辑2:实际上，查询计划的 RIGHT JOIN 为 45%，FULL JOIN 为 55%，结果证明这是完全不准确的(实际上它最终比 99%/1% 更糟糕)。我想这意味着我必须依赖实际的执行统计数据。

编辑3:慢速 RIGHT JOIN 的统计信息:

(40 row(s) affected)
Table 'LoadTestPerformanceCounterCategory'. Scan count 0, logical reads 37556, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LoadTestPerformanceCounter'. Scan count 0, logical reads 176464, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LoadTestScenario'. Scan count 0, logical reads 176464, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LoadTestCase'. Scan count 0, logical reads 176464, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'WebLoadTestTransaction'. Scan count 0, logical reads 13411100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LoadTestPerformanceCounterInstance'. Scan count 0, logical reads 36563718, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LoadTestPerformanceCounterSample'. Scan count 19721, logical reads 269657, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LoadTestRunInterval'. Scan count 41, logical reads 205, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
    CPU time = 36754 ms,  elapsed time = 36763 ms.

快速 FULL JOIN 的统计:

(41 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LoadTestPerformanceCounterCategory'. Scan count 0, logical reads 1832, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LoadTestPerformanceCounter'. Scan count 0, logical reads 8608, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LoadTestScenario'. Scan count 0, logical reads 8608, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LoadTestCase'. Scan count 0, logical reads 8608, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'WebLoadTestTransaction'. Scan count 0, logical reads 654200, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LoadTestPerformanceCounterInstance'. Scan count 0, logical reads 1783596, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LoadTestPerformanceCounterSample'. Scan count 962, logical reads 13154, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LoadTestRunInterval'. Scan count 2, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
    CPU time = 1950 ms,  elapsed time = 1944 ms.

尽管查询计划明显相似，但 RIGHT JOIN 比 FULL JOIN 执行的读取和扫描要多得多。

工作表(在 FULL JOIN 中)是一个提示吗？这是临时表吗？

这是否表明查询优化器已损坏？

Answer 1

好吧，事实证明答案是:错误的数据库统计信息。很坏。就像，从未更新过。

exec sp_updatestats; FTW。

_{[羞愧地低下头]}