Query execution performance tuning needed

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)

Query execution performance tuning needed

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-01-05 : 06:35:16

I have following query.Please suggest any imporovement for performance

select d.person_id,d.slip_no,
sum(dps.amount) amount,
(select sum(amount) from data_payment_slip where org_id=4 and person_id=d.person_id) as payable
from data_payment d
left join data_payment_slip dps on dps.person_id=d.person_id
where d.org_id=4
group by d.person_id,d.slip_no

Kamran Shahid
Sr. Software Engineer
Assurety Consulting inc.
(MCSD.Net,MCPD.net)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-05 : 07:15:13

[code]SELECT d.Person_ID,
d.Slip_No,
ISNULL(dps.Amount, 0.0)) AS Amount,
ISNULL(dps.Payable, 0.0) AS Payable
FROM (
SELECT Person_ID,
Slip_No
FROM Data_Payment
WHERE Org_ID = 4
GROUP BY Person_ID,
Slip_No
) AS d
LEFT JOIN (
SELECT Person_ID,
SUM(Amount) AS Amount,
SUM(CASE WHEN Org_ID = 4 THEN Amount ELSE 0.0 END) AS Payable
FROM Data_Payment_Slip
GROUP BY Person_ID
) AS dps ON dps.Person_ID = d.Person_ID
ORDER BY d.Person_ID,
d.Slip_No[/code]

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

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-01-05 : 07:57:58

Thanks PESO

Kamran Shahid
Sr. Software Engineer
Assurety Consulting inc.
(MCSD.Net,MCPD.net)

balaganapathy.n
Starting Member

18 Posts

Posted - 2010-01-05 : 08:42:44

This article may help you to get what went wrong in your query.

http://www.sqlservercentral.com/articles/T-SQL/61539/

balaganapathy n.

Anything you can imagine is real.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-05 : 10:50:07

quote:
Originally posted by kamii47

Thanks PESO

You're welcome.
What performance gain did you get?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-05 : 10:51:40

quote:
Originally posted by balaganapathy.n

This article may help you to get what went wrong in your query.

http://www.sqlservercentral.com/articles/T-SQL/61539/

I fail to see the relevance.
There is no triangular join in this case.

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

balaganapathy.n
Starting Member

18 Posts

Posted - 2010-01-06 : 07:42:15

quote:
Originally posted by Peso

quote:
Originally posted by balaganapathy.n

This article may help you to get what went wrong in your query.

http://www.sqlservercentral.com/articles/T-SQL/61539/
I fail to see the relevance.
There is no triangular join in this case.

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

Oh, sorry, mistakenly posted, while typing something else.

balaganapathy n.

Anything you can imagine is real.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources