SQL Server Query - ORDER BY在小结果集上杀死查询性能

Question

我在SQL Server 2008 R2中有一个以下形式的查询:

SELECT TOP (2147483647) *
FROM (
    SELECT *
    FROM sub_query_a
) hierarchy
LEFT JOIN (
    SELECT *
    FROM sub_query_b
) expenditure
ON hierarchy.x = expenditure.x AND hierarchy.y = expenditure.y
ORDER BY hierarchy.c, hierarchy.d, hierarchy.e

hierarchy子查询包含union和INNER join。expenditure子查询基于几个级别的子查询，包含union、INNER和LEFT join，最终包含一个PIVOT聚合。

hierarchy子查询本身在2秒内运行并返回467行。expenditure子查询本身运行时间为7秒，并返回458行。如果不带 ORDER BY子句，则查询运行时间为11秒。然而，与 ORDER BY子句，查询运行在11 分钟。

实际执行计划揭示了不同之处。如果没有ORDER BY子句，则hierarchy和expenditure子查询各运行一次，结果将被Merge Join (Right Outer Join)连接在一起。当包含ORDER BY子句时，hierarchy查询仍然运行一次，但是expenditure部分在层级查询的每行运行一次，结果被Nested Loops (Left Outer Join)连接在一起。这就好像ORDER BY子句导致expenditure子查询成为一个相关子查询(它不是)。

为了验证SQL Server实际上能够在11秒内执行查询并生成排序的结果集，作为测试，我创建了一个临时表，并将查询的结果插入其中，而不包含 ORDER BY子句。然后我做了一个SELECT * FROM #temp_table ORDER BY c, d, e。整个脚本花费了预期的11秒，并返回了所需的结果。

我想让ORDER BY子句作为一个查询有效地工作——我不想仅仅为了启用#temp_table破解解决方案而创建一个存储过程。

对这个问题的原因或修复有什么想法吗?

Answer 1

为了避免嵌套循环连接，您可以给编译器指定一个option:

SELECT TOP (2147483647) *
FROM (
    SELECT *
    FROM sub_query_a
) hierarchy
LEFT JOIN (
    SELECT *
    FROM sub_query_b
) expenditure
ON hierarchy.x = expenditure.x AND hierarchy.y = expenditure.y
ORDER BY hierarchy.c, hierarchy.d, hierarchy.e
option (merge join, hash join)

我通常更喜欢让优化器找出正确的查询计划。然而，在极少数情况下，我遇到了类似于你的问题，需要提出建议，推动它朝着正确的方向

Answer 2

感谢@MartinSmith的评论，我看到了什么可能导致expenditure子查询在非ORDER BY版本中交付的估计和实际行之间的主要差异，尽管我最终想要ORDER它。我想，如果我能稍微优化一下这个版本，也许也会对ORDER BY版本有好处。

正如我在OP中提到的，expenditure子查询在另一个子查询(我们称之为unaggregated_expenditure)中包含PIVOT聚合。我在PIVOT和unaggregated_expenditure子查询之间添加了一个层，该层在PIVOT之前聚合了所需的列，然后跨所需的几个枢轴列使用相同的列。这增加了一点概念上的复杂性，但是能够将来自PIVOT的估计行数从106,245,000减少到10,307。当将此更改应用于整个查询的ORDER BY版本时，会产生一个不同的实际执行计划，该计划能够在所需的11秒内处理和交付查询。