SQL Server非规范化转规范化表迁移性能低下，求优化方案

问题背景 #

我们有一个SQL Server非规范化表，计划迁移至规范化格式，但编写的迁移查询延迟严重。查看实际执行计划发现预估行数远高于实际行数。

旧表数据模型 #

Column A (PK Column): Long
Column1: String
Column2: String
Column3: String

新表数据模型 #

Column A (PK Column): Long
Column1: Long (FK，关联另一张表)
Column2: Long (FK，关联另一张表)
Column3: Long (FK，关联另一张表)

当前迁移SQL语句 #

insert into newTable (pkcolumn, column1, column2, column3)
select 
    pkColumn,
    anothertable1.id,
    anothertable2.id,
    anothertable3.id
left join 
    anothertable as anothertable1 on anothertable1.field_id = 1 
                                  and anothertable1.value = oldtable.column1 
                                  and oldtable.pkColumn in (1,2,3,4,5,6,7,8,9,10)
left join 
    anothertable as anothertable2 on anothertable2.field_id = 2 
                                  and anothertable2.value = oldtable.column2 
                                  and oldtable.pkColumn in (1,2,3,4,5,6,7,8,9,10)
left join 
    anothertable as anothertable3 on anothertable3.field_id = 3 
                                  and anothertable3.value = oldtable.column3 
                                  and oldtable.pkColumn in (1,2,3,4,5,6,7,8,9,10)

• 批量大小：每次处理oldtable.pkColumn in (1,2,3,...,1000)范围内的数据
• 已执行操作：更新了查询涉及表的统计信息，重建了查询所用列的索引

优化建议 #

1. 修正JOIN逻辑，前置过滤条件 #

原查询将oldtable.pkColumn in (...)放在JOIN的ON子句中，会导致先全表关联再过滤数据。应先筛选出oldtable中需要迁移的行，再关联其他表，减少关联数据量：

WITH TargetOldRows AS (
    SELECT pkColumn, Column1, Column2, Column3
    FROM oldtable
    WHERE pkColumn IN (1,2,...,1000)
)
INSERT INTO newTable (pkcolumn, column1, column2, column3)
SELECT 
    tor.pkColumn,
    at1.id,
    at2.id,
    at3.id
FROM TargetOldRows tor
LEFT JOIN anothertable at1 ON at1.field_id = 1 AND at1.value = tor.Column1
LEFT JOIN anothertable at2 ON at2.field_id = 2 AND at2.value = tor.Column2
LEFT JOIN anothertable at3 ON at3.field_id = 3 AND at3.value = tor.Column3

2. 优化anothertable的索引 #

针对field_id + value创建复合索引，因为每次关联都是按固定field_id匹配value，复合索引能让SQL Server快速定位匹配行，避免全表扫描：

CREATE NONCLUSTERED INDEX IX_anothertable_FieldValue ON anothertable (field_id, value) INCLUDE (id);

3. 调整批量大小 #

当前1000行的批量可能不是最优值，可测试500、2000等不同批量，平衡内存压力、锁竞争和往返开销。

4. 强制生成新执行计划 #

由于预估行数与实际差异大，可添加OPTION (RECOMPILE)提示，让SQL Server针对当前数据重新生成执行计划，避免过时缓存：

-- 在上述CTE查询末尾添加
OPTION (RECOMPILE)

5. 拆分迁移任务 #

若旧表数据量极大，可按pkColumn范围拆分多个小任务并行执行（如分批次处理1-1000、1001-2000），利用多核CPU资源，注意避免锁冲突。

6. 临时关闭约束与触发器 #

迁移期间可临时关闭newTable的外键约束、触发器（迁移完成后重新开启），减少写入时的校验开销。操作前需做好数据备份，确保一致性。

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