MERGE...USING同步源表到目标表过慢的优化方案咨询

源表约100万行（含重复数据），目标表1000万行且缓慢增长。现有MERGE语句在本地SQL Server Express执行约7分钟，Azure生产环境需40分钟。执行计划显示大量重型Sort运算符，导致tempdb溢出（Azure环境更严重）。因分组和MERGE匹配涉及大量列，全列索引方案不可行，需优化实现以下逻辑：

• 源表唯一记录与目标表无匹配时，插入目标表
• 匹配时仅更新目标表的UpdatedDateTime字段

现有MERGE语句 #

MERGE TargetTable AS target
USING (SELECT
           [Name], [CategoryName],
           [Field01], [Field02], [Field03], [Field04], [Field05],
           [Field06], [Field07], [Field08], [Field09], [Field10],
           [Value], [StartDate], [EndDate]
       FROM 
           SourceTable
       GROUP BY
           [Name], [CategoryName], 
           [Field01], [Field02], [Field03], [Field04], [Field05],
           [Field06], [Field07], [Field08], [Field09], [Field10],
           [Value], [StartDate], [EndDate]) 
    AS source ([Name], [CategoryName], 
               [Field01], [Field02], [Field03], [Field04], [Field05],
               [Field06], [Field07], [Field08], [Field09], [Field10],
               [Value], [StartDate], [EndDate])
        ON (target.[Name]           = source.[Name] AND
            target.[CategoryName]   = source.[CategoryName] AND
            target.[Field01]        = source.[Field01] AND
            target.[Field02]        = source.[Field02] AND
            target.[Field03]        = source.[Field03] AND
            UPPER(target.[Field04]) = source.[Field04] COLLATE SQL_Latin1_General_CP1_CS_AS AND
            UPPER(target.[Field05]) = source.[Field05] COLLATE SQL_Latin1_General_CP1_CS_AS AND
            UPPER(target.[Field06]) = source.[Field06] COLLATE SQL_Latin1_General_CP1_CS_AS AND
            target.[Value]          = source.[Value] AND
            target.[StartDate]      = source.[StartDate] AND
            target.[EndDate]        = source.[EndDate])  

WHEN MATCHED THEN
    UPDATE SET [UpdatedDateTime] = GETUTCDATE()
                       
WHEN NOT MATCHED THEN  
    INSERT ([Name], [CategoryName],
            [Field01], [Field02], [Field03], [Field04], [Field05],
            [Field06], [Field07], [Field08], [Field09], [Field10],
            [Value], [StartDate], [EndDate], [UpdatedDateTime])
    VALUES
        (source.[Name], source.[CategoryName],
         source.[Field01], source.[Field02], source.[Field03], source.[Field04], source.[Field05],
         source.[Field06], source.[Field07], source.[Field08], source.[Field09], source.[Field10],
         source.[Value], source.[StartDate], source.[EndDate], GETUTCDATE());

1. 替换GROUP BY为DISTINCT减少排序开销 #

GROUP BY会强制触发排序去重，改用SELECT DISTINCT通常能生成更高效的执行计划，避免不必要的排序操作：

SELECT DISTINCT
    [Name], [CategoryName],
    [Field01], [Field02], [Field03], [Field04], [Field05],
    [Field06], [Field07], [Field08], [Field09], [Field10],
    [Value], [StartDate], [EndDate]
FROM SourceTable

2. 预处理源表大小写转换，消除匹配时的计算开销 #

MERGE匹配条件中对Field04/05/06的UPPER()计算会导致无法利用索引，先在源表预处理转换后的值：

SELECT DISTINCT
    [Name], [CategoryName],
    [Field01], [Field02], [Field03],
    UPPER([Field04]) AS [Field04],
    UPPER([Field05]) AS [Field05],
    UPPER([Field06]) AS [Field06],
    [Field07], [Field08], [Field09], [Field10],
    [Value], [StartDate], [EndDate]
FROM SourceTable

同时修改MERGE的ON条件，去掉目标表的UPPER计算，改为直接匹配：

ON (target.[Name]           = source.[Name] AND
    target.[CategoryName]   = source.[CategoryName] AND
    target.[Field01]        = source.[Field01] AND
    target.[Field02]        = source.[Field02] AND
    target.[Field03]        = source.[Field03] AND
    target.[Field04]        = source.[Field04] COLLATE SQL_Latin1_General_CP1_CS_AS AND
    target.[Field05]        = source.[Field05] COLLATE SQL_Latin1_General_CP1_CS_AS AND
    target.[Field06]        = source.[Field06] COLLATE SQL_Latin1_General_CP1_CS_AS AND
    target.[Value]          = source.[Value] AND
    target.[StartDate]      = source.[StartDate] AND
    target.[EndDate]        = source.[EndDate])

如果目标表的Field04/05/06存储混合大小写，建议先一次性统一转为大写，后续写入时强制大写，彻底消除匹配时的计算开销。

3. 拆分MERGE为单独的UPDATE和INSERT操作 #

MERGE在处理大表时易生成低效执行计划，拆分操作可精准控制逻辑：

先执行UPDATE（仅更新匹配记录） #

UPDATE t
SET t.[UpdatedDateTime] = GETUTCDATE()
FROM TargetTable t
JOIN (
    SELECT DISTINCT
        [Name], [CategoryName],
        [Field01], [Field02], [Field03],
        UPPER([Field04]) AS [Field04],
        UPPER([Field05]) AS [Field05],
        UPPER([Field06]) AS [Field06],
        [Value], [StartDate], [EndDate]
    FROM SourceTable
) s ON 
    t.[Name] = s.[Name] AND
    t.[CategoryName] = s.[CategoryName] AND
    t.[Field01] = s.[Field01] AND
    t.[Field02] = s.[Field02] AND
    t.[Field03] = s.[Field03] AND
    t.[Field04] = s.[Field04] COLLATE SQL_Latin1_General_CP1_CS_AS AND
    t.[Field05] = s.[Field05] COLLATE SQL_Latin1_General_CP1_CS_AS AND
    t.[Field06] = s.[Field06] COLLATE SQL_Latin1_General_CP1_CS_AS AND
    t.[Value] = s.[Value] AND
    t.[StartDate] = s.[StartDate] AND
    t.[EndDate] = s.[EndDate];

再执行INSERT（插入无匹配的唯一记录） #

INSERT INTO TargetTable (
    [Name], [CategoryName],
    [Field01], [Field02], [Field03], [Field04], [Field05],
    [Field06], [Field07], [Field08], [Field09], [Field10],
    [Value], [StartDate], [EndDate], [UpdatedDateTime]
)
SELECT DISTINCT
    s.[Name], s.[CategoryName],
    s.[Field01], s.[Field02], s.[Field03], s.[Field04], s.[Field05],
    s.[Field06], s.[Field07], s.[Field08], s.[Field09], s.[Field10],
    s.[Value], s.[StartDate], s.[EndDate], GETUTCDATE()
FROM SourceTable s
LEFT JOIN TargetTable t ON 
    t.[Name] = s.[Name] AND
    t.[CategoryName] = s.[CategoryName] AND
    t.[Field01] = s.[Field01] AND
    t.[Field02] = s.[Field02] AND
    t.[Field03] = s.[Field03] AND
    UPPER(t.[Field04]) = s.[Field04] COLLATE SQL_Latin1_General_CP1_CS_AS AND
    UPPER(t.[Field05]) = s.[Field05] COLLATE SQL_Latin1_General_CP1_CS_AS AND
    UPPER(t.[Field06]) = s.[Field06] COLLATE SQL_Latin1_General_CP1_CS_AS AND
    t.[Value] = s.[Value] AND
    t.[StartDate] = s.[StartDate] AND
    t.[EndDate] = s.[EndDate]
WHERE t.[Name] IS NULL;

4. 用临时表缓存源表唯一记录 #

将源表去重后的结果存入临时表，避免多次执行DISTINCT的开销，同时给临时表创建聚集索引提升JOIN效率：

CREATE TABLE #UniqueSource (
    [Name] NVARCHAR(255),
    [CategoryName] NVARCHAR(255),
    [Field01] NVARCHAR(255),
    [Field02] NVARCHAR(255),
    [Field03] NVARCHAR(255),
    [Field04] NVARCHAR(255),
    [Field05] NVARCHAR(255),
    [Field06] NVARCHAR(255),
    [Field07] NVARCHAR(255),
    [Field08] NVARCHAR(255),
    [Field09] NVARCHAR(255),
    [Field10] NVARCHAR(255),
    [Value] DECIMAL(18,2),
    [StartDate] DATE,
    [EndDate] DATE,
    PRIMARY KEY CLUSTERED ([Name], [CategoryName], [Field01], [Field02], [Field03], [Field04], [Field05], [Field06], [Value], [StartDate], [EndDate])
);

INSERT INTO #UniqueSource
SELECT DISTINCT
    [Name], [CategoryName],
    [Field01], [Field02], [Field03],
    UPPER([Field04]) AS [Field04],
    UPPER([Field05]) AS [Field05],
    UPPER([Field06]) AS [Field06],
    [Field07], [Field08], [Field09], [Field10],
    [Value], [StartDate], [EndDate]
FROM SourceTable;

之后基于临时表执行UPDATE和INSERT操作即可。

5. Azure SQL环境额外优化 #

• 检查Azure SQL服务层级，确保CPU、内存、tempdb存储资源充足，tempdb溢出严重多为内存不足导致
• 开启READ_COMMITTED_SNAPSHOT隔离级别，减少锁竞争
• 将目标表的UpdatedDateTime设置为默认值GETUTCDATE()，插入时无需显式指定

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