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

我有一个查询，使用 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

The select SUBSTRINGbit 只是从 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

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

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

EDIT:这是慢速右连接查询计划 http://sdrv.ms/10hzuky和快速全连接查询计划 http://sdrv.ms/10hB2Lk。它们太大了，无法发布屏幕截图。

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

EDIT 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 执行的读取和扫描要多得多。

Is 工作表（在 FULL JOIN 中）提示？那是临时表吗？

这似乎表明查询优化器已损坏？

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

exec sp_updatestats; FTW.

_{[hides head in shame]}