SQL Server Performance Forum – Threads Archive
Is order by really that bad?
Is order by really that bad even with an non-clustered index on that column name?What I have come to realize is that when an article or person says that ORDER BY is bad, or any SQL statement is bad for performance, they are saying that as a generalized statement. If your application needs the use of ORDER BY, then use it and understand that there is a performance hit with that, and try to optimize the best you can. Now this also depends on your situation, how much data you have, and alot of other things as well. In general, a clustered index is better for an ORDER BY operation. Hope that helps, Roy
"How do you expect to beat me when I am forever?"
Yeah I agree with Roy. If u need it, there is no other option. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Thanks for both of you. The tables I was talking about are moderate sized,
like hundread thausands rows most of the time. I agree that if I could
set a clustered index for this, that would be great, but to do that I
have to sacrifice other query performance. Right now I have only a
non-clustered index on this order by column.
You may check the link http://www.sql-server-performance.com/clustered_indexes.asp in this website about CI. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
It doesn’t have to be a clustered index on the sort column, so if you have a better choice for a cluster index, don’t worry. A non clustered will work just fine. Bambola.
Satya, you pointed me to an interesting article. Printed on 8 1/2 X 11
papers, on the bottom of pg 2, while the author was talking about ‘wide index’,
wide index for a clustered is favored: If you run into a circumstance where you need to have a single wide index (a composite index of three or more columns) in a table, and the rest of the indexes in this table (assuming there are two or more) will only be one column wide, then consider making the wide index a clustered index and the other indexes non-clustered indexes. But in the middle of pg 3, when both clustered and non-clustered indexes are
presented, narrow/single column is favored: If a table has both a clustered index and non-clustered indexes, then performance will be best optimized if the clustered index is based on a single column that is as narrow as possible. This is because non-clustered indexes use the clustered index to locate data rows and because non-clustered indexes must hold the clustered keys within their B-tree structures. This helps to reduce not only the size of the clustered index, but all non-clustered indexes on the table as well. [6.5, 7.0, 2000] Am I understood this correctly?
Both cases correct. Refer thru other articles that might help you to understand. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>