I'm wondering if someone can help simplify this procedure - and improve performance...!?
We have data on grants. 'Donors' give funds to 'Recipients' and we want to show the top 15 recipients for each donor over 3 periods: CurrentYear-20, CurrentYear-10 and CurrentYear. We publish an annual report and show percentage shares of World and GeoZone totals for each donor.
I have "inherited" this code which was written by one of my predecessors. Until we switched to using a view, execution time was around 15-30 mins. Currently, this runs in just under FOUR hours (scheduled as a Server Agent job)! Management are not happy. For various reasons, the view must continue to be used and currently has just under 900,000 rows with data from the 1950s onwards. We current run this report for 30 (large) donors and more are added each year.
To help improve performance, I have thought about using a CTE or/using SUM() OVER(Partition BY...) or combination of these, but I'm not sure how to go about it.
Could someone point me in the right direction?
Here is the process:
- create a table (variable) to hold the top 15 recipients for the current donor
- create a table (variable) to hold the list of donors
- populate the donor table with the donors in the order they appear in the report
- loop thru the donor table and for each donor:
- put the donor ID for this donor into a temp table
- loop 3 times (for CurrentYear-20, CurrentYear-10, CurrentYear)
- calculate the share totals for each of 18 regions/zones
- print the values for each section in the report
- get the next donor ID
As you may see from the above, the calculations are run 54 times (18x3) for each donor!
Here is the code (simplified):
-- @LatestYear is passed as a parameter, hardcoded here for simplicity
DECLARE @LatestYear SMALLINT ,
@CurrentYear SMALLINT ,
@DonorID SMALLINT ,
@totalWorld NUMERIC(10, 2) ,
@LoopCounter TINYINT ,
@DonorName VARCHAR(100)
SELECT @latestyear = 2012
-- create a table to hold list of top 15 recipients for each donor and their 'share' of ODA.
DECLARE @Top15 TABLE
(
Country VARCHAR(100) ,
Percentage REAL
)
-- create a table to hold list of donors, ordered as they need to appear in the report.
DECLARE @PageOrder TABLE
(
DonorID SMALLINT ,
DonorName VARCHAR(100) ,
SortOrder SMALLINT IDENTITY(1, 1)
)
-- create a table to store the "focus" donor.
DECLARE @CurrentDonor TABLE ( DonorID SMALLINT )
INSERT INTO @PageOrder
SELECT DonorID ,
DonorName
FROM dbo.LookupDonor
ORDER BY DonorName;
-- cursor to loop through the donors in SortOrder
DECLARE DonorCursor CURSOR
FOR
SELECT DonorID ,
DonorName
FROM @PageOrder
ORDER BY DonorName;
OPEN DonorCursor
FETCH NEXT FROM DonorCursor INTO @DonorID, @DonorName
WHILE @@fetch_status = 0
BEGIN
INSERT INTO pubOutput
( XMLText )
SELECT @DonorName;
-- Populate the DonorID table
INSERT INTO @CurrentDonor
VALUES ( @DonorID )
/* The following loop is invoked 3 times. The first time through, the year will be 20 years before the latest year,
the second time through, 10 years before. The last time through the year will be the latest year.
*/
SET @LoopCounter = 1
WHILE @LoopCounter <= 3
BEGIN
SELECT @CurrentYear = CASE @LoopCounter
WHEN 1 THEN @LatestYear - 20
WHEN 2 THEN @LatestYear - 10
ELSE @LatestYear
END
-- calculate the world total for the current years (year,year-1) for all recipients
SELECT @totalWorld = SUM(Amount)
FROM dbo.vData2 d
INNER JOIN ( SELECT RecipientID
FROM dbo.RecipientGroup
WHERE GroupID = 160
) c ON d.RecipientID = c.RecipientID
INNER JOIN @CurrentDonor z ON d.DonorID = z.DonorID
WHERE d.year IN ( @CurrentYear - 1, @CurrentYear )
-- calculate the GeoZones total for the current years (year,year-1)
SELECT @totalGeoZones = SUM(Amount)
FROM dbo.vDac2a d
INNER JOIN ( SELECT RecipientID
FROM dbo.GeoZones
WHERE GeoZoneID = 100
) x ON d.RecipientID = x.RecipientID
INNER JOIN @CurrentDonor z ON d.DonorCode = z.DonorCode
WHERE d.year IN ( @CurrentYear - 1, @CurrentYear )
-- Find the top 15 recipients for the current donor
INSERT INTO @Top15
SELECT TOP 15
r.RecipientName ,
( ISNULL(SUM(Amount), 0) / @totalWorld ) * 100
FROM dbo.vData2 d
INNER JOIN dbo.LookupRecipient r ON r.RecipientID = d.RecipientID
INNER JOIN @CurrentDonor z ON d.DonorID = z.DonorID
WHERE d.year IN ( @CurrentYear - 1, @CurrentYear )
GROUP BY r.RecipientName
ORDER BY 2 DESC
-- Print the top 15 recipients and total
INSERT INTO pubOutput
(
XMLText
)
SELECT country + @Separator + CAST(percentage AS VARCHAR)
FROM @Top15
ORDER BY percentage DESC
INSERT INTO pubOutput
(
XMLText
)
SELECT @Heading1 + @Separator + CAST(SUM(Percentage) AS VARCHAR)
FROM @Top15
-- Breakdown by Regionas
-- Region1
IF @totalWorld IS NOT NULL
INSERT INTO pubOutput
(
XMLText
)
SELECT 'Region1' + @Separator
+ CAST(( ISNULL(SUM(Amount), 0) / @totalWorld ) * 100 AS VARCHAR)
FROM dbo.vData2 d
INNER JOIN ( SELECT RecipientID
FROM dbo.RecipientGroup
WHERE RegionID = 1
) c ON d.RecipientID = c.RecipientID
INNER JOIN @CurrentDonor z ON d.DonorID = z.DonorID
WHERE d.year IN ( @CurrentYear - 1, @CurrentYear )
ELSE -- force output of sub-total heading
INSERT INTO pubOutput
(
XMLText
)
SELECT @Heading2 + @Separator + '--'
-- Region2-8
/* similar syntax as Region1 above, for all Regions 2-8 */
-- Total Regions
INSERT INTO pubOutput
(
XMLText
)
SELECT @Heading2 + @Separator + CAST(@totalWorld AS VARCHAR)
-- Breakdown by GeoZones 1-7
-- GeoZone1
INSERT INTO pubOutput
(
XMLText
)
SELECT 'GeoZone1' + @Separator
+ CAST(( ISNULL(SUM(Amount), 0) / @totalGeoZones ) * 100 AS VARCHAR)
FROM dbo.vDac2a d
INNER JOIN ( SELECT RecipientID
FROM dbo.GeoZones
WHERE GeoZoneID = 1
) m ON d.RecipientID = m.RecipientID
INNER JOIN @CurrentDonor z ON d.DonorCode = z.DonorCode
WHERE d.year IN ( @CurrentYear - 1, @CurrentYear )
-- GeoZones2-8
/* similar syntax as GeoZone1 above for GeoZones 2-7 */
-- Total GeoZones - currently hard-coded as 100, due to minor rounding errors
INSERT INTO pubOutput
(
XMLText
)
SELECT @Heading3 + @Separator + '100'
SET @LoopCounter = @LoopCounter + 1
END -- year loop
-- Get the next donor from the cursor
FETCH NEXT FROM DonorCursor
INTO @DonorID, @DonorName
END
-- donorcursor
-- Cleanup
CLOSE DonorCursor
DEALLOCATE DonorCursor
Many thanks in advance for any help you may be able to provide.
