大型数据场景下JSON归一化存储过程性能优化咨询

问题背景 #

我开发的dbo.basic_json_normalization_incr存储过程负责从包含row_id（行ID）和row_data（JSON格式列）的原始表读取数据，解析JSON为结构化列后插入目标表，同时删除重复行。但处理超大规模数据时，该存储过程运行速度极慢。此前尝试用STRING_AGG替代游标构建动态SQL时，遇到错误：

The result of STRING_AGG aggregation has exceeded the 8,000-byte limit. Use LOB types to avoid truncation of the result

原存储过程代码如下：

CREATE OR ALTER PROCEDURE dbo.basic_json_normalization_incr
    @tableNamep NVARCHAR(MAX),
    @tableName NVARCHAR(MAX), 
    @rows_count INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @raw_tableName NVARCHAR(MAX);
    DECLARE @chargement NVARCHAR(MAX);
    DECLARE @truncateQuery NVARCHAR(MAX);
    DECLARE @columns_list NVARCHAR(MAX);
    DECLARE @json_query_part NVARCHAR(MAX);
    DECLARE @json_query NVARCHAR(MAX) = '';
    DECLARE @separator NVARCHAR(2) = '';
    DECLARE @sqldelete NVARCHAR(MAX);
    DECLARE @sqlinsert NVARCHAR(MAX);

    SET @raw_tableName = 'dbo.raw' + @tableNamep;

    SELECT @columns_list = COALESCE(@columns_list + ', ' + '[' + column_name + ']', '[' + column_name + ']')
    FROM dbo.schemaSource
    WHERE _enabled = 1 AND table_name = @tableNamep;

    DECLARE json_cursor CURSOR FOR
        SELECT CASE
                   WHEN single_mult = 'M'
                       THEN CONCAT('[', column_name, '] NVARCHAR(MAX) ', 'AS JSON')
                   ELSE CONCAT('[', column_name, '] NVARCHAR(MAX) ', '''' + '$.', column_name, '''')
               END
        FROM dbo.schemaSource WITH (NOLOCK)
        WHERE _enabled = 1
          AND table_name = @tableNamep;

    OPEN json_cursor;

    FETCH NEXT FROM json_cursor INTO @json_query_part;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @json_query = @json_query + @separator + @json_query_part;
        SET @separator = ',';
        
        FETCH NEXT FROM json_cursor INTO @json_query_part;
    END;

    CLOSE json_cursor;
    DEALLOCATE json_cursor;

    -- Determine the duplicate check column based on table name
    DECLARE @duplicateCheckColumn NVARCHAR(MAX);
    
    SET @duplicateCheckColumn = '_id';
    
    -- Build the dynamic SQL query for insertion with duplicate and CURR_NO check

    SET @sqldelete = N'DELETE FROM ' + @tableName + ' WHERE _id COLLATE French_CI_AS IN (SELECT raw_id COLLATE French_CI_AS FROM raw' + @tableNamep + ');';
        
    SET @sqlinsert =  N'
    INSERT INTO dbo.' + @tableName + '(_id, ORIGINAL_ID, ' + @columns_list + ')
    SELECT j.[_id], j.[ORIGINAL_ID], ' + @columns_list + '
    FROM ' + @raw_tableName + ' r
    CROSS APPLY OPENJSON(r.raw_data) WITH (
        _id nvarchar(155) ''$._id'' STRICT,
        ORIGINAL_ID nvarchar(150) ''$.ORIGINAL_ID'',
        ' + @json_query + '
    ) AS j
';
    BEGIN
        BEGIN TRANSACTION;

        EXECUTE sp_executesql @sqldelete;
        EXECUTE sp_executesql @sqlinsert;

        SELECT @rows_count = @@ROWCOUNT;
        COMMIT;
        END;
END;
GO

数据示例 #

rawACCOUNT表数据：

预期ACCOUNT表结构：

优化方案 #

1. 修复STRING_AGG长度限制，替代游标构建动态SQL #

游标遍历拼接字符串效率极低，改用STRING_AGG时，默认返回类型是NVARCHAR(4000)，超过长度就会报错。解决办法是将聚合结果强制转换为NVARCHAR(MAX)，同时用QUOTENAME处理标识符，避免SQL注入和语法错误：

-- 替换原@columns_list的拼接逻辑
SELECT @columns_list = STRING_AGG(QUOTENAME(column_name), ', ')
FROM dbo.schemaSource
WHERE _enabled = 1 AND table_name = @tableNamep;

-- 替换原游标构建@json_query的逻辑
SELECT @json_query = STRING_AGG(
    CASE
        WHEN single_mult = 'M'
            THEN CONCAT(QUOTENAME(column_name), ' NVARCHAR(MAX) AS JSON')
        ELSE CONCAT(QUOTENAME(column_name), ' NVARCHAR(MAX) ''$.', column_name, '''')
    END,
    ', '
) WITHIN GROUP (ORDER BY column_name) -- 可选：按列名排序保证生成SQL稳定
FROM dbo.schemaSource WITH (NOLOCK)
WHERE _enabled = 1 AND table_name = @tableNamep;

-- 强制转换类型避免长度限制（如果列数极多）
SET @json_query = CAST(@json_query AS NVARCHAR(MAX));

2. 合并删除与插入操作，减少IO开销 #

原逻辑先执行DELETE再执行INSERT，会对目标表进行两次扫描，且DELETE会产生大量事务日志。改用MERGE语句将两个操作合并为一次，大幅减少IO和锁表时间：

-- 替换原@sqldelete和@sqlinsert，构建MERGE语句
SET @sqlmerge = N'
MERGE INTO ' + QUOTENAME(@tableName) + ' AS tgt
USING (
    SELECT j.[_id], j.[ORIGINAL_ID], ' + @columns_list + '
    FROM ' + QUOTENAME(@raw_tableName) + ' r
    CROSS APPLY OPENJSON(r.raw_data) WITH (
        _id nvarchar(155) ''$._id'' STRICT,
        ORIGINAL_ID nvarchar(150) ''$.ORIGINAL_ID'',
        ' + @json_query + '
    ) AS j
) AS src
ON tgt._id COLLATE French_CI_AS = src._id COLLATE French_CI_AS
WHEN MATCHED THEN DELETE -- 匹配到重复行则删除原记录
WHEN NOT MATCHED THEN INSERT (_id, ORIGINAL_ID, ' + @columns_list + ')
    VALUES (src._id, src.ORIGINAL_ID, ' + @columns_list + ');
';

-- 执行MERGE，替代原DELETE+INSERT
BEGIN TRANSACTION;
EXECUTE sp_executesql @sqlmerge;
SELECT @rows_count = @@ROWCOUNT;
COMMIT;

3. 索引优化，提升查询与匹配速度 #

• 原始表：给raw{tableNamep}的raw_id列创建非聚集索引，加速DELETE/MERGE时的匹配查询：

  CREATE NONCLUSTERED INDEX IX_raw_table_raw_id ON dbo.raw{tableNamep}(raw_id);
  

• 目标表：给_id列创建唯一主键或唯一索引，确保重复行匹配的效率：

  ALTER TABLE dbo.{tableName} ADD CONSTRAINT PK_{tableName}_id PRIMARY KEY CLUSTERED (_id);
  -- 或唯一非聚集索引
  CREATE UNIQUE NONCLUSTERED INDEX IX_{tableName}_id ON dbo.{tableName}(_id);
  

• 可选：如果JSON解析频繁，可给raw_data列创建JSON索引，或持久化JSON中的_id计算列并建索引：

  -- 持久化计算列
  ALTER TABLE dbo.raw{tableNamep} ADD json_id AS JSON_VALUE(raw_data, '$.id') PERSISTED;
  CREATE NONCLUSTERED INDEX IX_raw_table_json_id ON dbo.raw{tableNamep}(json_id);
  

4. 批量处理超大规模数据 #

一次性处理全量数据会导致事务过大、日志暴涨、锁表时间过长。可按raw_id范围分批处理，每批次处理固定行数：

-- 在存储过程中添加批量处理逻辑
DECLARE @batchSize INT = 10000;
DECLARE @maxRawId NVARCHAR(155);
DECLARE @minRawId NVARCHAR(155);

SELECT @minRawId = MIN(raw_id), @maxRawId = MAX(raw_id) FROM @raw_tableName;

WHILE @minRawId <= @maxRawId
BEGIN
    DECLARE @currentMaxRawId NVARCHAR(155);
    SELECT TOP (@batchSize) @currentMaxRawId = raw_id FROM @raw_tableName WHERE raw_id > @minRawId ORDER BY raw_id;

    -- 构建批量MERGE语句，只处理当前批次的raw_id
    SET @sqlmerge = N'
    MERGE INTO ' + QUOTENAME(@tableName) + ' AS tgt
    USING (
        SELECT j.[_id], j.[ORIGINAL_ID], ' + @columns_list + '
        FROM ' + QUOTENAME(@raw_tableName) + ' r
        CROSS APPLY OPENJSON(r.raw_data) WITH (
            _id nvarchar(155) ''$._id'' STRICT,
            ORIGINAL_ID nvarchar(150) ''$.ORIGINAL_ID'',
            ' + @json_query + '
        ) AS j
        WHERE r.raw_id BETWEEN ''' + @minRawId + ''' AND ''' + @currentMaxRawId + '''
    ) AS src
    ON tgt._id COLLATE French_CI_AS = src._id COLLATE French_CI_AS
    WHEN MATCHED THEN DELETE
    WHEN NOT MATCHED THEN INSERT (_id, ORIGINAL_ID, ' + @columns_list + ')
        VALUES (src._id, src.ORIGINAL_ID, ' + @columns_list + ');
    ';

    BEGIN TRANSACTION;
    EXECUTE sp_executesql @sqlmerge;
    SELECT @rows_count += @@ROWCOUNT;
    COMMIT;

    SET @minRawId = @currentMaxRawId;
END;

5. OPENJSON解析优化 #

• 指定精确数据类型：避免所有字段都用NVARCHAR(MAX)，根据实际数据类型定义，比如日期、数字类型，减少内存占用和解析时间：

  -- 示例：将OPENING_DATE定义为DATE类型
  OPENJSON(r.raw_data) WITH (
      _id nvarchar(155) ''$._id'' STRICT,
      ORIGINAL_ID nvarchar(150) ''$.ORIGINAL_ID'',
      OPENING_DATE DATE ''$.OPENING_DATE'', -- 精确类型
      CURR_NO INT ''$.CURR_NO'' -- 精确类型
      -- 其他字段同理
  ) AS j
  

• 启用JSON压缩：对raw_data列启用JSON_STORAGE_COMPRESSION，减少磁盘IO开销：

  ALTER TABLE dbo.raw{tableNamep}
  ALTER COLUMN raw_data NVARCHAR(MAX)
  WITH (JSON_STORAGE_COMPRESSION = ON);
  

6. 动态SQL安全与性能优化 #

所有动态拼接的表名、列名都用QUOTENAME()包裹，避免SQL注入和标识符语法错误：

-- 替换原@raw_tableName的赋值
SET @raw_tableName = QUOTENAME('dbo.raw' + @tableNamep);
-- 目标表名也用QUOTENAME
SET @tableName = QUOTENAME(@tableName);

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