By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,600 Members | 1,683 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,600 IT Pros & Developers. It's quick & easy.

Adding Order by on clustered index

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
> 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

Jul 20 '05 #2

P: n/a
> 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.
Jul 20 '05 #3

P: n/a
> 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.

Jul 20 '05 #4

P: n/a
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
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.