避免WHILE循环和游标以获得更好的性能？

Question

我想知道是否有人能帮助简化这个程序-并提高性能.？

我们有赠款的数据。“捐款者”给“接受者”提供资金，我们希望在三个时期向每个捐赠者显示前15名受赠者:当前-20年、现在-10和CurrentYear。我们发布了一份年度报告，并显示了每个捐助者在世界和GeoZone总额中所占的百分比。

我“继承”了这段代码，它是由我的一位前任编写的。在我们切换到使用视图之前，执行时间大约是15-30分钟。目前，这只在下运行四个小时(计划作为服务器代理作业)！管理层不高兴。由于各种原因，该视图必须继续使用，目前有将近90万行数据是从1950年代开始的。我们目前运行这份报告的30个(大型)捐助者和更多的人每年增加。

为了帮助提高性能，我考虑过使用CTE或/使用SUM() OVER(Partition .)或者把它们结合起来，但我不知道该怎么做。

有人能给我指明正确的方向吗？

这是一个过程：

• 创建一个表(变量)以保存当前捐助方的前15位收件人
• 创建一个表(变量)来保存捐助方列表
• 按照捐助者在报告中出现的顺序，与捐助者一起填充捐助者表
• 循环通过施主表，并为每个施主：
  • 将此捐献者的捐助者ID放入临时表中
  • 循环3次(对于当前年份-20，当前年份-10，CurrentYear)
  • 计算18个区域/区域中每个区域的份额总额
  • 打印报表中每个部分的值。

​

• 获取下一个捐献者身份

正如您可以从上面看到的，计算是运行54次(18x3)为每个捐赠者！

以下是代码(简化)：

-- @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

非常感谢您能提供的任何帮助。

Answer 1

必须避免游标。你可以用‘时间’代替光标。但是，考虑到查询的复杂性，此时要保持游标。

要以其他方式提高性能，请检查以下查询的记录数：

1. 从RecipientCode dbo.RecipientGroup GroupID=160中选择GroupID=160
2. 从RecipientCode dbo.GeoZones GeoZoneID=100中选择GeoZoneID=100
3. 从RecipientID dbo.RecipientGroup RegionID=1中选择RegionID=1

我建议为上述查询创建3个临时表，并在游标内使用。

希望这能有所帮助！