优化含CTE与MERGE的存储过程：性能提升方案咨询

架构老林

2026-7-7

存储过程性能优化请求

我编写了一个批量更新两张数据表的存储过程，它接收SKU的JSON数据及若干参数。其中ProductValidation表仅需执行UPDATE操作（数据源自该表）；ProductValidation_Feedback表可能存在或不存在对应记录，因此需要支持UPDATE或INSERT操作。目前该过程已可正常运行，但效率可能偏低，希望从速度与性能角度进行优化，恳请专业人士提供改进建议。

现有存储过程代码

WITH cte AS (

    SELECT
        SellingSkuCode AS SellingSkuCodez
    FROM
        OPENJSON( @json ) WITH (
            SellingSkuCode                varchar(100),
            MaterialDescription           varchar(8000),
            TranslatedMaterialDescription varchar(8000),
            LocalErpSubbrand              varchar(8000),
            HarmonizedSubbrand            varchar(500),
            GrossRevenue                  varchar(100),
            Priority                      varchar(100),
            Validated                     varchar(100),
            MarketUnitName                varchar(100),
            UpdatedBy                     varchar(100)
        )
),
cte2 AS (
    SELECT
        *
    FROM
        cte
        INNER JOIN ProductValidation B ON cte.SellingSkuCodez = B.SellingSkuCode
)
MERGE ProductValidation original
USING cte2 modified ON (
    original.SellingSkuCode = modified.SellingSkuCode
    AND
    original.MarketUnitName = @Market
)
WHEN MATCHED THEN UPDATE SET

    original.SellingSkuCode                = modified.SellingSkuCode,
    original.MaterialDescription           = modified.MaterialDescription,
    original.TranslatedMaterialDescription = modified.TranslatedMaterialDescription,
    original.LocalErpSubbrand              = modified.LocalErpSubbrand,
    original.HarmonizedSubbrand            = @HarmonizedSubbrand,
    original.GrossRevenue                  = modified.GrossRevenue,
    original.Priority                      = modified.Priority,
    original.Validated                     = modified.Validated,
    original.MarketUnitName                = @Market,
    original.UpdatedBy                     = @UpdatedBy

WHEN NOT MATCHED BY TARGET THEN
    INSERT (
        SellingSkuCode,
        MaterialDescription,
        TranslatedMaterialDescription,
        LocalErpSubbrand,
        HarmonizedSubbrand,
        GrossRevenue,
        Priority,
        Validated,
        MarketUnitName,
        UpdatedBy
    )
    VALUES (
        SellingSkuCode,
        modified.MaterialDescription, 
        modified.TranslatedMaterialDescription,
        modified.LocalErpSubbrand,
        @HarmonizedSubbrand,
        modified.GrossRevenue,
        modified.Priority,
        modified.Validated,
        @Market,
        @UpdatedBy
);


WITH cte AS (
    SELECT
        SellingSkuCode AS SellingSkuCodez
    FROM
        OPENJSON(@json) WITH (
            SellingSkuCode                varchar(100),
            MaterialDescription           varchar(8000),
            TranslatedMaterialDescription varchar(8000),
            LocalErpSubbrand              varchar(8000),
            HarmonizedSubbrand            varchar(500),
            GrossRevenue                  varchar(100),
            Priority                      varchar(100),
            Validated                     varchar(100),
            MarketUnitName                varchar(100),
            UpdatedBy                     varchar(100)
        )
),
cte2 AS (
    SELECT
        *
    FROM
        cte
        INNER JOIN ProductValidation B ON cte.SellingSkuCodez = B.SellingSkuCode
)
MERGE ProductValidation_Feedback original
USING cte2 modified ON (
    original.SellingSkuCode = modified.SellingSkuCode
    AND
    original.MarketUnitName = modified.MarketUnitName
)
WHEN MATCHED THEN UPDATE SET
    original.SellingSkuCode                = modified.SellingSkuCode,
    original.MaterialDescription           = modified.MaterialDescription,
    original.TranslatedMaterialDescription = modified.TranslatedMaterialDescription,
    original.LocalErpSubbrand              = modified.LocalErpSubbrand,
    original.HarmonizedSubbrand            = @HarmonizedSubbrand,
    original.GrossRevenue                  = modified.GrossRevenue,
    original.Priority                      = modified.Priority,
    original.Validated                     = modified.Validated,
    original.MarketUnitName                = @Market,
    original.UpdatedBy                     = @UpdatedBy

WHEN NOT MATCHED BY TARGET THEN
    INSERT (
        SellingSkuCode,
        MaterialDescription,
        TranslatedMaterialDescription,
        LocalErpSubbrand,
        HarmonizedSubbrand,
        GrossRevenue,
        Priority,
        Validated,
        MarketUnitName,
        UpdatedBy
    )
    VALUES (
        SellingSkuCode,
        modified.MaterialDescription,
        modified.TranslatedMaterialDescription,
        modified.LocalErpSubbrand,
        @HarmonizedSubbrand,
        modified.GrossRevenue,
        modified.Priority,
        modified.Validated,
        @Market,
        @UpdatedBy
);

优化建议

1. 避免重复解析JSON

原代码两次调用OPENJSON解析同一个@json参数，会重复消耗CPU和内存。建议将解析后的JSON数据一次性存入临时表，后续操作直接复用该表，减少重复计算：

DROP TABLE IF EXISTS #JsonData;
CREATE TABLE #JsonData (
    SellingSkuCode                varchar(100),
    MaterialDescription           varchar(8000),
    TranslatedMaterialDescription varchar(8000),
    LocalErpSubbrand              varchar(8000),
    GrossRevenue                  varchar(100),
    Priority                      varchar(100),
    Validated                     varchar(100)
);

INSERT INTO #JsonData
SELECT SellingSkuCode, MaterialDescription, TranslatedMaterialDescription, LocalErpSubbrand, GrossRevenue, Priority, Validated
FROM OPENJSON( @json ) WITH (
    SellingSkuCode                varchar(100),
    MaterialDescription           varchar(8000),
    TranslatedMaterialDescription varchar(8000),
    LocalErpSubbrand              varchar(8000),
    GrossRevenue                  varchar(100),
    Priority                      varchar(100),
    Validated                     varchar(100)
);

2. 简化`ProductValidation`表的更新逻辑

你提到该表仅需UPDATE操作，原代码的MERGE包含不必要的INSERT分支，且CTE关联逻辑冗余。直接用临时表关联目标表做UPDATE即可：

UPDATE pv
SET
    MaterialDescription           = jd.MaterialDescription,
    TranslatedMaterialDescription = jd.TranslatedMaterialDescription,
    LocalErpSubbrand              = jd.LocalErpSubbrand,
    HarmonizedSubbrand            = @HarmonizedSubbrand,
    GrossRevenue                  = jd.GrossRevenue,
    Priority                      = jd.Priority,
    Validated                     = jd.Validated,
    UpdatedBy                     = @UpdatedBy
FROM ProductValidation pv
JOIN #JsonData jd ON pv.SellingSkuCode = jd.SellingSkuCode
WHERE pv.MarketUnitName = @Market;

3. 移除不必要的字段更新

原代码更新了SellingSkuCode、MarketUnitName等作为匹配条件的字段，这些字段值本身与源数据一致，完全不需要更新，减少日志写入和IO开销。

4. 优化索引

给ProductValidation表创建(SellingSkuCode, MarketUnitName)组合非聚集索引，加速UPDATE的匹配查找。
给ProductValidation_Feedback表创建(SellingSkuCode, MarketUnitName)组合主键或唯一索引，MERGE操作依赖该索引快速定位记录，避免全表扫描。

5. 开启`SET NOCOUNT ON`

在存储过程开头添加SET NOCOUNT ON;，避免SQL Server返回每个操作的受影响行数，减少网络传输和资源消耗。

6. 匹配数据类型

检查JSON解析时指定的字段类型是否与目标表字段类型完全一致（比如GrossRevenue如果是数值类型，不要用varchar解析），避免隐式转换导致索引失效。

优化后完整示例代码

SET NOCOUNT ON;

-- 一次性解析JSON到临时表
DROP TABLE IF EXISTS #JsonData;
CREATE TABLE #JsonData (
    SellingSkuCode                varchar(100),
    MaterialDescription           varchar(8000),
    TranslatedMaterialDescription varchar(8000),
    LocalErpSubbrand              varchar(8000),
    GrossRevenue                  varchar(100),
    Priority                      varchar(100),
    Validated                     varchar(100)
);

INSERT INTO #JsonData
SELECT
    SellingSkuCode,
    MaterialDescription,
    TranslatedMaterialDescription,
    LocalErpSubbrand,
    GrossRevenue,
    Priority,
    Validated
FROM OPENJSON( @json ) WITH (
    SellingSkuCode                varchar(100),
    MaterialDescription           varchar(8000),
    TranslatedMaterialDescription varchar(8000),
    LocalErpSubbrand              varchar(8000),
    GrossRevenue                  varchar(100),
    Priority                      varchar(100),
    Validated                     varchar(100)
);

-- 更新ProductValidation表
UPDATE pv
SET
    MaterialDescription           = jd.MaterialDescription,
    TranslatedMaterialDescription = jd.TranslatedMaterialDescription,
    LocalErpSubbrand              = jd.LocalErpSubbrand,
    HarmonizedSubbrand            = @HarmonizedSubbrand,
    GrossRevenue                  = jd.GrossRevenue,
    Priority                      = jd.Priority,
    Validated                     = jd.Validated,
    UpdatedBy                     = @UpdatedBy
FROM ProductValidation pv
JOIN #JsonData jd ON pv.SellingSkuCode = jd.SellingSkuCode
WHERE pv.MarketUnitName = @Market;

-- 处理ProductValidation_Feedback表的MERGE
MERGE ProductValidation_Feedback pvf
USING #JsonData jd ON pvf.SellingSkuCode = jd.SellingSkuCode
                  AND pvf.MarketUnitName = @Market
WHEN MATCHED THEN UPDATE SET
    MaterialDescription           = jd.MaterialDescription,
    TranslatedMaterialDescription = jd.TranslatedMaterialDescription,
    LocalErpSubbrand              = jd.LocalErpSubbrand,
    HarmonizedSubbrand            = @HarmonizedSubbrand,
    GrossRevenue                  = jd.GrossRevenue,
    Priority                      = jd.Priority,
    Validated                     = jd.Validated,
    UpdatedBy                     = @UpdatedBy
WHEN NOT MATCHED BY TARGET THEN
    INSERT (
        SellingSkuCode,
        MaterialDescription,
        TranslatedMaterialDescription,
        LocalErpSubbrand,
        HarmonizedSubbrand,
        GrossRevenue,
        Priority,
        Validated,
        MarketUnitName,
        UpdatedBy
    )
    VALUES (
        jd.SellingSkuCode,
        jd.MaterialDescription,
        jd.TranslatedMaterialDescription,
        jd.LocalErpSubbrand,
        @HarmonizedSubbrand,
        jd.GrossRevenue,
        jd.Priority,
        jd.Validated,
        @Market,
        @UpdatedBy
);

DROP TABLE IF EXISTS #JsonData;

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

优化含CTE与MERGE的存储过程：性能提升方案咨询

现有存储过程代码 #

优化建议 #

1. 避免重复解析JSON #

2. 简化ProductValidation表的更新逻辑 #

3. 移除不必要的字段更新 #

4. 优化索引 #

5. 开启SET NOCOUNT ON #

6. 匹配数据类型 #

优化后完整示例代码 #