Adding Order by on clustered index

[Tryfon Gavriel]

Hi all

I recently noticed when trying to optimise a major query of a chess website
I am the webmaster of, that adding an order by for "gamenumber" which is a
clustered index field as in for example "order by timeleft desc, gamenumber
desc" actually speeded up the queries and reduced sql server 2000 timeouts.
I have an ASP error log and I am fairly sure that a dramatic reduction in
sql server timeouts is simply attributed to adding an extra seemingly
redundant order by field - which is the clustered index. Is this phenomena
at all possible or is it my imagination?!

Other special attributes of the query includes the use of "Top" to obtain a
maximum specified number of rows. Perhaps it is just the unique
characteristics of the query, but I would have thought that the less order
by fields would imply faster performance. Has anyone else noticed that a
seemingly redundant order by column on for example the clustered index
column, can actually help speed up queries?!

Best wishes
Tryfon Gavriel
Webmaster
www.chessworld.net

[Dan Guzman]

> I have an ASP error log and I am fairly sure that a dramatic reduction in

sql server timeouts is simply attributed to adding an extra seemingly
redundant order by field - which is the clustered index. Is this
phenomena
at all possible or is it my imagination?!
Apparently, SQL Server is choosing a more efficient execution plan to
satisfy your ORDER BY specification. This implies that the plan without the
ORDER BY is sub-optimal and may be an indication that statistics need to be
updated. There may be other factors but it's difficult to say without DDL
and sample data that demonstrates the problem.

ORDER BY is never redundant. Regardless of any indexes, SQL Server can
return rows in any sequence it deems appropriate unless ORDER BY is
explicitly specified. Similarly TOP n is meaningless without ORDER BY. If
ORDER BY is not specified, any rows can be returned.
--
Hope this helps.

Dan Guzman
SQL Server MVP

"Tryfon Gavriel" <tr****@gtryfon.demon.co.uk> wrote in message
news:cn*******************@news.demon.co.uk... Hi all

I recently noticed when trying to optimise a major query of a chess
website
I am the webmaster of, that adding an order by for "gamenumber" which is a
clustered index field as in for example "order by timeleft desc,
gamenumber
desc" actually speeded up the queries and reduced sql server 2000
timeouts.
I have an ASP error log and I am fairly sure that a dramatic reduction in
sql server timeouts is simply attributed to adding an extra seemingly
redundant order by field - which is the clustered index. Is this
phenomena
at all possible or is it my imagination?!

Other special attributes of the query includes the use of "Top" to obtain
a
maximum specified number of rows. Perhaps it is just the unique
characteristics of the query, but I would have thought that the less order
by fields would imply faster performance. Has anyone else noticed that a
seemingly redundant order by column on for example the clustered index
column, can actually help speed up queries?!

Best wishes
Tryfon Gavriel
Webmaster
www.chessworld.net

[Forrest]

> Other special attributes of the query includes the use of "Top" to obtain a

maximum specified number of rows. Perhaps it is just the unique
If you just added a Top N, that should have a dramatic impact.
characteristics of the query, but I would have thought that the less order
by fields would imply faster performance. Has anyone else noticed that a
seemingly redundant order by column on for example the clustered index
column, can actually help speed up queries?!

If the query was sorting on anything other than the clustered index,
or if the records were coming out in no particular order, and not in
order by the clustering, that will help, too.

A "clustered" table is saved in order, row-by-row, so when you want
your data back in order, it doesn't have to sort the data. Just do a
table scan, and they'll come back already sorted. So if you sort on
something else, you're giving up that optimization, and then having to
sort on something else, too.

[Dan Guzman]

> A "clustered" table is saved in order, row-by-row, so when you want

your data back in order, it doesn't have to sort the data. Just do a
table scan, and they'll come back already sorted.

Just to be clear, SQL server may choose an access method that causes rows to
be returned out-of-sequence even with a simple query against a table with a
clustered index. ORDER BY *must* be specified when you want to ensure data
are returned in a particular order.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Forrest" <Va************@gmail.com> wrote in message
news:9d**************************@posting.google.c om...

Other special attributes of the query includes the use of "Top" to obtain
a
maximum specified number of rows. Perhaps it is just the unique

If you just added a Top N, that should have a dramatic impact.

characteristics of the query, but I would have thought that the less
order
by fields would imply faster performance. Has anyone else noticed that a
seemingly redundant order by column on for example the clustered index
column, can actually help speed up queries?!

If the query was sorting on anything other than the clustered index,
or if the records were coming out in no particular order, and not in
order by the clustering, that will help, too.

A "clustered" table is saved in order, row-by-row, so when you want
your data back in order, it doesn't have to sort the data. Just do a
table scan, and they'll come back already sorted. So if you sort on
something else, you're giving up that optimization, and then having to
sort on something else, too.

[Erland Sommarskog]

Dan Guzman (gu******@nospam-online.sbcglobal.net) writes:

A "clustered" table is saved in order, row-by-row, so when you want
your data back in order, it doesn't have to sort the data. Just do a
table scan, and they'll come back already sorted.

Just to be clear, SQL server may choose an access method that causes
rows to be returned out-of-sequence even with a simple query against a
table with a clustered index. ORDER BY *must* be specified when you
want to ensure data are returned in a particular order.

Just to elaborate on Dan's comment a little more. In SQL Server 6.5 you
could be fairly sure that the rows would be returned in order of the
clustered index if you said things like:

SELECT * FROM tbl

But in SQL7 and later, SQL Server may open parallel streams on the table,
and the result is likely to be semi-ordered (which could be deceivable).
This is particularly true for large queries.

(In all versions of SQL Serger, a WHERE clause a on column with a non-
clustered index or a Select list covered by a non-covered index is likely
to use that index as the starting point for the return order.)

Thus, to echo Dan: you *must* specify ORDER BY if you want a certain order.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp