嘿，你的这个批量更新问题我太熟悉了——直接跑全量UPDATE卡死，用游标又慢得离谱，确实得换个更高效的思路！

首先得说下你当前游标方案的核心问题：虽然你一次性取了50万行到表变量里，但本质还是逐行更新，相当于执行了50万次单条UPDATE语句，每次都要单独处理锁、日志和事务开销，这效率能高才怪。咱们直接改成批量更新整批数据，就能把效率提上去一大截。

方案1：最简批量循环更新（推荐） #

这种方案不需要游标，直接用WHILE循环每次更新指定行数，逻辑简单还高效：

SET NOCOUNT ON; -- 减少不必要的行计数反馈，降低网络开销
DECLARE @BatchSize INT = 10000; -- 可根据服务器性能调整，比如50000
DECLARE @RowsUpdated INT = 1;

WHILE @RowsUpdated > 0
BEGIN
    BEGIN TRANSACTION;

    -- 每次更新@BatchSize条符合条件的行
    UPDATE TOP (@BatchSize) dbo.table
    SET collectedtime = DateUpdated
    WHERE collectedtime IS NULL;

    SET @RowsUpdated = @@ROWCOUNT; -- 获取本次更新的行数

    COMMIT TRANSACTION;
    
    -- 如果服务器同时有其他业务在跑，可加个短暂延迟避免锁表过度
    -- WAITFOR DELAY '00:00:01';
END

为什么这个方案更好？

• 每次更新一批数据，而不是逐行，大幅减少了UPDATE语句的执行次数和事务开销
• 每批更新后立即提交事务，释放锁资源，避免长时间持有大表锁影响其他业务
• 代码简洁，维护成本低

方案2：用临时表批量更新（适合复杂条件） #

如果你的更新逻辑更复杂，或者需要先筛选出要更新的ID再操作，可以用临时表存一批ID，再通过JOIN批量更新：

SET NOCOUNT ON;
DECLARE @BatchSize INT = 10000;
DECLARE @RowsProcessed INT = 1;

WHILE @RowsProcessed > 0
BEGIN
    -- 创建临时表存储一批要更新的ID（加主键提升JOIN效率）
    CREATE TABLE #TempIds (Raw_Id BIGINT PRIMARY KEY);

    -- 取一批符合条件的ID
    INSERT INTO #TempIds (Raw_Id)
    SELECT TOP (@BatchSize) Raw_Id
    FROM dbo.table WITH (NOLOCK) -- 非必要，若允许脏读可加快查询
    WHERE collectedtime IS NULL;

    SET @RowsProcessed = @@ROWCOUNT;

    IF @RowsProcessed > 0
    BEGIN
        BEGIN TRANSACTION;

        -- 通过JOIN批量更新
        UPDATE t
        SET t.collectedtime = t.DateUpdated
        FROM dbo.table t
        INNER JOIN #TempIds temp ON t.Raw_Id = temp.Raw_Id;

        COMMIT TRANSACTION;
    END

    DROP TABLE #TempIds;
    -- WAITFOR DELAY '00:00:01';
END

额外优化建议 #

• 调整批次大小：如果服务器性能足够（CPU、内存充裕，无其他高负载业务），可以把@BatchSize调大到5万甚至10万；如果服务器压力大，就调小到1万以内，平衡更新速度和资源占用。
• 加合适的索引：确保Raw_Id是主键或有非聚集索引；另外可以给collectedtime建一个过滤索引：CREATE NONCLUSTERED INDEX IX_table_collectedtime ON dbo.table(collectedtime) WHERE collectedtime IS NULL;，这样查询要更新的行速度会快很多。
• 关注日志增长：如果数据库是完整恢复模式，批量更新会产生大量日志，建议在更新过程中定期备份日志，避免日志文件撑爆磁盘；如果是简单恢复模式，日志会自动截断，压力小很多。
• 避免长事务：绝对不要把所有更新放在一个大事务里，否则不仅会锁表很久，还会导致日志暴涨，甚至数据库崩溃。

内容的提问来源于stack exchange，提问作者Alexander Bushong