存储过程性能优化请求：解决API超时问题

问题概述 #

现有存储过程功能正常，但部分场景下执行耗时超5分钟，导致API调用超时。存储过程输入参数为@Include（BIT类型）和@BeginDate（datetime类型）。

原存储过程代码 #

DECLARE @BeginDate datetime ='2023-09-28'
DECLARE @from datetime
DECLARE @to datetime
DECLARE @Include BIT = 1

SET @from = DATEADD(dd, DATEDIFF(dd, 0, @BeginDate), 0)
SET @to = DATEADD(dd, DATEDIFF(dd, -1, @BeginDate), 0)

IF OBJECT_ID('tempdb..#results') IS NOT NULL 
    DROP TABLE #results

CREATE TABLE #results 
(
    [BPID] [nvarchar](32) NOT NULL,
    [DId] [bigint] NULL,
    [Number] [nvarchar](32) NULL,
    [Account] [nvarchar](32) NULL,
    [LineOfBusinessDesc] [nvarchar](4) NULL,
    [LineOfBusiness] [varchar](35) NULL,
    [Channel] [varchar](35) NULL,
    [ProductLine] [varchar](35) NULL,
    [BusinessSegment] [varchar](35) NULL,
    [LineOfBusinessType] [varchar](35) NULL
)

INSERT INTO #results
    SELECT 
        [PID], [DId], [Number], [Account],
        NULL, NULL, NULL, NULL, NULL
    FROM 
        dbo.DailyDataInfo
    WHERE 
        CreatedDate >= @from 
        AND CreatedDate < @to

IF @Include = 1
BEGIN
    INSERT INTO #results
        SELECT 
            a.[PID], [DId], a.[Number], a.[Account],
            a.[LineOfBusiness], b.[LineOfBusiness],
            b.[Channel], b.[ProductLine], b.[BusinessSegment]
        FROM 
            dbo.OtherDailyData a
        OUTER APPLY 
            (SELECT TOP 1 Id, 
                 x.Data.value('(//Data/Company/LineOfBusiness)[1]', 'varchar(30)') AS LineOfBusiness, 
                 x.Data.value('(//Data/Company/Channel)[1]', 'varchar(30)') AS Channel, 
                 x.Data.value('(//Data/Company/ProductLine)[1]', 'varchar(25)') AS ProductLine, 
                 x.Data.value('(//Data/Company/BusinessSegment)[1]', 'varchar(25)') AS BusinessSegment
             FROM 
                 dbo.TermData x
             WHERE
                 x.Reference = a.Number
             ORDER BY
                 x.Id DESC) b
        WHERE 
            CreatedDate >= @from AND CreatedDate < @to
 END

 SELECT * FROM #results

表结构与索引信息 #

dbo.DailyDataInfo #

• 索引：CreatedDate为非唯一非聚集索引，无其他索引
• 示例数据：

BPID            Id  Number       Account    LineOfBusiness  Createddate
F886A11A6546199 1   9203919023  9203919023  HH              2023-10-02 20:04:00
1802063B1312516 2   9203919031  9203919031  KJ              2023-10-02 20:04:00
a4DEEF472650CB8 3   9203905782  9203905782  KJ              2023-10-02 20:04:00
05D23BE7D263582 4   9203908786  9203908786  HHH             2023-10-02 20:04:00

dbo.OtherDailyData #

• 索引：DId为主键（聚集索引），无其他索引
• 示例数据：

BPId                DId         Number       CreatedDate
9FE25361398013BF    64          9340733736  2023-10-02 20:05:00
20C072C8596503A     68          9340732569  2023-10-02 20:01:00
6526588B6CFC49A     72          9340733502  2023-10-02 20:02:00

dbo.TermData #

• 索引：TermId为主键；Reference为非唯一非聚集索引
• 示例数据：

TermId  Reference  Data
------------------------------------------------------------------
321432  9340703401 <Data><Allow>1</Allow><EligibilityFlag>0</EligibilityFlag><Company><LineOfBusiness>CCC</LineOfBusiness><LineOfBusinessType><LOBType>CommercialAuto</LOBType></LineOfBusinessType><Channel /><ProductLine>Trad</ProductLine><BusinessSegment>E</BusinessSegment></Company><Info><Auditable>0</Auditable></Info></Data>

性能瓶颈分析 #

执行计划显示，dbo.TermData表的查询是核心性能瓶颈：

• 每次查询需实时解析XML字段Data的XPath表达式，单个字段解析占比达12%，多次解析累积开销巨大
• OUTER APPLY结合TOP 1的逻辑，会对OtherDailyData的每一行数据发起一次XML解析查询，重复计算量高

优化建议 #

1. 持久化XML解析结果（核心优化） #

在TermData表中新增持久化计算列，提前解析XML字段并存储，避免每次查询实时解析：

-- 添加持久化计算列
ALTER TABLE dbo.TermData
ADD LineOfBusiness AS Data.value('(Data/Company/LineOfBusiness)[1]', 'varchar(30)') PERSISTED,
    Channel AS Data.value('(Data/Company/Channel)[1]', 'varchar(30)') PERSISTED,
    ProductLine AS Data.value('(Data/Company/ProductLine)[1]', 'varchar(25)') PERSISTED,
    BusinessSegment AS Data.value('(Data/Company/BusinessSegment)[1]', 'varchar(25)') PERSISTED;

-- 创建覆盖索引，避免回表查询
CREATE NONCLUSTERED INDEX IX_TermData_Reference_Includes
ON dbo.TermData (Reference)
INCLUDE (Id, LineOfBusiness, Channel, ProductLine, BusinessSegment);

2. 优化OtherDailyData的查询索引 #

原表仅依赖主键聚集索引，按CreatedDate过滤时需全表扫描，新增非聚集索引减少IO开销：

CREATE NONCLUSTERED INDEX IX_OtherDailyData_CreatedDate_Includes
ON dbo.OtherDailyData (CreatedDate)
INCLUDE (PID, DId, Number, Account, LineOfBusiness);

3. 简化XML解析逻辑（可选） #

若无法新增持久化列，可在查询中一次性解析XML，减少重复解析开销：

-- 修改OUTER APPLY部分
OUTER APPLY 
(
    SELECT TOP 1 
        x.Id,
        xmlData.value('(Data/Company/LineOfBusiness)[1]', 'varchar(30)') AS LineOfBusiness,
        xmlData.value('(Data/Company/Channel)[1]', 'varchar(30)') AS Channel,
        xmlData.value('(Data/Company/ProductLine)[1]', 'varchar(25)') AS ProductLine,
        xmlData.value('(Data/Company/BusinessSegment)[1]', 'varchar(25)') AS BusinessSegment
    FROM dbo.TermData x
    CROSS APPLY (SELECT x.Data AS xmlData) AS tmp
    WHERE x.Reference = a.Number
    ORDER BY x.Id DESC
) b

4. 临时表优化 #

为临时表#results添加合适索引，若后续有查询或合并操作可进一步提升性能：

CREATE NONCLUSTERED INDEX IX_Results_Number ON #results (Number);

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