SQL Server Query - ORDER BY killing query performance on small result set

Question

I have a query in SQL Server 2008 R2 in the following form:

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

The hierarchy subquery contains UNIONS and INNER JOINS. The expenditure subquery is based on several levels of sub-subqueries, and contains UNIONS, INNER and LEFT JOINS, and ultimately, a PIVOT aggregate.

The hierarchy subquery by itself runs in 2 seconds and returns 467 rows. The expenditure subquery by itself runs in 7 seconds and returns 458 rows. Together, without the ORDER BY clause, the query runs in 11 seconds. However, with the ORDER BY clause, the query runs in 11 minutes.

The Actual Execution Plan reveals what's different. Without the ORDER BY clause, both the hierarchy and expenditure subqueries are running once each, with the results being Merge Join (Right Outer Join) joined together. When the ORDER BY clause is included, the hierarchy query is still run once, but the expenditure portion is run once per row from the hierarchy query, and the results are Nested Loops (Left Outer Join) joined together. Its as if the ORDER BY clause is causing the expenditure subquery to become a correlated subquery (which it is not).

To verify that SQL Server was actually capable of doing the query and producing a sorted result set in 11 seconds, as a test, I created a temp table and inserted the results of the query without the ORDER BY clause into it. Then I did a SELECT * FROM #temp_table ORDER BY c, d, e. The entire script took the expected 11 seconds, and returned the desired results.

I want to make the query work efficiently with the ORDER BY clause as one query--I don't want to have to create a stored procedure just to enable the #temp_table hacky solution.

Any ideas on the cause of this issue, or a fix?

How many estimated and actual rows feeding into the nested loops? Are statistics up to date? — Martin Smith, Jun 13 '13 at 16:35
Sorting the output of a bunch of "union and inner joins" can't be reliably compared to sorting the output as a whole once placed into a #temp table. Are there indexes on the underlying tables that support the order by? Are they unions or union alls? What on earth is TOP (2147483647) doing for you? Enabling you to do awful things like put this query into a view definition with the order by clause? — Aaron Bertrand, Jun 13 '13 at 16:44
@MartinSmith: Good idea. 1 relevant index in a subquery in the `expenditures` subquery did not have updated statistics. After updating, there was unfortunately no improvement. From the updated Actual Execution Plans: in the non-`ORDER BY` version, the `hierarchy` subquery has an estimated 343 rows (467 actual), and the `expenditure` subquery has an estimated 106,245,000 (459 actual). In the `ORDER BY` version, the `hierarchy` subquery has an estimated 343 rows (467 actual), and the `expenditure` subquery has an estimated 104,648 rows (459 actual). — pcronin, Jun 14 '13 at 08:04
@AaronBertrand: The test involving the #temp_table was to verify that SQL Server was in fact capable of producing the results I desired in a reasonable time. I am aware that the comparison of the mechanism of the two is not useful. The `hierarchy` subquery has 3 `UNION ALL`'s and the `expenditure` subquery has many, many `UNION ALL`'s and some `UNION`'s where necessary. The underlying tables contain indexes to support the `ORDER BY` clause. You are correct about TOP (2147483647); I have a reason for it, and no views or queries will be built or run on top of this query. Any ideas to improve? — pcronin, Jun 14 '13 at 08:15

score 1 · Answer 1 · answered Jun 13 '13 at 16:57

1

To avoid nested loop joins, you can give an option to the compiler:

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)

I generally much prefer to have the optimizer figure out the right query plan. On rare occasions, however, I run into a problem similar to yours and need to make a suggestion to push it in the right direction

answered Jun 13 '13 at 16:57

Gordon Linoff

1,242,037
58
646
786

I tried this, but the query doesn't get run. Instead, I get the following error: Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using `SET FORCEPLAN`. – pcronin Jun 14 '13 at 08:16
@pcronin . . . You must have some very interesting subqueries. All I can say is that this has worked for me when I've needed it. I'll leave the answer up in case anyone else has the same idea. – Gordon Linoff Jun 14 '13 at 09:35
I've avoided writing the contents of the subqueries on the site as it would take a few thousand lines. sub_query_b, especially, is a view based on several layers of views. At the moment I can think of a few places for improvement, but nothing that I can see would yield tremendous improvement. Definitely leave your answer up, as it will probably help people, even me, in the future. – pcronin Jun 14 '13 at 09:53

score 1 · Accepted Answer · answered Jun 14 '13 at 10:07

Thanks to @MartinSmith's comment, I got looking at what could cause the major discrepancies between the estimated and actual rows delivered by the expenditure subquery in the non-ORDER BY version, even though I eventually wanted to ORDER it. I thought that perhaps if I can optimize that version a bit, perhaps that would also benefit the ORDER BY version as well.

As I mentioned in the OP, the expenditure subquery contains a PIVOT aggregation across yet another subquery (let's call it unaggregated_expenditure). I added a layer between the PIVOT and the unaggregated_expenditure subquery which aggregated the required column before PIVOTing the same column across the required few pivot columns. This added a bit of conceptual complexity, yet was able to reduce the estimated number of rows coming from the PIVOT from 106,245,000 to 10,307. This change, when applied to the ORDER BY version of the whole query, resulted in a different Actual Execution Plan that was able to process and deliver the query within the desired 11 seconds.

SQL Server Query - ORDER BY killing query performance on small result set

2 Answers2