Can SQL optimize an Order By clause?

In doing some analysis of our queries, we let users dynamically sort
data in a resulting grid, and, consistently, sorted queries are much
slower than non-sorted queries. Can SQL use indexes to get speed up a
query? For example, say we have a Orders table with 100 million
orders, clustered index on orderID, and we want to get the top 10
orders of all time, can sql server use an index on orderTotal to speed
up the query time of:
select top 10 orderID, orderTotal from orders order by orderTotal desc
Obviously, the real business case is more complex than this, but I want
to know if SQL server can use an index to just get the top 10 without
having to do a full table scan. From my tests, that doesn't appear to
be the case.Yes, for such a high-selectivity query, an index can be used. SQL Server would use the index on
OrderTotal to find the highest value. For the 10 first rows, it will use the pointer in that index
to go fetch the data row, in descending order. The lower selectivity you have, the less of a perf
gain you see by using the index, as a data page has to be visited *for each row* to be returned. Too
low selectivity, it is cheaper to scan the table (or use some other index) and then do the sort. I
recommend you check the execution plan, try some index hint, perhaps even the FIRST hint and also
make sure that statistics are up to date.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Xavryn@.gmail.com> wrote in message news:1134664775.545525.108690@.g43g2000cwa.googlegroups.com...
> In doing some analysis of our queries, we let users dynamically sort
> data in a resulting grid, and, consistently, sorted queries are much
> slower than non-sorted queries. Can SQL use indexes to get speed up a
> query? For example, say we have a Orders table with 100 million
> orders, clustered index on orderID, and we want to get the top 10
> orders of all time, can sql server use an index on orderTotal to speed
> up the query time of:
> select top 10 orderID, orderTotal from orders order by orderTotal desc
> Obviously, the real business case is more complex than this, but I want
> to know if SQL server can use an index to just get the top 10 without
> having to do a full table scan. From my tests, that doesn't appear to
> be the case.
>