INNER/RIGHT/LEFT JOIN 怎么会比 FULL JOIN 慢 14 倍? [英] How could a INNER/RIGHT/LEFT JOIN be 14x slower than a FULL JOIN?

问题描述

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

I have a query that takes 2.5 seconds with FULL JOIN, and 40 seconds with INNER, RIGHT or LEFT JOIN.

这是查询.子查询(执行两次)仅需要 1.3 秒.

Here is the query. The subquery (done twice) only takes 1.3 seconds on its own.

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 负载测试结果存储).以下是它的(相关)列:

The select SUBSTRING bit is just getting an HH:MM string out of a DateTime. LoadTestTransactionSample is actually a VIEW that joins across 8 tables. (FYI the database is a Visual Studio load test results store). Here are its (relevant) columns:

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

FULL JOIN 返回一个多余的不需要的行，所以我需要做一个 RIGHT JOIN 以获得正确的答案.

A FULL JOIN returns an extra unwanted row, so I do need to do a RIGHT JOIN to get the right answer.

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

I'm not really looking for a solution (I have one: pre-fetch the subquery into a table variable use SQL Server 2012 analytic function 'LAG', thanks @a1ex07), just some understanding as to what could possibly cause the extreme difference in performance between these join types.

这是慢右连接查询计划和快速全连接查询计划.它们太大了，无法发布屏幕截图.

Here's the slow right join query plan and the fast full join query plan. They are too big to post a screenshot.

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

EDIT 2: Actually the query plans have the RIGHT JOIN at 45% and FULL JOIN at 55%, which turns out to be utterly inaccurate (in reality it ends up worse than 99%/1%). I guess this means I have to rely on the actual execution statistics.

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

EDIT 3: Statistics for the slow 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.

快速完全连接的统计数据:

Statistics for the fast 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 执行更多的读取和更多的扫描，尽管查询计划明显相似.

The RIGHT JOIN is doing massively more reads and more scans than the FULL JOIN, despite an apparently similar query plan.

Worktable(完全连接)是一个提示吗?那是临时表吗?

Is Worktable (in FULL JOIN) a hint? Is that a temp table?

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

Does this seem to suggest that the query optimizer is broken?

推荐答案

好的，事实证明答案是:糟糕的数据库统计.很坏.就像，从未更新过.

OK, it turns out the answer is: bad db statistics. Very bad. As in, never been updated.

exec sp_updatestats; FTW.

_{[害羞地捂着头]}

这篇关于INNER/RIGHT/LEFT JOIN 怎么会比 FULL JOIN 慢 14 倍?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！