优化SCD2方案中的INSERT查询（不使用MERGE语句）

API 探路者

2026-8-10

SCD2场景下非MERGE方式的INSERT语句优化方案

我通过两条INSERT语句实现SCD2（缓慢变化维度2型）逻辑，出于练习目的不使用MERGE语句：

第一条语句：当目标表中不存在对应id时插入新行
第二条语句：仅在源表id非空且关联后的行哈希值不同（表示源表行已更新）时插入新行

其中current列值为1表示该行是当前有效行，0表示已过期行，后续会利用该标记更新目标表中的过期行。现在需要处理数十万行的插入操作，除了添加索引外，有没有更高效的方法优化这些INSERT查询，使其效果接近SCD2方案中MERGE语句的新增/更新插入逻辑？

原始查询语句如下：

-- Query 1:
INSERT INTO TARGET
SELECT Name, Middlename, Age, 1 AS current, Row_HashValue, id
FROM Source s
WHERE s.id NOT IN (SELECT id FROM TARGET) AND s.id IS NOT NULL

-- Query 2:
INSERT INTO TARGET
SELECT Name, Middlename, Age, 1 AS current, Row_HashValue, id
FROM SOURCE s 
LEFT JOIN TARGET t ON s.id = t.id
AND s.Row_HashValue = t.Row_HashValue
WHERE t.Row_HashValue IS NULL AND s.ID IS NOT NULL

优化建议

合并两条INSERT为单条语句
两条语句会分别扫描源表和目标表至少两次，合并后可减少扫描次数，提升效率。可以通过NOT EXISTS直接过滤掉无需处理的记录（已存在的当前有效且哈希一致的行），一次完成新增和更新插入：
```
INSERT INTO TARGET
SELECT s.Name, s.Middlename, s.Age, 1 AS current, s.Row_HashValue, s.id
FROM SOURCE s
WHERE s.id IS NOT NULL
AND NOT EXISTS (
    SELECT 1 FROM TARGET t
    WHERE t.id = s.id 
      AND t.Row_HashValue = s.Row_HashValue 
      AND t.current = 1
);
```
这个写法直接筛选出需要新增的行（目标表无对应id）和需要更新插入的行（目标表有id但哈希值不同），避免重复扫描表。
替换NOT IN为NOT EXISTS
原Query1使用的NOT IN存在陷阱：如果TARGET表的id列包含NULL值，NOT IN会返回空结果，导致新行无法插入。NOT EXISTS不仅更可靠，性能也更优——它在找到匹配项后会立即停止扫描，无需遍历全部结果。
用临时表预处理源数据
先将源表中id非空的行导入临时表，在临时表上创建id和Row_HashValue的复合索引，再用临时表与目标表关联。临时表的IO效率更高，能减少重复扫描大体积源表的开销，尤其适合数十万行的批量处理场景。

分批次批量插入
一次性插入数十万行会占用大量锁资源和日志空间，还会增加失败回滚的风险。建议分批次插入（比如每批次1000-5000行），可以用ROW_NUMBER()进行分页拆分：

DECLARE @BatchSize INT = 1000;
DECLARE @CurrentBatch INT = 1;

WHILE EXISTS (SELECT 1 FROM SOURCE WHERE id IS NOT NULL)
BEGIN
    INSERT INTO TARGET
    SELECT Name, Middlename, Age, 1 AS current, Row_HashValue, id
    FROM (
        SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS RowNum
        FROM SOURCE
        WHERE id IS NOT NULL
    ) s
    WHERE RowNum BETWEEN (@CurrentBatch - 1)*@BatchSize + 1 AND @CurrentBatch*@BatchSize
      AND NOT EXISTS (
          SELECT 1 FROM TARGET t
          WHERE t.id = s.id 
            AND t.Row_HashValue = s.Row_HashValue 
            AND t.current = 1
      );

    SET @CurrentBatch = @CurrentBatch + 1;
    -- 可选：删除已处理的源数据，避免重复处理
    DELETE FROM SOURCE WHERE id IS NOT NULL AND id IN (SELECT id FROM INSERTED);
END

控制事务大小
每批次插入后单独提交事务，避免单事务过大导致日志暴涨，同时减少回滚时的资源消耗。

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

优化SCD2方案中的INSERT查询（不使用MERGE语句）

优化建议 #

优化建议