如何降低SQL Server中FOR JSON PATH的性能影响

DevOps 小满

2026-6-26

问题描述

我们前后端统一用JSON做模型绑定，简化了开发流程，减少了对Entity Framework的依赖。但使用FOR JSON时性能损耗严重，查询约2500条记录需要10-15秒，目前靠分页缓解前端压力。我们不是专业程序员，对SQL了解不多，希望定位查询问题并优化——曾听说有人处理数万条记录的JSON查询能在1秒内完成，这个性能水平是我们能接受的，求优化建议。

优化建议

减少冗余的JSON_QUERY嵌套：很多子查询用JSON_QUERY(SELECT ... FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)的写法完全多余，比如MMCID部分，直接将t1.MMCID和MMCIDForDisplay作为对象字段返回即可，不需要额外嵌套子查询，能减少SQL的解析和JSON生成开销。
替换旧式表连接语法为显式JOIN：当前代码用逗号分隔表的旧式写法容易出现笛卡尔积，改成INNER JOIN/LEFT JOIN明确关联条件，比如RRBaseProgramTBL t4和RRProgramListTBL t5的连接，RRRoleAssignmentMMCIDTBL t6、UsersAccountsTBL t7、UserRoleDefinitionsTBL t8的连接，都要显式写出JOIN ON条件。
替换FORMAT函数：FORMAT函数性能极低，日期格式化建议放到前端处理，或者用CONVERT函数替代，比如CONVERT(varchar, t1.RATING_PERIOD_BEGINS, 101)就能生成MM/dd/yyyy格式的日期字符串。
添加必要索引：
- 给RateReviewBaseTBL的MMCID、REVIEW_TYPE_DD、ACTUARIAL_REVIEW_STATUS_DD、DMCP_REVIEW_STATUS_DD字段创建复合索引，包含排序用的MMCID。
- 给关联表的外键字段建索引：比如RRRoleAssignmentMMCIDTBL的MMCID、USER_ASSIGNMENT、JOB_ASSIGNMENT；ActuarySigningTBL的MMCID；DMCPAdditionalTrackerInformationTBL的MMCID等，加快关联查询速度。
- 给USStateListTBL的STATE_ID建主键或唯一索引，确保STATE_INFORMATION子查询快速匹配。
先分页再关联：当前逻辑是先关联所有表再分页，改成先从RateReviewBaseTBL分页获取目标MMCID列表，再基于这些MMCID关联其他表，能大幅减少需要关联的数据量。
移除不必要的DISTINCT：很多子查询里的DISTINCT是多余的，比如STATE_INFORMATION中的distinct，如果STATE_ID是表的主键，关联后不会产生重复数据，直接去掉即可；其他子查询的distinct也要逐一检查是否真的需要去重。
优化标量函数dbo.A2F_0012_ReturnMMCIDforDisplay：标量函数会逐行调用，性能很差，建议改成内联表值函数，或者直接把函数的逻辑整合到主查询中。

相关SQL代码

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[A2Q_0171_RR_CertificationReviewModel_Browse_JSON](@StartingIndex int, @MaxRecords int)
RETURNS NVARCHAR(MAX)
AS
BEGIN
RETURN
(
select
    (
    select 
        JSON_QUERY ((select 
        --top (@MaxRecords)
            t1.MMCID,
            dbo.A2F_0012_ReturnMMCIDforDisplay(t1.MMCID) as MMCIDForDisplay
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) as MMCID,
        JSON_QUERY ((select distinct
            t2.STATE_ID,
            t2.ENTITY_NAME,
            t2.ENTITY_ABREVIATION,
            --t3.PROGRAM_SHORT_NAME as PROGRAM_CONCATENATE,
                (select distinct
                    t4.PROGRAM_NAME_DD,
                    t5.PROGRAM_NAME,
                    t5.PROGRAM_SHORT_NAME,
                    t5.PROGRAM_ACTIVE
                from
                    dbo.RRBaseProgramTBL as t4,
                    dbo.RRProgramListTBL as t5
                where
                    t2.STATE_ID = t5.STATE_ID
                    AND t1.MMCID = t4.MMCID
                    AND t4.PROGRAM_NAME_DD = t5.PROGRAM_DD
                FOR JSON PATH) as PROGRAM_INFO
            --dbo.A2F_0013_RR_ConcatenateProgramsByMMCID() as t3
            --AND t1.MMCID = t3.MMCID
        from
            dbo.USStateListTBL as t2
        where
            t1.STATE_ID = t2.STATE_ID
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) as STATE_INFORMATION,
        JSON_QUERY ((select 
            t1.OLD_TRACKER_NAME,
            t1.NEW_TRACKER_NAME
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES)) as TRACKER_NAME,
        JSON_QUERY ((select 
            t1.RATING_PERIOD_BEGINS,
            format(t1.RATING_PERIOD_BEGINS, 'MM/dd/yyyy') as RATING_PERIOD_BEGINS_FORMATTED,
            t1.RATING_PERIOD_ENDS,
            format(t1.RATING_PERIOD_ENDS, 'MM/dd/yyyy') as RATING_PERIOD_ENDS_FORMATTED
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) as RATING_PERIOD,
        JSON_QUERY ((select 
            t1.CERTIFICATION_DATE,
            format(t1.CERTIFICATION_DATE, 'MM/dd/yyyy') as CERTIFICATION_DATE_FORMATTED
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) as CERTIFICATION_DATE,
        (select distinct
            t7.FIRST_NAME,
            t7.LAST_NAME,
            t7.INTERNAL_USER_NUMBER,
            t8.User_Role_Description,
            t8.User_Role_Description_abbrev
        from
            dbo.RRRoleAssignmentMMCIDTBL as t6,
            dbo.UsersAccountsTBL as t7,
            dbo.UserRoleDefinitionsTBL as t8
        where
            t1.MMCID = t6.MMCID
            AND (t6.JOB_ASSIGNMENT = 1 OR t6.JOB_ASSIGNMENT = 2 OR t6.JOB_ASSIGNMENT = 5)
            AND t6.USER_ASSIGNMENT = t7.INTERNAL_USER_NUMBER
            AND t6.JOB_ASSIGNMENT = t8.User_Role_Number_DD
        FOR JSON PATH, INCLUDE_NULL_VALUES) as RATE_REVIEWERS,
        JSON_QUERY((select distinct
            --t9.Signing_Actuary as SIGNING_ACTUARY_CONCATENATED,
            t9.ACTUARIAL_FIRM_DD,
            t10.FIRM_NAME
        from
            dbo.ActuarySigningTBL as t9,
            dbo.ActuarialFirmTBL as t10
        where
            t1.MMCID = t9.MMCID
            AND t9.ACTUARIAL_FIRM_DD = t10.FIRM_DD_NUMBER
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES)) as ACTUARIAL_FIRM,
        (select
            (t11.FIRST_NAME + ' ' + t11.LAST_NAME) as NAME,
            t11.INTERNAL_USER_NUMBER
        from
            dbo.ActuarySigningTBL as t16,
            dbo.UsersAccountsTBL as t11
        where
            t1.MMCID = t16.MMCID
            AND t16.SIGNING_ACTUARY_DD = t11.INTERNAL_USER_NUMBER
        FOR JSON PATH, INCLUDE_NULL_VALUES) as SIGNING_ACTUARY,
        JSON_QUERY((select
            t1.REVIEW_TYPE_DD,
            t12.ReviewTypeDescription
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) as REVIEW_TYPE,
        JSON_QUERY((select 
            t13.STATE_SUBMISSION_DATE,
            format(t13.STATE_SUBMISSION_DATE, 'MM/dd/yyyy') as STATE_SUBMISSION_DATE_FORMATTED,
            t13.DMCP_SUBMISSION_DATE,
            format(t13.DMCP_SUBMISSION_DATE, 'MM/dd/yyyy') as DMCP_SUBMISSION_DATE_FORMATTED
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES)) as SUBMISSION_DATE,
        JSON_QUERY((select 
            t1.ACTUARIAL_REVIEW_STATUS_DD,
            t14.DISPLAY,
            t13.OACT_MEMO_DATE,
            format(t13.OACT_MEMO_DATE, 'MM/dd/yyyy') as OACT_MEMO_DATE_FORMATTED
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES)) as OACT_REVIEW_STATUS,
        JSON_QUERY((select
            t1.DMCP_REVIEW_STATUS_DD,
            t15.DISPLAY,
            t13.DMCP_RECOMMENDATION_DATE,
            format(t13.DMCP_RECOMMENDATION_DATE, 'MM/dd/yyyy') as DMCP_RECOMMENDATION_DATE_FORMATTED,
            t13.DMCP_RECOMMENDATION_COMMENT
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES)) as DMCP_REVIEW_STATUS,
        JSON_QUERY((select
            t13.RISK_MITIGATION,
            t13.RATE_RANGES,
            t13.REVISED_CERT,
            t13.REVISED_CERT_DATE,
            format(t13.REVISED_CERT_DATE, 'MM/dd/yyyy') as REVISED_CERT_DATE_FORMATTED
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES)) as DMCP_ADDITIONAL_INFO
    from
        dbo.RateReviewBaseTBL as t1,
        dbo.RRReviewTypeTBL as t12,
        dbo.DMCPAdditionalTrackerInformationTBL as t13,
        dbo.ActuarialReviewStatusDisplay_TBL as t14,
        dbo.RRDMCPReviewStatusTBL as t15
    where
        t1.REVIEW_TYPE_DD = t12.ReviewTypeDD
        AND t1.MMCID = t13.MMCID
        AND t1.ACTUARIAL_REVIEW_STATUS_DD = t14.ACTUARIAL_REVIEW_STATUS_DD
        AND t1.DMCP_REVIEW_STATUS_DD = t15.DMCP_REVIEW_STATUS_DD
        --AND t1.MMCID >= (select max(t16.MMCID) from
        --              (select top (@StartingIndex) MMCID
        --              from dbo.RateReviewBaseTBL
        --              order by MMCID) as t16)
    order by t1.MMCID
    OFFSET (@StartingIndex - 1) ROWS
    FETCH NEXT @MaxRecords ROWS ONLY
    FOR JSON PATH, INCLUDE_NULL_VALUES
    ) as DATA,
    @StartingIndex as STARTING_INDEX,
    (select count(MMCID) from dbo.RateReviewBaseTBL) as TOTAL_ROWS,
    @MaxRecords as MAX_RECORDS_PER_QUERY
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES
)
END
GO

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