SQL Server更新4000万行性能优化：逐行填充Session ID过慢如何解决

核心瓶颈说明 #

你现有代码的性能问题来自逐行处理（RBAR）模式：4200万次独立的行查询+更新操作会产生极高的IO开销、事务日志开销和锁等待，完全没有利用SQL Server集合运算的优势。另外你现有代码存在明显逻辑冗余：else分支下的两个判断分支最终都会给sessionid赋值相同的@sval，那堆条件判断完全没有生效，属于无效代码。

优化前提 #

先做以下准备工作降低更新开销：

• 确保number列是表的聚集索引/主键，所有按number的定位和范围查询都能走索引，这是优化的基础
• 临时将数据库恢复模式调整为大容量日志模式，更新完成后改回原模式，大幅减少事务日志生成量
• 若sessionid列上存在非聚集索引，先删除索引，更新完成后再重建，避免更新过程中额外的索引维护开销

最优实现方案（集合运算一次性计算，性能提升上千倍） #

如果你原有逻辑的Session ID断点仅为vltid <> previous_vltid 或 previous_vltid 为空，可直接用窗口函数一次性计算所有行的Session ID，再执行更新：

WITH session_calc AS (
    SELECT
        number,
        sessionid,
        -- 按行号顺序累加断点标记，直接生成连续的SessionID
        SUM(CASE WHEN vltid <> previous_vltid OR previous_vltid IS NULL THEN 1 ELSE 0 END)
            OVER(ORDER BY number ROWS UNBOUNDED PRECEDING) AS new_sessionid
    FROM dbo.stage3
)
UPDATE session_calc SET sessionid = new_sessionid;

如果你原本写的那堆条件是要作为额外的Session ID断点（你原代码写错了没有生效），直接把条件加到CASE判断中即可：

WITH session_calc AS (
    SELECT
        number,
        sessionid,
        SUM(CASE
            WHEN vltid <> previous_vltid OR previous_vltid IS NULL THEN 1
            WHEN recordtype = 4 AND seconds > 30 AND previous_action IN (1,5) THEN 1
            WHEN ISNULL(playercashableamount,0) < 1 AND seconds < 30 AND next_action IN (1,4) THEN 1
            WHEN (recordtype =5 AND seconds <30 AND next_action=4) OR next_action=4 THEN 1
            ELSE 0 END
        ) OVER(ORDER BY number ROWS UNBOUNDED PRECEDING) AS new_sessionid
    FROM dbo.stage3
)
UPDATE session_calc SET sessionid = new_sessionid;

大容量数据分批更新方案（避免大事务锁表/日志爆盘） #

如果担心单次更新4000万行产生过大事务，可按批次更新，每次处理10万行，仅需循环400余次即可完成：

DECLARE @batchSize INT = 100000,
        @currentMax INT = 0,
        @totalMax INT = (SELECT MAX(number) FROM dbo.stage3);

WHILE @currentMax < @totalMax
BEGIN
    ;WITH session_calc AS (
        SELECT
            number,
            sessionid,
            SUM(CASE WHEN vltid <> previous_vltid OR previous_vltid IS NULL THEN 1 ELSE 0 END)
                OVER(ORDER BY number ROWS UNBOUNDED PRECEDING) AS new_sessionid
        FROM dbo.stage3
        WHERE number > @currentMax AND number <= @currentMax + @batchSize
    )
    UPDATE session_calc SET sessionid = new_sessionid;

    SET @currentMax += @batchSize;
    -- 可选：每批次执行完打印进度
    PRINT '已处理到行号：' + CAST(@currentMax AS VARCHAR(20));
END

性能对比 #

原逐行代码预计执行时间为小时级甚至天级，优化后的方案在普通服务器上预计10~30分钟即可完成全量更新。

内容的提问来源于stack exchange，提问作者Satya Gurucharan