为提升性能，如何将Cross Apply转换为Pivot？

问题背景 #

原查询在数据量较小时运行正常，但数据规模扩大后，Cross Apply dbo.Sundown_func_Get_HTML_Table_Results_test(P.prno_id)成为性能瓶颈——单独执行该表值函数（TVF）耗时0ms，但结合Cross Apply处理170/280条数据均耗时18秒。尝试索引优化无效，需用Pivot替代Cross Apply实现性能提升。

原查询代码：

SELECT 
    P.prno_id
    ,C.client_id as [ClientID]
    ,E.enco_id as [Encounter]
    ,C.fname + ' ' + C.lname as [Patient]
    ,PL.prle_listname as [Program]
    ,t.PreDate as [RXDate]
    ,t.RxNumber as [RxNumber] 
    ,CASE WHEN CHARINDEX(t.RxPrice, '$') = 0 THEN '$' ELSE '' END + t.RxPrice as [Price]
    ,t.RXNotes as [Notes]
    ,t.RxStatus as [Status]
FROM Progress_Note P
JOIN Encounter E 
    on E.enco_id = P.enco_id
JOIN client_program CP 
    on CP.clpr_id = dbo.Sundown_func_Get_Admission_Program_By_Encounter(E.enco_id)
JOIN PROGRAM_LEVEL PL 
    on PL.prle_id = CP.prle_id
JOIN Client 
    C on C.client_id = P.client_id
Cross Apply dbo.Sundown_func_Get_HTML_Table_Results_test(P.prno_id)  as t

优化思路 #

性能瓶颈的核心是逐行调用TVF：SQL Server会对每个prno_id单独执行一次函数，导致大量重复计算开销。用Pivot替代的关键是把TVF内部逻辑拆解出来，改成批量处理所有数据，再通过Pivot完成行转列（或对应的数据转换），彻底避免逐行调用的额外开销。

具体实现步骤 #

1. 拆解TVF内部逻辑 #

首先明确dbo.Sundown_func_Get_HTML_Table_Results_test的实际逻辑——从命名判断，它应该是解析prno_id对应的HTML表格，提取PreDate、RxNumber等字段。我们需要将这个解析逻辑直接整合到主查询中，而非通过函数调用。

2. 批量解析+Pivot转列 #

以下是基于HTML解析场景的替代代码（需根据TVF实际逻辑调整解析部分）：

WITH ParsedRxData AS (
    -- 批量解析所有Progress_Note的HTML数据，提取键值对行
    SELECT 
        P.prno_id,
        -- 替换为TVF内部解析HTML的实际逻辑，此处以XML解析表格为例
        TRIM(x.value('(td/text())[1]', 'nvarchar(100)')) AS RxKey,
        TRIM(x.value('(td/text())[2]', 'nvarchar(200)')) AS RxValue
    FROM Progress_Note P
    -- 将HTML表格转换为XML格式以便解析（需根据实际HTML结构调整）
    CROSS APPLY (SELECT CAST('<root>' + REPLACE(REPLACE(P.html_data, '<tr>', '</tr><tr>'), '</table>', '</tr></root>') AS xml)) AS xmldoc
    CROSS APPLY xmldoc.nodes('root/tr') AS n(x)
    -- 只保留需要提取的字段对应的行
    WHERE x.value('(td/text())[1]', 'nvarchar(100)') IN ('PreDate', 'RxNumber', 'RxPrice', 'RXNotes', 'RxStatus')
),
PivotedRxData AS (
    -- 用Pivot逻辑将键值对行转成列，匹配原TVF的输出结构
    SELECT 
        prno_id,
        MAX(CASE WHEN RxKey = 'PreDate' THEN RxValue END) AS PreDate,
        MAX(CASE WHEN RxKey = 'RxNumber' THEN RxValue END) AS RxNumber,
        MAX(CASE WHEN RxKey = 'RxPrice' THEN RxValue END) AS RxPrice,
        MAX(CASE WHEN RxKey = 'RXNotes' THEN RxValue END) AS RXNotes,
        MAX(CASE WHEN RxKey = 'RxStatus' THEN RxValue END) AS RxStatus
    FROM ParsedRxData
    GROUP BY prno_id
)
-- 主查询关联批量处理后的Pivot结果，替代原Cross Apply
SELECT 
    P.prno_id,
    C.client_id as [ClientID],
    E.enco_id as [Encounter],
    C.fname + ' ' + C.lname as [Patient],
    PL.prle_listname as [Program],
    t.PreDate as [RXDate],
    t.RxNumber as [RxNumber],
    CASE WHEN CHARINDEX(t.RxPrice, '$') = 0 THEN '$' ELSE '' END + t.RxPrice as [Price],
    t.RXNotes as [Notes],
    t.RxStatus as [Status]
FROM Progress_Note P
JOIN Encounter E ON E.enco_id = P.enco_id
JOIN client_program CP ON CP.clpr_id = dbo.Sundown_func_Get_Admission_Program_By_Encounter(E.enco_id)
JOIN PROGRAM_LEVEL PL ON PL.prle_id = CP.prle_id
JOIN Client C ON C.client_id = P.client_id
JOIN PivotedRxData t ON t.prno_id = P.prno_id;

3. 关键注意事项 #

• 如果TVF不是解析HTML，而是其他计算/转换逻辑，只需把ParsedRxData中的解析代码替换为对应的批量计算逻辑即可。
• 若原TVF是多语句表值函数（MS TVF），这种批量处理方式能彻底消除逐行调用的开销；即使是内联表值函数（ITVF），展开逻辑后也能让SQL Server生成更优的执行计划。
• 需根据实际数据结构调整XML解析或字段提取逻辑，确保输出结果与原TVF完全一致。

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