Large number of group bys

Question

Post reply

Large number of group bys

urmarke

Valued Member

Points: 55
More actions
August 31, 2010 at 5:18 pm

#239793

Hi
i have a query that is taking an inordinate amount of time to run. ie. many hours
Looking into the code I see it has a fairly small where statement selecting dates via a date range but extraordinarily it has 25+ group bys !!
My questions are:
1. do group bys use indexes or is that just plain silly? I think not but just want some confirmation.
2. Given that the group by is cause of the problem, is there a viable alternative? Its in a very sensitive area of the system and changing it can have a huge knock on impact downstream so I'm after minimal change with maximum performance gain.
Thanks for any help

Viewing 12 posts - 1 through 11 (of 11 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

1. Yes, group by does use indexes, if present.

2. It sounds to me more an aspect of having large tables without supporting indexes.

Can you post an execution plan? This would help us figure out what's going on.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ramji29 Hall of Fame Points: 3832 More actions · Answer 2

I'll request you to include the table definitions along with the indexes.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

urmarke (8/31/2010)
Looking into the code I see it has a fairly small where statement selecting dates via a date range but extraordinarily it has 25+ group bys !!

What does this mean?

Does it mean you have one GROUP BY statement including 25 columns, or 25 GROUP BY for 25 derived tables or subqueries?

N 56°04'39.16"
E 12°55'05.25"

urmarke Valued Member Points: 55 More actions · Answer 4

Hi All

Thanks for your comments.

To clarify, the query is a single group by with 25 plus columns, including case statements using the in command. eg Case When column In ('[a]','') Then X . There is also a convert date in the group by clause as well. Do case and convert statements also use indexes?

Ramji29 – how does including indexes in the table definition differ from creating indexes normally, outside of the table definition?

I think you’re right, it probably is a case of missing indexes but I’m not sure how to determine what’s missing.

Rather than post the execution plan, can someone point me towards a primer on how to read an execution plan generated by the ‘Display Estimated Execution Plan’ button in Management Studio

Thanks again everyone.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

urmarke (9/1/2010)
To clarify, the query is a single group by with 25 plus columns, including case statements using the in command. eg Case When column In ('[a]','') Then X . There is also a convert date in the group by clause as well. Do case and convert statements also use indexes?

No, they don't.

What are you trying to do? Maybe using windowed functions would be a better choice for you?

N 56°04'39.16"
E 12°55'05.25"

Ramji29 Hall of Fame Points: 3832 More actions · Answer 6

urmarke (9/1/2010)
Hi All
Ramji29 – how does including indexes in the table definition differ from creating indexes normally, outside of the table definition?

What I meant was when you post the table definitions then post the index structure also.... 🙂

You're right it doesn't make any difference 😎

Ramji29 Hall of Fame Points: 3832 More actions · Answer 7

urmarke (9/1/2010)
Hi All
Rather than post the execution plan, can someone point me towards a primer on how to read an execution plan generated by the ‘Display Estimated Execution Plan’ button in Management Studio

See before jumping into execution plan you must need to undst the basics of sql server internals & indexing like index scan, index seek, bookmark lookup, agg, logical reads etc....

As per my exp what I'll suggest you is look at the step which is taking most of the query cost and then review that step as what exactly that particular step is doing and what we can do in terms of addition or altering index or changing any join logic etc. to bring it down....

Plz refer below links to start with and you can always do google and check what means what.....:-)

http://www.sql-server-performance.com/tips/query_execution_plan_analysis_p1.aspx

http://geekswithblogs.net/evankoch/archive/2007/08/14/114677.aspx

http://www.simple-talk.com/sql/performance/execution-plan-basics/

I hope it helps...... See without the table definition & the query.... We won't be of much help.....

Rohit

Paul White SSC Guru Points: 150467 More actions · Answer 8

An excessive number of columns in a GROUP BY clause is almost always a sign of a database design or query that has considerable scope for improvement 🙂

Often, the GROUP BY with 'n' columns is equivalent to a DISTINCT. You should review the query to see why it is producing duplicate intermediate rows, and fix the cause rather than the effect.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

urmarke Valued Member Points: 55 More actions · Answer 9

Hi

Sorry for the delay in posting back to you all. The issue hasnt been resolved but we've got a work around so its okay for now. I have certainly highlighted the procedure to our client and now await their decision as to whether we should change it. Anway, i shall of course take your valuable comments on board and make good use of them.

By the way, thank you for your prompt responses and for sharing your knowledge. Every reply is really appreciated.

SQlServerCentral is now on my favourites list.

Thanks again.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 10

urmarke (10/2/2010)
The issue hasnt been resolved but we've got a work around so its okay for now.

What kind of workaround ? any index or query adjustment ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 11

1) Our own (SSC.com) Grant Fritchey has a wonderful and FREE ebook on query execution plans here: http://www.sqlservercentral.com/articles/books/65831/

2) review actual execution plan from execution, and SET STATISTICS IO ON and review reads from each table.

3) You sound like a novice at tuning, but there are a TON of other things you can do to evaluate what is causing this particular query to be slow, and most of them have nothing to do with the query plan. See if you can get your company to bring a mentor on board to a) improve your current systems and queries and b) teach you how to do the same. win-win-win

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service