sum grouping causing long run time? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sum grouping causing long run time?

Author

Topic

sqlclarify
Yak Posting Veteran

56 Posts

Posted - 2009-01-30 : 21:38:52

Hello,

I have a query of the form

SELECT tableA.colA, tableB.colq
FROM table A
JOIN table B
ON tableA.colC = tableB.colC

This works in a reasonable amount of time. But the second I try to take sums by grouping on colq that is, shown below, the query seems to run forever.. How do I solve this?

SELECT SUM(colA), tableB.colq
FROM table A
JOIN table B
ON tableA.colC = tableB.colC
group by colq

takes forever to run?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-30 : 21:42:51

How big are those tables? Do you have proper indexes on joined columns and Aggregate? What does your execution plan say?

sqlclarify
Yak Posting Veteran

56 Posts

Posted - 2009-01-30 : 22:29:09

The table is huge with around 150,000. The view (which I'm joining with) is 1,000,000 rows. I'm not sure what you mean by execution plan. The database has been designed by someone else so there is nothing I can do about the indexes. .. Is there a way to get around this problem?

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-01-30 : 23:02:01

Try to have a aggregate function on tableB.colq like SUM or MAX

Avantha Siriwardana
Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-31 : 00:26:25

There are potentially 150,000 x 1,000,000 records to scan, which is 150,000,000,000 records.
It adds up quickly.

From which table do column ColA derive?

E 12°55'05.63"
N 56°04'39.26"

sqlclarify
Yak Posting Veteran

56 Posts

Posted - 2009-01-31 : 01:08:30

col A is from table A

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-31 : 03:31:52

SELECT SUM(a.colA) * b.yak, B.colq
FROM (select sum(cola) as cola, colc from tableA group by colc) AS a
inner JOIN (select colq, colc, count(*) as yak from tableB group by colq, colc) as b ON A.colC = B.colC
group by b.colq

E 12°55'05.63"
N 56°04'39.26"

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-02-01 : 07:03:21

Without knowing what you are doing, it would appear that this is unlikely to be the full query. There is no WHERE statement so you are calculating 150,000 totals from a million records. It's not huge, but not trivial either as there is no way to prevent both tables from being scanned & processed at least once. If this is truly the end of the question then perhaps an index on TableA.ColC and TableA.ColA in that order. That will allow parts of it to be performed using data only stored in the index (a covering index) which will reduce IO significantly by skipping access to TableA.
Also consider denormalising a moving colQ to tableA and the index.
Can you give a clue as to what the tables & columns represent? I can't quite see what you are doing here.

sqlclarify
Yak Posting Veteran

56 Posts

Posted - 2009-02-01 : 18:04:50

Ok, looks like the problem is solved :)

SELECT SUM(colA), tableB.colq
FROM table A
JOIN table B
ON tableA.colC = tableB.colC
group by colq

What I did is summed Col A grouped by col C first..

so I have (select sum(col A), colC
group by colC)
This reduced the rows to like 25 rows.. so when I joined it , it speeded things up...and THEN I joined this to table B.. and then grouped by ColQ

Thank you everybody for your help :)))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-02 : 00:52:35

You are welcome!
I guess you were inspired by this post 01/31/2009 : 03:31:52 ?

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources