存储过程优化：Outer Apply替代方案及查询性能优化咨询

咱们先拆解下原查询里的核心问题：OUTER APPLY的冗余关联、多次重复JOIN同一张表导致的IO过载（尤其是Valet Parking关联超时的根源），然后一步步给出优化方案。

一、OUTER APPLY的替代方案 #

原OUTER APPLY的逻辑是给每个ProgramID取第一个符合条件的次要演讲者，但里面存在冗余关联——INNER JOIN Tran_SpeakerProgram TSP完全没必要，因为已经通过TSC.ProgramID = SP.ProgramID关联到主表了，这个额外的关联只会增加查询开销。

推荐用**LEFT JOIN + 窗口函数ROW_NUMBER()**替代，既保留原逻辑，又能避免APPLY逐行处理的潜在性能瓶颈：

LEFT JOIN (
    SELECT 
        TSC.ProgramID,
        ms.FirstName AS SecondarySpeakerFirstName,
        ms.LastName AS SecondarySpeakerLastName,
        TSC.SpeakerID AS SecondarySpeakerId,
        -- 按业务需求排序取第一个次要演讲者，这里用SpeakerID示例，你可以调整排序字段
        ROW_NUMBER() OVER (PARTITION BY TSC.ProgramID ORDER BY TSC.SpeakerID) AS rn
    FROM Tran_ScheduleProgramSpeaker TSC WITH(NOLOCK)
    INNER JOIN Mst_Speaker ms ON TSC.SpeakerID = ms.SpeakerID
    WHERE TSC.IsActive=1 AND TSC.IsDeleted=0
) SECP ON SP.ProgramID = SECP.ProgramID 
    AND SECP.rn = 1
    AND SECP.SecondarySpeakerId != SP.PrimarySpeakerID

二、整体查询的核心优化：合并重复的同表LEFT JOIN #

原查询多次LEFT JOIN Tran_SpeakerExpenses和Tran_SpeakerProgramExpenses（每个类型的费用单独JOIN一次），这种写法会重复扫描同一张表，极大增加IO开销，也是Valet Parking关联超时的主要原因。我们可以用条件聚合合并这些关联，只访问表一次。

1. 合并Tran_SpeakerExpenses的多次关联 #

把多个类型的费用查询合并到一个子查询中，用CASE WHEN获取对应类型的记录：

LEFT JOIN (
    SELECT 
        SpeakerID,
        ProgramID,
        MAX(CASE WHEN TravelLogistic = 'Honoraria' THEN SpeakerExpensesID END) AS HonorariaID,
        MAX(CASE WHEN TravelLogistic = 'Airfare' THEN SpeakerExpensesID END) AS AirfareID,
        MAX(CASE WHEN TravelLogistic = 'Lodging' THEN SpeakerExpensesID END) AS LodgingID,
        MAX(CASE WHEN TravelLogistic = 'Ground Transportation' THEN SpeakerExpensesID END) AS GroundTransID,
        MAX(CASE WHEN TravelLogistic = 'Other' THEN SpeakerExpensesID END) AS OtherID
    FROM Tran_SpeakerExpenses WITH(NOLOCK)
    WHERE HonorariumType = 'A' AND IsActive = 1
    GROUP BY SpeakerID, ProgramID
) TSE_AGG ON SECP.SecondarySpeakerId = TSE_AGG.SpeakerID AND SP.ProgramID = TSE_AGG.ProgramID

2. 合并Tran_SpeakerProgramExpenses的多次关联 #

同样用条件聚合解决Valet Parking的超时问题，只访问一次表就能拿到所有类型的费用记录：

LEFT JOIN (
    SELECT 
        ProgramID,
        MAX(CASE WHEN ProgramExpenses = 'Audio/Visual' THEN ProgramExpensesID END) AS AV_ID,
        MAX(CASE WHEN ProgramExpenses = 'Deposit' THEN ProgramExpensesID END) AS Deposit_ID,
        MAX(CASE WHEN ProgramExpenses = 'Venue Rental' THEN ProgramExpensesID END) AS Venue_ID,
        MAX(CASE WHEN ProgramExpenses = 'Wi-Fi' THEN ProgramExpensesID END) AS Wifi_ID,
        MAX(CASE WHEN ProgramExpenses = 'Valet Parking' THEN ProgramExpensesID END) AS Valet_ID
    FROM Tran_SpeakerProgramExpenses WITH(NOLOCK)
    WHERE ProgramExpensesType = 'A' AND IsActive = 1
    GROUP BY ProgramID
) TSP_EXP_AGG ON SP.ProgramID = TSP_EXP_AGG.ProgramID

三、完整优化后的SQL #

SELECT DISTINCT 
    SP.ProgramID AS [ProgramID],
    -- 按需添加次要演讲者的字段
    SECP.SecondarySpeakerFirstName,
    SECP.SecondarySpeakerLastName,
    SECP.SecondarySpeakerId,
    -- 按需添加费用相关字段，比如对账记录
    TRS.ReconcileID
FROM Tran_SpeakerProgram SP WITH(NOLOCK) 
-- 替代原OUTER APPLY的LEFT JOIN + 窗口函数
LEFT JOIN (
    SELECT 
        TSC.ProgramID,
        ms.FirstName AS SecondarySpeakerFirstName,
        ms.LastName AS SecondarySpeakerLastName,
        TSC.SpeakerID AS SecondarySpeakerId,
        ROW_NUMBER() OVER (PARTITION BY TSC.ProgramID ORDER BY TSC.SpeakerID) AS rn
    FROM Tran_ScheduleProgramSpeaker TSC WITH(NOLOCK)
    INNER JOIN Mst_Speaker ms ON TSC.SpeakerID = ms.SpeakerID
    WHERE TSC.IsActive=1 AND TSC.IsDeleted=0
) SECP ON SP.ProgramID = SECP.ProgramID 
    AND SECP.rn = 1
    AND SECP.SecondarySpeakerId != SP.PrimarySpeakerID
-- 合并Tran_SpeakerExpenses的多次关联
LEFT JOIN (
    SELECT 
        SpeakerID,
        ProgramID,
        MAX(CASE WHEN TravelLogistic = 'Honoraria' THEN SpeakerExpensesID END) AS HonorariaID,
        MAX(CASE WHEN TravelLogistic = 'Airfare' THEN SpeakerExpensesID END) AS AirfareID,
        MAX(CASE WHEN TravelLogistic = 'Lodging' THEN SpeakerExpensesID END) AS LodgingID,
        MAX(CASE WHEN TravelLogistic = 'Ground Transportation' THEN SpeakerExpensesID END) AS GroundTransID,
        MAX(CASE WHEN TravelLogistic = 'Other' THEN SpeakerExpensesID END) AS OtherID
    FROM Tran_SpeakerExpenses WITH(NOLOCK)
    WHERE HonorariumType = 'A' AND IsActive = 1
    GROUP BY SpeakerID, ProgramID
) TSE_AGG ON SECP.SecondarySpeakerId = TSE_AGG.SpeakerID AND SP.ProgramID = TSE_AGG.ProgramID
-- 关联对账表
LEFT JOIN Tran_ReconcileSpeakerExpenses TRS WITH(NOLOCK) ON TSE_AGG.HonorariaID = TRS.SpeakerExpensesID 
    AND TRS.IsActive = 1 AND TRS.IsDeleted = 0
-- 合并Tran_SpeakerProgramExpenses的多次关联
LEFT JOIN (
    SELECT 
        ProgramID,
        MAX(CASE WHEN ProgramExpenses = 'Audio/Visual' THEN ProgramExpensesID END) AS AV_ID,
        MAX(CASE WHEN ProgramExpenses = 'Deposit' THEN ProgramExpensesID END) AS Deposit_ID,
        MAX(CASE WHEN ProgramExpenses = 'Venue Rental' THEN ProgramExpensesID END) AS Venue_ID,
        MAX(CASE WHEN ProgramExpenses = 'Wi-Fi' THEN ProgramExpensesID END) AS Wifi_ID,
        MAX(CASE WHEN ProgramExpenses = 'Valet Parking' THEN ProgramExpensesID END) AS Valet_ID
    FROM Tran_SpeakerProgramExpenses WITH(NOLOCK)
    WHERE ProgramExpensesType = 'A' AND IsActive = 1
    GROUP BY ProgramID
) TSP_EXP_AGG ON SP.ProgramID = TSP_EXP_AGG.ProgramID
ORDER BY SP.ProgramID;

四、额外优化建议 #

• 索引优化：给以下字段添加非聚集索引（包含查询需要的字段），进一步提升性能：
  • Tran_ScheduleProgramSpeaker：(ProgramID, IsActive, IsDeleted)包含SpeakerID
  • Tran_SpeakerExpenses：(SpeakerID, ProgramID, HonorariumType, IsActive)包含TravelLogistic, SpeakerExpensesID
  • Tran_SpeakerProgramExpenses：(ProgramID, ProgramExpensesType, IsActive)包含ProgramExpenses, ProgramExpensesID
• 去掉不必要的DISTINCT：如果优化后的查询逻辑不会产生重复行，可以移除SELECT DISTINCT，减少排序开销。
• NOLOCK的使用：确认业务允许脏读的情况下再使用WITH(NOLOCK)，避免数据一致性问题。

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