Performance issues in group by

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

General SQL Server Forums

New to SQL Server Programming

Performance issues in group by

Author

Topic

gayathri123
Starting Member

3 Posts

Posted - 2008-03-31 : 17:24:47

hi,

Im not very familiar in tuning the performance of a sql.I want to join three tables
Grant Ledger ,Grant fund and payment table.
Grant ledger has grant_id,fund etc.,
Grant fund has grant_id,fund,proj start and end dates
Payment has grant code(FK of grant id),payment type and some other columns..

Can anyone suggest me how to join all these three tables with a group by having all of the above mentioned columns.

Normal join with group by takes extremely long ...like minutes to execute..

Should we group it immediately after we join or can we group them altogether at the end with all the column names in select statement.

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-31 : 18:16:04

quote:
Originally posted by gayathri123

Normal join with group by takes extremely long ...like minutes to execute..

Show us what you tried.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

gayathri123
Starting Member

3 Posts

Posted - 2008-04-01 : 15:58:31

select led.GRANT_ID, led.FUND,fund.PROJ_STRT_DATE,fund.PROJ_END_DATE,

p.PMT_METHOD_TYPE_IND, sum( led.budget)

from grant_ledger led,grant_fund fund,payment_method p

where led.grant_id = fund.grant_id
and led.fund = fund.fund(+)
and payment_type in ('A','B','C')
and led.chart_of_accounts(+) = 'N'

group by led.GRANT_ID, led.FUND,fund.PROJ_STRT_DATE,fund.PROJ_END_DATE,

p.PMT_METHOD_TYPE_IND

gayathri123
Starting Member

3 Posts

Posted - 2008-04-01 : 15:59:23

All these tables have bulk volume of data and execute for minutes...
Suggestions?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-01 : 16:22:35

You've posted an Oracle query on a Microsoft SQL Server site. Although the SQL will be similar, there are differences. All I can offer is to make sure that the following are indexed: led.grant_id, fund.grant_id, led.fund, and fund.fund.

For a better Oracle answer, you should post on an Oracle forum such as the one over at dbforums.com

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources