Group by - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Group by

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-05-23 : 09:01:17

Barry writes "Hey guys,

from reading the articles herein about group by, I believe that Method 2 below should return result faster than Method 1. Any ideas why this does not appear to be the case on the datasets I have tested. Can only get a difference of a second or 2 better performance from method 2, when results take approximatley 3 mins to return.

-- Method 1
select
DebtMaster.ref_id,
DebtMaster.name,
sum(DebtTransactions.gross_val)
from
DebtMaster
inner join DebtTransactions on DebtTransactions.comp_id = DebtMaster.comp_id and
DebtTransactions.ledger_id = DebtMaster.ledger_id and
DebtTransactions.ref_id = DebtMaster.ref_id
where
DebtMaster.comp_id = 'DEM' and
DebtMaster.ledger_id = 'PL1'
group by
DebtMaster.ref_id,
DebtMaster.name

-- Method 2
select
DebtMaster.ref_id,
DebtMaster.name,
trans_total.total_gross
from
DebtMaster
inner join (select
ref_id,
sum(DebtTransactions.gross_val) as 'total_gross',
sum(DebtTransactions.outs_bal) as 'total_outs'
from
DebtTransactions
where
DebtTransactions.comp_id = 'DEM' and
DebtTransactions.ledger_id = 'PL1'
group by
DebtTransactions.ref_id) as trans_total on trans_total.ref_id = DebtMaster.ref_id
where
DebtMaster.comp_id = 'DEM' and
DebtMaster.ledger_id = 'PL1'"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-23 : 10:41:34

have you created the appropriate indexes on the filtered fields/columns?

--------------------
keeping it simple...

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-05-25 : 20:16:26

They are not even the same query so you can't really compare them. You have added assumptions that the optimiser cannot know to the second (DebtTransactions.comp_id = 'DEM'). If you used the same predicates in both cases I'd wager the optimiser would come up with the same plan.

If it's bugging you, why not check the execution plan?

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-05-26 : 01:23:47

Despite the fact that you have an extra unused SUM() in method 2, then what made you think method 2 should be faster, the lack of need to have DebtMaster.name in the GROUP BY ?

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-26 : 02:06:18

u have to create the appropriate indexes on the filtered columns. That will much faster.

Thanks
KK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources