You need to enable JavaScript to run this app. TSQL动态TOP N行更新的无游标实现与存储过程性能优化
优惠活动
大模型
产品
解决方案
定价
更多

TSQL动态TOP N行更新的无游标实现与存储过程性能优化

存储过程游标逻辑优化方案

原方案性能瓶颈根源

  • 游标逐行遍历配置参数,每处理一条配置就要重复关联日期、时段维度,重复扫描出行主表匹配过滤条件,表扫描开销被N倍放大
  • 单批次更新的命中行数少,事务日志逐批次累积写入,IO开销居高不下
  • 原更新语句中UPDATE TOP未指定排序规则,TOP取值完全随机,更新结果不可控

无游标实现核心逻辑

完全对齐原游标处理规则,用窗口函数替代循环逻辑,实现单次批量更新:

  • 提前一次性扫描所有待更新的出行记录,关联维度完成字段映射,避免重复扫描
  • 对每个「日期类型+时段+目的地」分组下的待更新出行记录,按约定排序规则(原注释提到的ContractID升序)生成组内连续行号
  • 对配置表的同分组记录,按原游标排序规则(Deploy降序、出发站台升序)计算每个配置对应的连续行号区间
  • 匹配行号落在对应区间的出行记录,一次性完成字段更新,完全替代逐行游标循环

重构后的存储过程实现

ALTER PROCEDURE [dbo].[SP_Travels_History_FromMissing]
AS
BEGIN
    SET NOCOUNT ON;

    -- 配置临时表刷新:去掉无意义的插入排序逻辑
    TRUNCATE TABLE  [TCK_TMP_Fact_ALL_Travels_History_FromMissing]
    INSERT INTO [TCK_TMP_Fact_ALL_Travels_History_FromMissing]  
    SELECT  
        TRV.Day_Type_Code,
        TRV.PartOfDay_Code,
        HIS.Station_From,
        TRV.DestinationStation,
        CAST(CEILING((TRV.TotalTravels * HIS.Percentage) / 100.0) AS INT) Deploy
    FROM    
    (
        SELECT  
            DAT.Day_Type_Code,
            POD.PartOfDay_Code,
            DestinationStation,
            COUNT(*) TotalTravels
        FROM [Tickets].[dbo].[TCK_STG_Fact_Contract_Magnetic_Travels1] TRV
        INNER JOIN [Tickets].[dbo].[TCK_DWH_Dim_Date] DAT 
            ON CAST(TRV.[ExitDate] AS DATE) = CAST(DAT.Date AS DATE)
        INNER JOIN [Tickets].[dbo].[TCK_DWH_Dim_Manual_PartOfDay] POD 
            ON DATEPART(HOUR,TRV.[ExitDate]) = POD.PartOfDay_Hour
        WHERE [EventID_Entrance] = -1
        GROUP BY DAT.Day_Type_Code, POD.PartOfDay_Code, DestinationStation
    ) TRV
    INNER JOIN [dbo].[TCK_DWH_Fact_ALL_Travels_History_FromMissing] HIS 
        ON HIS.Day_Type_Code = TRV.Day_Type_Code
        AND HIS.PartOfDay_Code = TRV.PartOfDay_Code
        AND HIS.Station_To = TRV.DestinationStation
    WHERE HIS.Total_Travels IS NOT NULL
        AND CAST(CEILING((TRV.TotalTravels * HIS.Percentage) / 100.0) AS INT) > 0;

    -- 批量更新逻辑:完全替代原游标循环
    WITH 
    -- 待更新出行记录打行号
    TravelRank AS (
        SELECT 
            TRV.SourceStationID,
            TRV.MissingStationSourceCode,
            DAT.Day_Type_Code,
            POD.PartOfDay_Code,
            TRV.DestinationStation,
            ROW_NUMBER() OVER (
                PARTITION BY DAT.Day_Type_Code, POD.PartOfDay_Code, TRV.DestinationStation 
                ORDER BY TRV.ContractID
            ) AS RowSeq
        FROM [Tickets].[dbo].TCK_STG_Fact_Contract_Magnetic_Travels1 TRV
        INNER JOIN [Tickets].[dbo].[TCK_DWH_Dim_Date] DAT 
            ON CAST(TRV.[ExitDate] AS DATE) = CAST(DAT.Date AS DATE)
        INNER JOIN [Tickets].[dbo].[TCK_DWH_Dim_Manual_PartOfDay] POD 
            ON DATEPART(HOUR,TRV.[ExitDate]) = POD.PartOfDay_Hour
        WHERE TRV.[EventID_Entrance] = -1
            AND TRV.SourceStationID IS NULL
    ),
    -- 配置表计算对应行号区间,对齐原游标顺序分配规则
    CfgRange AS (
        SELECT 
            Day_Type_Code,
            PartOfDay_Code,
            DestinationStation,
            Station_From,
            SUM(Deploy) OVER (
                PARTITION BY Day_Type_Code, PartOfDay_Code, DestinationStation
                ORDER BY Deploy DESC, Station_From
                ROWS UNBOUNDED PRECEDING
            ) - Deploy + 1 AS RowStart,
            SUM(Deploy) OVER (
                PARTITION BY Day_Type_Code, PartOfDay_Code, DestinationStation
                ORDER BY Deploy DESC, Station_From
                ROWS UNBOUNDED PRECEDING
            ) AS RowEnd
        FROM [TCK_TMP_Fact_ALL_Travels_History_FromMissing]
    )
    UPDATE tr
    SET 
        tr.SourceStationID = cfg.Station_From,
        tr.MissingStationSourceCode = 888
    FROM TravelRank tr
    INNER JOIN CfgRange cfg
        ON tr.Day_Type_Code = cfg.Day_Type_Code
        AND tr.PartOfDay_Code = cfg.PartOfDay_Code
        AND tr.DestinationStation = cfg.DestinationStation
        AND tr.RowSeq BETWEEN cfg.RowStart AND cfg.RowEnd;
END

配套效率优化项

  • 索引优化:针对出行主表TCK_STG_Fact_Contract_Magnetic_Travels1建立覆盖索引,索引键包含EventID_Entrance、SourceStationID、ExitDate、DestinationStation,索引包含字段ContractID,消除更新过程中的回表扫描开销
  • 类型修正:计算Deploy值时将除法分母改为100.0,避免整数除法导致的精度损失和隐式类型转换开销
  • 关联逻辑裁剪:原统计和更新逻辑中的LEFT OUTER JOIN维度表改为INNER JOIN,过滤掉无有效维度映射的无效记录,减少无效数据处理量
  • 冗余排序移除:去掉临时表插入时的无意义ORDER BY子句,减少临时表的排序计算开销

内容的提问来源于stack exchange,提问作者Gines

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.14 16:24:03