SQL Server中WHILE循环插入性能优化咨询

这问题太典型了——逐行处理50万行数据肯定会慢到让人崩溃！我来给你拆解下优化思路，直接上干货：

你的代码属于**RBAR（Row By Agonizing Row）**操作，也就是逐行处理。数据库天生是为批量操作设计的，逐行循环会反复触发锁申请、事务日志写入、执行计划编译，50万行下来时间成本会指数级上升，完全浪费了数据库的批量处理能力。

每10,000行提交一次确实能大幅提升速度！大事务会持续占用大量事务日志空间，还会长时间持有锁导致其他操作阻塞。把大事务拆成多个小事务，既能减少日志压力，降低锁持有时间，还能避免单次事务过大导致的回滚风险（万一中间出错，不用回滚全部50万行）。

最关键的优化是用批量插入+OUTPUT子句替代逐行操作，这样能一次性处理一批数据，同时直接捕获DB2生成的自增主键，不用依赖IDENT_CURRENT（这个函数在并发场景下还可能返回错误值）。

具体步骤： #

1. 保留存储DB1复合键的临时表（注意索引优化，你的#TempTable已经给ROWID加了主键，没问题）
2. 分批从临时表中取出复合键对应的DB1数据，批量插入DB2，同时用OUTPUT子句把插入的自增ID和对应的复合键存入映射临时表
3. 用映射临时表批量更新DB1的ID字段
4. 每处理完一批就提交事务

完整优化代码示例 #

-- 1. 创建临时表存储DB1的复合键（注意：你原代码里写的是FILES表，应该是ADDRESS吧？）
CREATE TABLE #TempTable (
    ROWID int identity(1,1) primary key,
    Comp_Key_1 NVARCHAR(20),
    Comp_Key_2 NVARCHAR(256),
    Comp_Key_3 NVARCHAR(256)
)
INSERT INTO #TempTable (Comp_Key_1, Comp_Key_2, Comp_Key_3)
SELECT Comp_Key_1, Comp_Key_2, Comp_Key_3 
FROM [DB1].[dbo].ADDRESS

-- 2. 创建临时表存储ID映射关系（DB2自增ID <-> DB1复合键）
CREATE TABLE #IdMapping (
    NewId INT,
    Comp_Key_1 NVARCHAR(20),
    Comp_Key_2 NVARCHAR(256),
    Comp_Key_3 NVARCHAR(256)
)

DECLARE @BatchSize INT = 10000; -- 可根据服务器性能调整
DECLARE @StartRow INT = 1;
DECLARE @MaxRow INT;

SELECT @MaxRow = COUNT(*) FROM #TempTable;

WHILE @StartRow <= @MaxRow
BEGIN
    BEGIN TRANSACTION;

    -- 清空当前批次的映射数据
    TRUNCATE TABLE #IdMapping;

    -- 批量插入DB2，同时捕获自增ID和复合键
    INSERT INTO [DB2].[dbo].[ADDRESS] (STREET, STREET_FROM, STREET_TO)
    OUTPUT inserted.Id, t.Comp_Key_1, t.Comp_Key_2, t.Comp_Key_3 INTO #IdMapping
    SELECT a.STREET, a.STREET_FROM, a.STREET_TO
    FROM [DB1].[dbo].[ADDRESS] a
    JOIN #TempTable t ON 
        a.Comp_Key_1 = t.Comp_Key_1 
        AND a.Comp_Key_2 = t.Comp_Key_2 
        AND a.Comp_Key_3 = t.Comp_Key_3
    WHERE t.ROWID BETWEEN @StartRow AND @StartRow + @BatchSize - 1;

    -- 批量更新DB1的ID字段
    UPDATE a
    SET a.id = m.NewId
    FROM [DB1].[dbo].[ADDRESS] a
    JOIN #IdMapping m ON 
        a.Comp_Key_1 = m.Comp_Key_1 
        AND a.Comp_Key_2 = m.Comp_Key_2 
        AND a.Comp_Key_3 = m.Comp_Key_3;

    COMMIT TRANSACTION;

    SET @StartRow = @StartRow + @BatchSize;

    -- 可选：打印进度，方便监控
    PRINT '已完成 ' + CAST(@StartRow - 1 AS VARCHAR) + ' 行数据处理';
END

-- 清理临时表
DROP TABLE #TempTable;
DROP TABLE #IdMapping;

• 禁用DB2的非聚集索引：插入前先禁用DB2.ADDRESS表的非聚集索引，插入完成后再重建。因为每次插入都会维护索引，批量插入前禁用能大幅减少IO开销。
• 调整恢复模式：把DB2的恢复模式改成SIMPLE（插入前修改，完成后改回原模式），这样事务日志会自动截断，避免日志文件暴涨占用磁盘空间。
• 索引优化：确保DB1.ADDRESS表的复合键（Comp_Key_1, Comp_Key_2, Comp_Key_3）有主键或唯一索引，这样JOIN和UPDATE操作的效率会大幅提升。
• 调整批量大小：如果服务器CPU、内存、磁盘IO足够，可以把@BatchSize调到20000甚至50000，减少循环次数，但不要太大导致事务日志压力过高。
• 避免NOLOCK滥用：如果业务允许脏读，可以在SELECT时加WITH(NOLOCK)，但要注意数据一致性风险，谨慎使用。

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