高级T-SQL：批量更新多行多列如何找到性能最优平衡点？

背景情况 #

我目前在MDM（主数据管理）/抽象分层分类/功能型SQL Server环境下工作，核心问题聚焦在数据驱动计算完成后的结果保存环节——我想找到批量更新多行多列时的性能「最优平衡点」。当前的实现逻辑是：把JSON数据传入SQL函数，由函数自动生成插入/更新语句（如果字段值和现有数据匹配，就跳过更新操作）。

目标表结构 #

目标表tblDestination的创建语句如下：

create table tblDestination ( 
    sysPrimaryKey bigint identity(1,1) 
    , sysHeaderId bigint -- udt_ForeignKey 
    , sysLevel1Id bigint -- udt_ForeignKey (一级分类ID) 
    , strText nvarchar(100) -- 以下为需要更新的业务字段 
    , dtmDate datetime2(7) 
    , numNumeric float 
    , flgFlag bit 
    , intInteger bigint 
    , sysRefKey bigint-- 关联外键 
    , primary key non clustered (sysPrimaryKey) 
)

补充说明：该表已经存在建模合理的聚集索引，包含上述未列出的列；测试时可以按需使用聚集索引、非聚集索引或唯一索引。

传入的JSON示例 #

JSON里的自定义字段数量在2到100之间，示例如下：

declare @JSON nvarchar(max)='{"ARBITRARY NAME 1":"3/1/2017","ARBITRARY NAME 2": "Value", "ARBITRARY NAME 3": 45.3}' 

当前的实现方式 #

函数通过游标遍历传入的@JSON来构建增改语句，核心代码框架如下：

DECLARE @JSON_Key NVARCHAR(100), @Value NVARCHAR(MAX)
DECLARE jsonCursor CURSOR local static forward_only read_only 
FOR SELECT [key] AS JSON_Key, [value] FROM OPENJSON(@json) 

OPEN jsonCursor
FETCH NEXT FROM jsonCursor INTO @JSON_Key, @Value

WHILE @@fetch_status = 0
BEGIN
    -- 这里是获取自定义ID对应的标识及分类，构建插入/更新语句的业务逻辑
    FETCH NEXT FROM jsonCursor INTO @JSON_Key, @Value
END

CLOSE jsonCursor
DEALLOCATE jsonCursor

性能优化的核心方向（找最优平衡点） #

针对当前游标逐行处理的低效问题，结合场景特点，从性能和资源平衡的角度可以试试这些思路：

• 替换游标为集合式操作：游标是逐行循环，批量数据下性能拖后腿，改用OPENJSON直接解析JSON为临时结果集，再结合MERGE语句一次性完成「匹配则更新（有变化才更）、不匹配则插入」的逻辑，能大幅减少循环开销。
• 针对性优化索引：针对MERGE的匹配条件（比如sysHeaderId、sysLevel1Id这类关联字段）创建非聚集索引，提升数据匹配的效率；同时确保聚集索引的覆盖性，减少查询时的键查找开销。
• 控制批量大小：当自定义字段接近100个时，可把JSON数据拆分成合理的批次（比如每50条一组），避免单次操作占用过多CPU、内存资源，平衡处理吞吐量和系统负载。
• 优化跳过更新的逻辑：在MERGE的WHEN MATCHED条件里加上字段值对比（比如WHEN MATCHED AND target.strText <> source.strText OR ...），只有当字段实际有变化时才执行更新，减少不必要的写操作。
• 提升JSON解析效率：使用OPENJSON的WITH子句明确指定字段类型，避免隐式类型转换带来的性能损耗，让解析过程更高效。

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