Better way to do SUM

[aajay]

I’m new bee
I wrote store proc following

select sum(a.Amount) as amount1
,sum(b.Amount)as amount2
,'Agencies' as CategoryName
from DMAS_INFO di
left outer join
(select sum(Amount) as Amount
,ADISID -—current year
from vwGiving_Gift_Journal_Rpt
where TransactionType=1
and month(DateProcessed) = 4
and year(DateProcessed) = 2007
group by ADISID ) a
on a.ADISID=di.ADISID
left outer join
(select sum(Amount)as Amount
,ADISID -- year to date
from vwGiving_Gift_Journal_Rpt
where TransactionType=1
and FiscalYear = 2007
and DateProcessed < '4/26/2007'
group by ADISID)b
on b.ADISID=di.ADISID
where di.CATEGORY = 'L'


It runs, but verty slow, Plus I have to do 6 more union to get different conditions join for every di.CATEGORY e.g ‘M’,’N’,’Y’,’Z’

Would any one look at and let me know what would be the best way to do this store proc specially when I'm doing sum(a.Amount)

thanks

 

[AlexCuse]

If you group by di.CATEGORY, you will get sums for each category. Why do you need a union at all?

I think maybe you should blow up your proc, and just post sample data and desired results. I am not really following your query, but am 99% sure there must be a better way.

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

You have 2 derived table here. I would suggest that you run each one seperately to see where your performance issues are.

Most troubling is this one...

select sum(Amount) as Amount,
       ADISID -—current year
from   vwGiving_Gift_Journal_Rpt 
where  TransactionType=1 
       and month(DateProcessed) = 4 
       and year(DateProcessed) = 2007                      
group by  ADISID

Try changing it to...

select sum(Amount) as Amount,
       ADISID -—current year
from   vwGiving_Gift_Journal_Rpt 
where  TransactionType=1 
       [!]and DateProcessed >= '20070401'
       and DateProcessed < '20070501'[/!]
group by  ADISID

It should return the same data, but will allow you to use indexes (if they exist).



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[aajay]

Thanks you guys
gmmastros your way is more slower that I already have
AlexCuse
I have to get sum of two value
one for current date sum(a.Amount) as amount1
and one for year to date sum(b.Amount)as amount2
for that I had to come up for left outer join of same set of table with different date range
and I have to do it for multiple categories and there are different criteria to sum other catergories as well
here is complete store proc
Please take a look
again I appreciate your time and effort to look at my problem
I'm also uning udf dbo.GetWUFiscalYear(@rDate) which gives me current fiscal year
for e.g if I pass 5/1/2007 it will give me 2007

here is store proc

ALTER PROCEDURE [dbo].[rpt_360]
@rDate datetime
as
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

select sum(a.Amount) as amount1, sum(b.Amount)as amount2, 'Agencies' as CatName
from DMAS_INFO di
left outer join (select sum(Amount) as Amount,ADISID-- current Month
from vwGiving_Gift_Journal_Rpt
where TransactionType=1
and month(DateProcessed) = 4 and year(DateProcessed) = dbo.GetWUFiscalYear(@rDate)
group by ADISID ) a
on a.ADISID=di.ADISID
left outer join (select sum(Amount)as Amount,ADISID -- ytd
from vwGiving_Gift_Journal_Rpt
where TransactionType=1 and FiscalYear = dbo.GetWUFiscalYear(@rDate)
and DateProcessed < '4/26/2007' group by ADISID)b
on b.ADISID=di.ADISID
where di.CATEGORY = 'L'
Union -- Alumni

select sum(a.Amount) as amount1, sum(b.Amount)as amount2, 'Alumni' as CatName
from DMAS_INFO di
left outer join DMAS_CLASSIFICATION dc
on di.ADISID=dc.ADISID
LEFT OUTER JOIN Education e
ON di.ADISID=e.ADISID
left outer join (select sum(Amount) as Amount,ADISID-- current
from vwGiving_Gift_Journal_Rpt
where TransactionType=1
and month(DateProcessed) = 4 and year(DateProcessed) = dbo.GetWUFiscalYear(@rDate)
group by ADISID ) a
on a.ADISID=di.ADISID
left outer join (select sum(Amount)as Amount,ADISID -- ytd
from vwGiving_Gift_Journal_Rpt
where TransactionType=1 and FiscalYear = dbo.GetWUFiscalYear(@rDate)
and DateProcessed < '4/26/2007' group by ADISID)b
on b.ADISID=di.ADISID
where di.CATEGORY in ( 'A','B','D','E','F') or dc.Code='D'
and year(e.DegreeDate) < dbo.GetWUFiscalYear(@rDate)
/*






Union -- Alumni Bequest
select sum(a.Amount) as amount1, sum(b.Amount)as amount2, 'Alumni Bequest' as CatName -- current
from dbo.DMAS_INFO di
left outer join DMAS_CLASSIFICATION dc
on dc.ADISID=dc.ADISID
left outer join (select sum(Amount)as Amount,ADISID
from vwGiving_Gift_Journal_NoAdjust_Pmts_Bequest
where TransactionType=1
and month(DateProcessed) = 4 and year(DateProcessed) = dbo.GetWUFiscalYear(@rDate)
and GiftType = '10' group by ADISID) a
on a.ADISID=di.ADISID
left outer join (select sum(Amount)as Amount,ADISID
from vwGiving_Gift_Journal_NoAdjust_Pmts_Bequest
where TransactionType=1
and month(DateProcessed) = 4 and year(DateProcessed) = dbo.GetWUFiscalYear(@rDate)
and GiftType = '10' group by ADISID)b
on b.ADISID=di.ADISID
where di.CATEGORY in ( 'A','B','D','E','F') or dc.Code='D'

*/
Union --Alumni Honorary
select sum(a.Amount) as amount1, sum(b.Amount)as amount2, 'Alumni Honorary' as CatName
from DMAS_INFO di
left outer join (select sum(Amount) as Amount,ADISID-- current
from vwGiving_Gift_Journal_Rpt
where TransactionType=1
and month(DateProcessed) = 4 and year(DateProcessed) = dbo.GetWUFiscalYear(@rDate)
group by ADISID ) a
on a.ADISID=di.ADISID
left outer join (select sum(Amount)as Amount,ADISID -- ytd
from vwGiving_Gift_Journal_Rpt
where TransactionType=1 and FiscalYear = dbo.GetWUFiscalYear(@rDate)
and DateProcessed < '4/26/2007' group by ADISID)b
on b.ADISID=di.ADISID
where di.CATEGORY = 'C'
Union --Corporations
select sum(a.Amount) as amount1, sum(b.Amount)as amount2, 'Corporations' as CatName
from DMAS_INFO di
left outer join (select sum(Amount) as Amount,ADISID-- current
from vwGiving_Gift_Journal_Rpt
where TransactionType=1
and month(DateProcessed) = 4 and year(DateProcessed) = dbo.GetWUFiscalYear(@rDate)
group by ADISID ) a
on a.ADISID=di.ADISID
left outer join (select sum(Amount)as Amount,ADISID -- ytd
from vwGiving_Gift_Journal_Rpt
where TransactionType=1 and FiscalYear = dbo.GetWUFiscalYear(@rDate)
and DateProcessed < '4/26/2007' group by ADISID)b
on b.ADISID=di.ADISID
where di.CATEGORY = 'K'
Union --Trust and Foundations

select sum(a.Amount) as amount1, sum(b.Amount)as amount2, 'Trust and Foundations' as CatName
from DMAS_INFO di
left outer join DMAS_CLASSIFICATION dc
on di.ADISID=dc.ADISID
left outer join (select sum(Amount) as Amount,ADISID-- current
from vwGiving_Gift_Journal_Rpt
where TransactionType=1
and month(DateProcessed) = 4 and year(DateProcessed) = dbo.GetWUFiscalYear(@rDate)
group by ADISID ) a
on a.ADISID=di.ADISID
left outer join (select sum(Amount)as Amount,ADISID -- ytd
from vwGiving_Gift_Journal_Rpt
where TransactionType=1 and FiscalYear = dbo.GetWUFiscalYear(@rDate)
and DateProcessed < '4/26/2007' group by ADISID)b
on b.ADISID=di.ADISID
where dc.Code='F'
Union --Groups
select sum(a.Amount) as amount1, sum(b.Amount)as amount2, 'Groups' as CatName
from DMAS_INFO di
left outer join (select sum(Amount) as Amount,ADISID-- current
from vwGiving_Gift_Journal_Rpt
where TransactionType=1
and month(DateProcessed) = 4 and year(DateProcessed) = dbo.GetWUFiscalYear(@rDate)
group by ADISID ) a
on a.ADISID=di.ADISID
left outer join (select sum(Amount)as Amount,ADISID -- ytd
from vwGiving_Gift_Journal_Rpt
where TransactionType=1 and FiscalYear = dbo.GetWUFiscalYear(@rDate)
and DateProcessed < '4/26/2007' group by ADISID)b
on b.ADISID=di.ADISID
where di.CATEGORY = 'M'

Union -- Individual

select sum(a.Amount) as amount1, sum(b.Amount)as amount2, 'Individuals' as CatName
from DMAS_INFO di
left outer join DMAS_CLASSIFICATION dc
on di.ADISID=dc.ADISID
left outer join (select sum(Amount) as Amount,ADISID-- current
from vwGiving_Gift_Journal_Rpt
where TransactionType=1
and month(DateProcessed) = 4 and year(DateProcessed) = dbo.GetWUFiscalYear(@rDate)
group by ADISID ) a
on a.ADISID=di.ADISID
left outer join (select sum(Amount)as Amount,ADISID -- ytd
from vwGiving_Gift_Journal_Rpt
where TransactionType=1 and FiscalYear = dbo.GetWUFiscalYear(@rDate)
and DateProcessed < '4/26/2007' group by ADISID)b
on b.ADISID=di.ADISID
where di.CATEGORY = 'H' or dc.Code='E'


/*
Union -- Individual Bequest
select sum(a.Amount) as amount1, sum(b.Amount)as amount2, 'Individuals Bequest' as CatName -- current
from dbo.DMAS_INFO di
left outer join DMAS_CLASSIFICATION dc
on dc.ADISID=dc.ADISID
left outer join (select sum(Amount)as Amount,ADISID
from vwGiving_Gift_Journal_NoAdjust_Pmts_Bequest
where TransactionType=1
and month(DateProcessed) = 4 and year(DateProcessed) = dbo.GetWUFiscalYear(@rDate)
and GiftType = '10'group by ADISID ) a
on a.ADISID=di.ADISID
left outer join (select sum(Amount)as Amount,ADISID
from vwGiving_Gift_Journal_NoAdjust_Pmts_Bequest
where TransactionType=1
and month(DateProcessed) = 4 and year(DateProcessed) = dbo.GetWUFiscalYear(@rDate)
and GiftType = '10' group by ADISID)b
on b.ADISID=di.ADISID
where di.CATEGORY ='H' or dc.Code='E'

*/



Union -- Parents
select sum(a.Amount) as amount1, sum(b.Amount)as amount2, 'Parents' as CatName
from DMAS_INFO di
left outer join DMAS_CLASSIFICATION dc
on di.ADISID=dc.ADISID
LEFT OUTER JOIN Education e
ON di.ADISID=e.ADISID
left outer join (select sum(Amount) as Amount,ADISID-- current
from vwGiving_Gift_Journal_Rpt
where TransactionType=1
and month(DateProcessed) = 4 and year(DateProcessed) = dbo.GetWUFiscalYear(@rDate)
or ADISID in (2289954,2289962,6599222,2485974) group by ADISID ) a
on a.ADISID=di.ADISID
left outer join (select sum(Amount)as Amount,ADISID -- ytd
from vwGiving_Gift_Journal_Rpt
where TransactionType=1 and FiscalYear = dbo.GetWUFiscalYear(@rDate)
and DateProcessed < '4/26/2007'
or ADISID in (2289954,2289962,6599222,2485974) group by ADISID)b
on b.ADISID=di.ADISID
where di.CATEGORY ='H' and dc.Code in ('02','03','04','19')





end

 

[AlexCuse]

Because you are using different where clauses in each bit of the union, it seems a union query is probably the best bet. What I would do is make the changes George suggested, then load your query into a query analyzer window. Pressing ctrl-i will give you the 'index tuning wizard' which will suggest indexes you can add to your tables involved to increase performance.

Just beware that indexes will speed up selects, but slow down updates and inserts (because indexes need to be rebuilt). If this is a 'popular' query, it may be worth it though.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom