小伙伴恋上游标，你能怎么办

有一段时间没对Top SQL提出优化建议了，周五开发小伙伴说："有一个存储过程执行了两个多小时还没完成，我只是把原来具体语句修改为动态拼接语句而已，之前十分钟就好了！"
纳尼？！改过语句！我什么都不知情！
存储过程业务不算复杂，统计各游戏、各代理商前一天的推广用户量、房卡购买金额、房卡消耗量、活跃用户量、新增活跃用户量
其中代理商存在层级关系(1级、2级)，1级的数据=1级代理本身+其下面的2级代理数据。由于不同的游戏记录在不同的表格，开发小伙伴将各游戏对应的数据表、统计字段保存到一张配置表

/******** 简化代码、库名|表名已做替换 ********/
DECLARE loop_cursor CURSOR FOR
SELECT aui.KindID,aui.KindName,aui.AgentUserID,aui.AgentAccounts,aui.AgentNickName,aui.AgentLevel,aui.HighAccounts,aui.Personal,ais.UserID,ais.NickName
FROM LnkServer.DBname.dbo.AgentUserInfo aui WITH(NOLOCK)
LEFT JOIN LnkServer.DBname.dbo.AccountsInfoSimple ais WITH(NOLOCK)
on aui.HighAccounts = ais.accounts
ORDER BY aui.OperateDate DESC
OPEN loop_cursor
FETCH NEXT FROM loop_cursor INTO @KindID,@KindName,@AgentUserID,@AgentAccounts,@AgentNickName,@AgentLevel,@HighAccounts,@Personal,@HighAgentUserID,@HighAgentNickName
WHILE @@FETCH_STATUS = 0
BEGIN
    declare ...

    SELECT @GroupStatRecord=ISNULL(GroupStatRecord,'SparrowGroupStatRecord')
    ,@CardConsumedField=ISNULL(CardConsumedField,'CostRoomCards') FROM DBname.dbo.SparrowConfigForAll WITH(NOLOCK)
    WHERE KindID=@KindID
        
    --推广用户量
    --房卡购买金额
    
    --房卡消耗量
    DECLARE @sqlVoucherConsumeTotal NVARCHAR(MAX);
    set @sqlVoucherConsumeTotal='select @VoucherConsumeTotal = isnull(sum('+@CardConsumedField+'),0)
    from ' +@GroupStatRecord+' with(nolock)
    where StartDate between @StartTime and @EndTime   And KindID=@KindID AND UserID in (
    SELECT DISTINCT ais.UserID
    FROM LnkServer.DBname.dbo.AccountsInfoSimple ais WITH(NOLOCK)
    INNER JOIN LnkServer.DBname.dbo.AgentBindUser T WITH(NOLOCK) ON ais.UserID = T.UserID
    WHERE (T.CreateDate <= @EndTime) AND
    T.KindID = @KindID
    AND T.AgentAccounts = @AgentAccounts
    )' 
        
    DECLARE @pargamVoucherConsumeTotal NVARCHAR(1000)
    SET @pargamVoucherConsumeTotal = '@VoucherConsumeTotal DECIMAL(18,0) out,@StartTime DATETIME,@EndTime DATETIME,@KindID INT,@AgentAccounts VARCHAR(100) '        
                        
    exec sp_executesql @sqlVoucherConsumeTotal,
    @pargamVoucherConsumeTotal,
    @VoucherConsumeTotal=@VoucherConsumeTotal OUTPUT,
    @StartTime=@StartTime,
    @EndTime=@EndTime,
    @KindID=@KindID,
    @AgentAccounts=@AgentAccounts
    
    --单天、累计活跃用户量
    --单天、累计新增活跃用户量

    IF(@AgentLevel=1)
    BEGIN        
        --重复上面的代码，AgentAccounts条件调整
        --累加1级代理下的2级代理
        
        --推广用户量
        --房卡购买金额
        
        --房卡消耗量
        ---------------------------
        DECLARE @sqlVoucherConsumeTotalTemp NVARCHAR(MAX);
        set @sqlVoucherConsumeTotalTemp='select  @VoucherConsumeTotalTemp =isnull(sum('+@CardConsumedField+'),0)
        from '+@GroupStatRecord+' with(nolock)
        where StartDate between @StartTime and @EndTime   And KindID=@KindID AND UserID in (
        SELECT DISTINCT ais.UserID
        FROM LnkServer.DBname.dbo.AccountsInfoSimple ais WITH(NOLOCK)
        INNER JOIN LnkServer.DBname.dbo.AgentBindUser T WITH(NOLOCK) ON ais.UserID = T.UserID
        WHERE (T.CreateDate <= @EndTime) AND
        T.KindID = @KindID
        AND T.AgentAccounts IN
        (select AgentAccounts from LnkServer.DBname.dbo.AgentUserInfo
        where HighAccounts = @AgentAccounts)
        )' 

        DECLARE @pargamVoucherConsumeTotalTemp NVARCHAR(1000)
        SET @pargamVoucherConsumeTotalTemp = '@VoucherConsumeTotalTemp DECIMAL(18,0) out,@StartTime DATETIME,@EndTime DATETIME,@KindID INT,@AgentAccounts VARCHAR(100)'        
                                
        exec sp_executesql @sqlVoucherConsumeTotalTemp,
        @pargamVoucherConsumeTotalTemp,
        @VoucherConsumeTotalTemp=@VoucherConsumeTotalTemp OUTPUT,            
        @StartTime=@StartTime,
        @EndTime=@EndTime,
        @KindID=@KindID,
        @AgentAccounts=@AgentAccounts
        
        --单天、累计活跃用户量
        --单天、累计新增活跃用户量
        
    END    
    SET @VoucherConsumeTotal = @VoucherConsumeTotal + @VoucherConsumeTotalTemp
    
    insert into AgentSpreadDayStatistics...

    FETCH NEXT FROM loop_cursor INTO @KindID,@KindName,@AgentUserID,@AgentAccounts,@AgentNickName,@AgentLevel,@HighAccounts,@Personal,@HighAgentUserID,@HighAgentNickName
END
CLOSE loop_cursor
DEALLOCATE loop_cursor
View Code

存储过程里洋洋洒洒码了600+行，在我看过N遍后，我决定放弃梳理存储过程的含义。既然执行太慢，就从执行计划开始，缓存中的执行计划看得一脸懵逼。退求其次，拎一个代理商执行查看它的执行计划(截图只是部分，下同)


它在@GroupStatRecord表(暂且称为"主表")上使用StartDate列上的索引查找返回前一天的数据，然后再和后面的子查询关联。我们具体来看执行步骤：
1、先返回主表一天的记录，索引查找+RID查找(量大的情况下，高消耗)
2、子查询返回单个代理商推广的用户
3、关联1、2步骤结果
步骤1返回很多其他用户的数据，尤其是统计月初到现在的数据时。主表每天的量仅2-3K(每月10万以内)，子查询中平均每个代理商(2K个代理商)绑定的用户约20个(总绑定用户约4W)。似乎明白了慢的原因在于步骤1中的无效数据。尝试给主表在UserID创建索引并在语句后面添加索引提示，希望执行计划先从子查询返回需要的UserID，再到主表嵌套循环。改过后执行，单天的统计确实快了，从1小时降到7分钟。然而，累计统计(月初到现在)似乎没有那么顺畅，偶尔在某个代理商上卡壳！执行半个多小时，查看只跑了一半的累计数据(之前1个多小时仅跑了107个累计数据，2000个不得要跑20小时？！)。
某些代理商慢，它们有什么不同？它们具体慢在哪项统计步骤上？
从最终的统计结果表发现所有执行慢的代理商都是1级代理商(个人觉得这是游标一行一行处理的唯一好处，可以知道哪条/些记录执行过长)，在对应位置的前后加上print时间，执行一个1级代理商，查看具体哪个步骤慢

执行计划使用了IX_UserID索引，但也是先返回满足主表条件的记录，再和子查询关联查询，这样主表依旧会返回很多无效数据。上图就是房卡消耗量的累计数据，开销大部分用在IX_UserID索引扫描+键查找。如果能指引执行计划先从子查询返回需要的UserID，再到主表查找就完美了。查看了整个执行计划，其他统计都是先利用子查询返回足够少的UserID，然后嵌套循环主表；唯独在累加1级代理商下的2级代理商的房卡消耗数据时顺序反转了。
我们查看"累加1级代理下的2级代理"中房卡消耗语句，拼接表名、字段，in子查询中嵌套in子查询。编写的人可能觉得这样"通俗易懂"，数据库可没有人脑那么理所当然。数据已经表明，它选择了错误的执行计划。
鉴于最小改动的情况下，我提出以下建议：
1、该有的索引还是得创建，根据执行情况添加索引提示
2、一次获取所有代理商绑定/推广的用户信息保存到临时表，避免每项统计都要去关联查询代理商用户
3、使用步骤2的临时表与对应表关联(如果需要继续添加临时表，指引优化器按预想的顺序执行)
4、累加1级代理下的2级代理，原始代码完全是重复计算，直接从统计好的2级代理商累加即可
5、为什么要用游标？set-based，好好的集合不用，非得拆成一条一条的去获取
总结，没事瞎bibi，刀要常磨~

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/Uest/p/6612859.html