Indexing: Required for Columns Used in ORDER BY or GROUP BY Clauses?

Question

Indexing: Required for Columns Used in ORDER BY or GROUP BY Clauses?

Gail Wanabee

SSCrazy Eights

Points: 8471
More actions
February 3, 2010 at 7:05 pm

#138662

My thoughts are that since the ORDER BY and GROUP BY clauses are applied to the intermediate resultset of a SQL query, indexing columns referenced in these clauses is pointless.
However, I've had an MVP tell me otherwise and insist that columns used in these clauses should be indexed for best (SELECT) query performance.
I'd like to read some knowledgeable and informed opinions on this subject.
Thanks,
LC

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

DHeath SSCertifiable Points: 5200 More actions · Answer 1

Hey there,

I am not one for the how fast can this versus that run and thats mostly because the difference is usually minimal. I have learned that over the years the indexes help tremendously but something that is often over-looked is a well written query versus and query that is not well written. I would think that your ORDER BY and GROUP BY will give different results if the queries were good ones vs bad ones. NOW hehehe with that said if you have both well written queries and they are running on the same tables or databases then i think your best bet would be to use the execution plan as that will be able to break it down for you much better so that you can actually see where the "bottleneck" is first hand versus reading what someone else has experienced. Also i can bet you that in either case if you have bad written queries against unindexed data you will have slow performance of that particular query then again..what is considered slow.. =]

Good luck in your search for the answer... hehehe

-D-

DHeath

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

It depends on the query and the execution plan. You should consult your execution plan to determine where the sticking points in your query are. If an index on an order by column helps performance - why not do it? There is also the associated cost with having too many indexes. Index judiciously in an OLTP environment such that your queries will perform optimally and inserts will not be slowed too terribly by too many indexes.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 3

Thanks for both of your replies.

I review all queries submitted by our development staff so I know that they are all correctly and efficiently written, or I specify changes to make them correctly and efficiently written. I also am responsible for 100% of all index creations.

Your comments about evaluating each query's Execution Plan are good advice and well taken; I've done it on occasion, when a performance problem has been discovered. However, I simply do not have the time to evaluate the E.P. for every query and the effects of every index (for instance: our last software release required the deployment of 168 indexes in support of several hundred new queries). Plus, the E.P. for a query evaluated on a currently empty table does not always give the correct information; without a data population, how can it?

That is why I created this post; I need to know the best guidelines for index creation in advance. I already know them except for the ORDER BY and GROUP BY clauses. These 2 clauses are still question marks for me.

Sincerely,

LC

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

It is possible to configure indexes so that they can be used for Order By and Group By, it's difficult, it cannot always be done. It's something that I put into the category of 'advanced indexing'

The trick to remember for both of those is that indexes are sorted and, if the keys are correct, can be used to remove the need for SQL to do a sort. (can, maybe, depending on the query). It's not something you would add a separate index for, it's something that you might widen an existing index for.

Generally I would say stick to indexing for where and join and, if you do have an opportunity to get an index to provide a sort order, great, but don't focus on it. The biggest gain in indexing is the where/join.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 5

Thanks for the details, Gail.

I should have posted my question about indexing ORDER BY and GROUP BY clauses as a Best Practices question. I think that would have been more accurate.

Thanks to all of you for your replies.

LC

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events