SQL Server 2016下TSQL解析大JSON的性能优化咨询

1. 替换自定义函数为原生JSON函数 #

SQL Server 2016已原生支持JSON解析，OPENJSON是引擎级优化的函数，性能远高于自定义TSQL函数（自定义函数通常依赖字符串遍历、循环等低效操作）。

基础用法（返回所有JSON元素） #

如果你的自定义函数AA_F_ParseJSON是返回JSON的所有键值对，直接用OPENJSON替代即可：

DECLARE @LargeJSON NVARCHAR(MAX) = '你的大型JSON内容';

SELECT 
    [key] AS ElementKey,
    [value] AS ElementValue,
    type AS ElementType -- 0=null,1=string,2=number,3=object,4=array
FROM OPENJSON(@LargeJSON);

嵌套JSON解析 #

如果需要解析嵌套结构，可通过WITH子句定义目标表结构，直接映射JSON层级：

SELECT *
FROM OPENJSON(@LargeJSON, '$.nestedObject')
WITH (
    Id INT '$.id',
    Name NVARCHAR(100) '$.name',
    Tags NVARCHAR(MAX) '$.tags' AS JSON -- 保留嵌套JSON以便后续解析
);

2. 优化JSON输入处理 #

• 避免分段传入或拼接JSON：一次性传入完整的JSON字符串，减少函数内部的字符串操作开销。
• 确保JSON格式合法：提前校验JSON格式（比如在应用层），避免解析函数做无效的错误回退处理。

3. 大JSON分块解析 #

若JSON是超大数组结构，可拆分单个元素分批处理，降低内存压力：

-- 假设JSON是一个包含大量对象的数组
SELECT 
    JSON_VALUE(value, '$.id') AS ItemId,
    JSON_VALUE(value, '$.data') AS ItemData
FROM OPENJSON(@LargeJSON) -- 默认拆分数组元素
ORDER BY ItemId;

可结合OFFSET...FETCH实现分页处理，避免一次性加载所有数据。

4. 若必须保留自定义函数的优化方向 #

如果因业务限制无法替换自定义函数，可针对性优化：

• 替换游标/WHILE循环为基于集合的操作：避免逐行处理的低效逻辑。
• 减少重复字符串扫描：将中间结果存入临时表或表变量，避免多次解析同一JSON片段。
• 查看执行计划：定位函数中耗时的步骤（比如正则表达式匹配、递归调用），针对性替换为更高效的逻辑。

5. 服务器资源优化 #

• 调整内存配置：增大MAX_SERVER_MEMORY，确保SQL Server有足够内存缓存JSON数据，减少磁盘交换。
• 优化磁盘IO：若JSON从磁盘读取，使用SSD存储相关文件，提升读取速度。

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