sql server programming: I have a pretty large query.

It is selecting a distinct 2000 records.

If the query returns 2000 records...it takes 11 seconds to run.
There is a single order by clause on one column...a datetime column.
The query execution plan shows the SORT as taking 75% of cost.

Taking out that order by allows the query to run in 1 second.
I've tuned the crap out of the query...so I don't think any more
indexes can help.

Are 'order by' clauses bad for queries? Should the sorting be done on
the application side?
It's a cold fusion app.

Thanks.

(mchi55@hotmail.com) writes:
[quoted text, click to view]

It had helped if you had posted the query.

If the query includes a TOP clause, the ORDER BY makes a lot of difference.
If you say:

SELECT TOP 2000 ... FROM tbl

all the engine has to do is to go and grab any 2000 rows. If you add an
ORDER BY clause, the engine will have to read the entire table.

Adding an ORDER BY clause does of course add a cost to the query,
but a difference of 10 seconds to sort 2000 rows smells like there
is something more to it.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at