title: SQL Server Query - ORDER BY killing query performance on small result set
body: <p>I have a query in SQL Server 2008 R2 in the following form:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The <code>hierarchy</code> subquery contains UNIONS and INNER JOINS. The <code>expenditure</code> subquery is based on several levels of sub-subqueries, and contains UNIONS, INNER and LEFT JOINS, and ultimately, a PIVOT aggregate.</p>
  
  <p>The <code>hierarchy</code> subquery by itself runs in 2 seconds and returns 467 rows. The <code>expenditure</code> subquery by itself runs in 7 seconds and returns 458 rows. Together, <em>without</em> the <code>ORDER BY</code> clause, the query runs in 11 seconds. However, <em>with</em> the <code>ORDER BY</code> clause, the query runs in 11 <strong>minutes</strong>.</p>
  
  <p>The Actual Execution Plan reveals what's different. Without the <code>ORDER BY</code> clause, both the <code>hierarchy</code> and <code>expenditure</code> subqueries are running once each, with the results being <code>Merge Join (Right Outer Join)</code> joined together. When the <code>ORDER BY</code> clause is included, the <code>hierarchy</code> query is still run once, but the <code>expenditure</code> portion is run once <strong>per row from the hierarchy query</strong>, and the results are <code>Nested Loops (Left Outer Join)</code> joined together. Its as if the <code>ORDER BY</code> clause is causing the <code>expenditure</code> subquery to become a correlated subquery (which it is not).</p>
  
  <p>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 <em>without</em> the <code>ORDER BY</code> clause into it. Then I did a <code>SELECT * FROM #temp_table ORDER BY c, d, e</code>. The entire script took the expected 11 seconds, and returned the desired results.</p>
  
  <p>I want to make the query work efficiently with the <code>ORDER BY</code> clause as one query--I don't want to have to create a stored procedure just to enable the #temp_table hacky solution.</p>
  
  <p>Any ideas on the cause of this issue, or a fix?</p>