SQL Server跨库批量插入优化求助：分批插入耗时超全量

原查询2慢的核心原因 #

原分批逻辑每次循环都要对源表全表扫描并执行ROW_NUMBER()排序，1300万行数据重复执行该操作会产生极高的IO和CPU开销，这是耗时翻倍的主要原因。

优化后的分批插入脚本 #

改用基于有序列的范围分批，利用源表的有序列（这里用Column1，假设它是唯一且有序的，比如主键或带索引的列）定位批次边界，避免全表扫描：

DECLARE @batch INT = 100000;
DECLARE @maxCol1 INT; -- 若Column1为其他数据类型，需同步修改变量类型
DECLARE @currentMinCol1 INT;
DECLARE @currentMaxCol1 INT;

-- 初始化批次起始与结束的基准值
SELECT @currentMinCol1 = MIN(Column1), @maxCol1 = MAX(Column1)
FROM [Database_Source].[dbo].[Table1_Source];

WHILE @currentMinCol1 <= @maxCol1
BEGIN
    BEGIN TRANSACTION;

    -- 获取当前批次的最大Column1值，确保批次数据不重复、不遗漏
    SELECT @currentMaxCol1 = MAX(Column1)
    FROM (
        SELECT TOP (@batch) Column1
        FROM [Database_Source].[dbo].[Table1_Source]
        WHERE Column1 >= @currentMinCol1
        ORDER BY Column1
    ) AS BatchRange;

    -- 插入当前批次数据，添加TABLOCK提示提升批量插入效率
    INSERT INTO [Database_Dest].[dbo].[Table1_Dest] WITH (TABLOCK)
    (Column1, Column2, Column3)
    SELECT Column1, Column2, Column3
    FROM [Database_Source].[dbo].[Table1_Source]
    WHERE Column1 >= @currentMinCol1 
      AND Column1 <= @currentMaxCol1;

    SET @currentMinCol1 = @currentMaxCol1 + 1; -- 切换至下一批次的起始点
    COMMIT TRANSACTION;
END;

额外优化建议 #

• 临时禁用目标表的非聚集索引与约束：插入前执行ALTER INDEX ALL ON [Database_Dest].[dbo].[Table1_Dest] DISABLE;，插入完成后重建索引ALTER INDEX ALL ON [Database_Dest].[dbo].[Table1_Dest] REBUILD;；外键、检查约束同理，插入后再启用，避免每次插入都更新索引和校验约束。
• 调整批量大小：根据服务器内存、IO能力调整@batch值，可尝试50000或200000，找到最优批次规模。
• 给源表的Column1创建索引：若Column1无索引，先执行CREATE NONCLUSTERED INDEX IX_Table1_Source_Column1 ON [Database_Source].[dbo].[Table1_Source](Column1) INCLUDE(Column2, Column3);，大幅提升范围查询速度，插入完成后可按需删除该索引。
• 采用大容量日志恢复模式：若目标数据库恢复模式为完整，可临时切换为大容量日志模式，减少批量插入的日志开销，完成后再切回原模式。

内容的提问来源于stack exchange，提问作者Peter Nguy Nguyen