SQL Server批量数据迁移性能优化咨询：8000万数据迁移耗时过长

问题 #

我们采用以下架构：

• Staging表（暂存表）：直接从外部数据库加载数据
• Read表（读取表）：每周将Staging表的数据经少量内连接后加载至此，减少用户访问时的查询开销

当前问题是Staging表数据量过大（Table1_Staging约8000万条记录），已耗尽DB日志，因此实现了批量处理脚本，但目前仅处理1/8数据就耗时约4小时。Table2约有400条记录，且已配置索引、唯一性约束及主键。

现有脚本如下：

DECLARE @PageSize int = 50000;
DECLARE @PageIndex int = 0;
DECLARE @TotalPages int = 0;


SELECT @TotalPages = ((count(1) / @PageSize) + 1)
FROM Table1_Staging t1
INNER JOIN Table2 t2 ON t2.r1 = t1.r7
INNER JOIN Table2 t3 ON t3.r1 = t1.r8

SELECT @TotalPages;

WHILE (@PageIndex <= @TotalPages)
BEGIN
    BEGIN TRANSACTION
        INSERT INTO Table1 ( [row1], [row2], [row3], [row4],
                              [row5], [row6], [row7], [row8])
        SELECT  t1.r2,
                t1.r3,
                t2.r1,
                t2.r3,
                t3.r3,
                t1.r6,
                IIF(TRY_CONVERT(datetime, t1.DATE) IS NULL,
                    DATEADD(YEAR, -1, GETDATE()),
                    TRY_CONVERT(datetime, t1.DATE))
                    AS date,
                t1.r1
        FROM Table1_Staging t1
                    INNER JOIN Table2 t2 ON t2.r1 = t1.r7
                    INNER JOIN Table2 t3 ON t3.r1 = t1.r8
        ORDER BY t1.r2 DESC
        OFFSET (@PageSize * @PageIndex) ROWS
        FETCH NEXT @PageSize ROWS ONLY;

    COMMIT TRANSACTION;

    /** Increments the Page Size */
    SET @PageIndex  = @PageIndex  + 1;
END

想请教：有什么优化该查询的建议？或是仅用SQL实现批量加载的其他方案？是否应将@PageSize增大至50万？

优化建议 #

1. 调整批量大小，同时监控日志 #

可以尝试将@PageSize调到50万，但要结合数据库恢复模式判断：

• 如果是简单恢复模式，更大的批次能减少事务提交次数和循环开销，提交后日志会自动截断，反而能降低总耗时，同时减少日志碎片化。
• 如果是完整恢复模式，大批次会生成更大的日志文件，要确保磁盘空间足够，或者先做一次日志备份再继续。
  建议先小范围测试（比如跑1-2个大批次），对比耗时和日志增长情况，再决定最终大小。

2. 替换OFFSET/FETCH为范围分页，避免全表扫描 #

原脚本用OFFSET/FETCH分页，随着@PageIndex增大，数据库每次都要扫描并跳过前面所有行，性能会急剧下降。改用基于唯一键的范围查询，效率会提升很多：

DECLARE @PageSize int = 500000;
DECLARE @LastR2 [t1.r2的数据类型] = (SELECT MAX(r2) FROM Table1_Staging); -- 匹配原脚本的ORDER BY r2 DESC逻辑

WHILE @LastR2 IS NOT NULL
BEGIN
    BEGIN TRANSACTION
        INSERT INTO Table1 ( [row1], [row2], [row3], [row4],
                              [row5], [row6], [row7], [row8])
        SELECT TOP (@PageSize)
                t1.r2,
                t1.r3,
                t2.r1,
                t2.r3,
                t3.r3,
                t1.r6,
                IIF(TRY_CONVERT(datetime, t1.DATE) IS NULL,
                    DATEADD(YEAR, -1, GETDATE()),
                    TRY_CONVERT(datetime, t1.DATE)) AS date,
                t1.r1
        FROM Table1_Staging t1
        INNER JOIN Table2 t2 ON t2.r1 = t1.r7
        INNER JOIN Table2 t3 ON t3.r1 = t1.r8
        WHERE t1.r2 < @LastR2 -- 基于上一次的最后值做范围查询
        ORDER BY t1.r2 DESC;

        -- 更新@LastR2为本次批次的最小r2值，用于下一轮循环
        SET @LastR2 = (SELECT MIN(r2) FROM inserted);
    COMMIT TRANSACTION;
END

这种方式每次查询都是直接定位到目标范围，不需要扫描前面的所有行，大数据量下性能提升明显。

3. 预筛选数据到临时表，避免重复关联 #

原脚本每次循环都要重新关联Table1_Staging和Table2两次，这是重复开销。可以先把需要迁移的数据预筛选到临时表，再分批插入：

-- 先创建带索引的临时表，存储预处理后的结果
SELECT 
    t1.r2,
    t1.r3,
    t2.r1,
    t2.r3,
    t3.r3,
    t1.r6,
    IIF(TRY_CONVERT(datetime, t1.DATE) IS NULL,
        DATEADD(YEAR, -1, GETDATE()),
        TRY_CONVERT(datetime, t1.DATE)) AS date,
    t1.r1
INTO #TempMigrate
FROM Table1_Staging t1
INNER JOIN Table2 t2 ON t2.r1 = t1.r7
INNER JOIN Table2 t3 ON t3.r1 = t1.r8;

-- 给临时表加索引，方便后续分页
CREATE CLUSTERED INDEX IX_TempMigrate_R2 ON #TempMigrate(r2 DESC);

-- 然后用范围分页从临时表分批插入
DECLARE @PageSize int = 500000;
DECLARE @LastR2 [r2的数据类型] = (SELECT MAX(r2) FROM #TempMigrate);

WHILE @LastR2 IS NOT NULL
BEGIN
    BEGIN TRANSACTION
        INSERT INTO Table1 ( [row1], [row2], [row3], [row4],
                              [row5], [row6], [row7], [row8])
        SELECT TOP (@PageSize)
                r2, r3, r1, r3, r3, r6, date, r1
        FROM #TempMigrate
        WHERE r2 < @LastR2
        ORDER BY r2 DESC;

        SET @LastR2 = (SELECT MIN(r2) FROM inserted);
    COMMIT TRANSACTION;
END

DROP TABLE #TempMigrate;

临时表只需要关联一次，后续循环直接读取预处理好的数据，能大幅减少CPU和IO开销。

4. 临时禁用目标表的非聚集索引和触发器 #

插入大量数据时，维护非聚集索引会消耗大量时间，尤其是小批次插入时，每次都要更新索引。可以先禁用Table1的非聚集索引，插入完成后再重建：

-- 禁用非聚集索引
ALTER INDEX ALL ON Table1 DISABLE;

-- 执行批量插入脚本...

-- 重建非聚集索引（比每次插入更新索引快很多）
ALTER INDEX ALL ON Table1 REBUILD;

如果目标表有触发器，也建议暂时禁用，插入完成后再启用，避免触发器逻辑拖慢插入速度。

5. 调整数据库恢复模式 #

如果数据库当前是完整恢复模式，可以临时改成简单恢复模式，这样每次事务提交后日志会自动截断，避免日志耗尽，同时提升插入性能。操作完成后再改回完整模式（如果需要日志备份的话）：

-- 改为简单恢复模式
ALTER DATABASE [你的数据库名] SET RECOVERY SIMPLE;

-- 执行批量插入...

-- 改回完整恢复模式
ALTER DATABASE [你的数据库名] SET RECOVERY FULL;

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