SQL Server ORDER BY Performance Aberration

Question

SQL Server 2008 running on Windows Server Enterprise(?) Edition 2008

I have a query joining against twenty some-odd tables (mostly LEFT OUTER JOINs). The full dataset returned by an unfiltered query returns less than 1,000 rows in less than 1s. When I apply a WHERE clause to filter the query it returns less than 300 rows in less than 1s.

When I apply an ORDER BY clause to the query it returns in 90s.

I examined the results of the query and notice a number of NULL results returned in the column that is being used to sort. I modified the query to COALESCE a NULL value to a valid search value without any change to the performance of the query.

I then did a

SELECT * FROM
(
my query goes here
) qry
ORDER BY myOrderByHere

And that produced the same results.

When I SELECT ... INTO #tempTable (without the ORDER BY) and then SELECT FROM the #tempTable with the order by the query returns in less than 1s.

What is really strange at this point is that the SELECT... INTO will also take 90s even without the ORDER BY.

The Execution Plan says that the SORT is taking 98% of the execution time when included with the main query. If I am doing the INSERT INTO the the explain plan says that the actual insert into the temp table takes 99% of the execution time.

And to take out server issues I have run the same tests on two different instances of SQL Server 2008 with nearly identical results.

Many thanks!

rjsjr

Answer 1

A sort operation is usually an expensive step in the query. So, it's not surprising that the addition of the sort adds time. You may be seeing similar results when you incorporate a temp table in your steps. The sort operation in your original query may use tempdb to help do the sort, and that can be the time-consuming step in each query you compare.

If you want to learn more about each query you're running, you can review query plan outputs.

Answer 2

Sounds like something strange is going on with your tempdb. Inserting 1000 rows in a temporary table should be fast, whether it's an implicit spool for sorting, or an explicit select into.

Check the size of your tempdb, the health of the hard disk it's on, and it's recovery model (should be simple, not full or bulk logged.)