解决方法:CTE 因为一个字段而运行缓慢?

Question

首先，这是我问的第一个问题。现在，查询突然需要 3+ 分钟才能运行。如果我删除底部的最后一个 case 语句(FROM tau 正上方(，它实际上只需 1 秒即可运行。

我已经重写了我的标量 UDF 以改用表值函数。这最初提高了性能。然后，我在表上创建了索引。老实说，我不擅长阅读执行计划，所以我把它包括在下面。

实际执行计划

法典：

-- Normalized Effectiveness score = function(Effectiveness) {CASE WHEN ??? THEN Normalized Effectiveness Score}
-- Effectiveness = (Target AHT * # Calls Handled)/Total Handle Time; 
-- Weight = Eligible Calls Handled Per Split / Total Eligible Calls 
-- (calls from certain skill groups are ineligible)
-- split = call reason/call type
-- Ultimate goal is to arrive at a table with one row per advocate, with a Normalized Effectiveness Score (and all component parts) for each advocate
WITH agent_split_stats AS ( 
Select
    racf,
    agent_stats.SkillTargetId,
    agent_stats.SkillGroupSkillTargetID,
    aht_target.EnterpriseName,
    aht_target.target,
    Sum(agent_stats.CallsHandled) as n_calls_handled,
    CASE WHEN (Sum(agent_stats.TalkInTime) + Sum(agent_stats.IncomingCallsOnHoldTime) + Sum(agent_stats.WorkReadyTime)) = 0 THEN 1 ELSE
        (Sum(agent_stats.TalkInTime) + Sum(agent_stats.IncomingCallsOnHoldTime) + Sum(agent_stats.WorkReadyTime))END
    AS total_handle_time
from tblAceyusAgntSklGrp as agent_stats
-- GET TARGETS
INNER JOIN tblCrosswalkWghtPhnEffTarget as aht_target
  ON aht_target.SgId = agent_stats.SkillGroupSkillTargetID
  AND agent_stats.DateTime BETWEEN aht_target.StartDt and aht_target.EndDt
-- GET RACF 
INNER JOIN tblAgentMetricCrosswalk as xwalk
  ON xwalk.SkillTargetID = agent_stats.SkillTargetID
--GET TAU DATA LIKE START DATE AND GRADUATED FLAG
INNER JOIN tblTauClassList AS T
  ON T.SaRacf = racf
WHERE DateTime >= TauStart
  AND DateTime <= TauEnd
  --AND Graduated = 'No'
--WPE FILTERS TO ENSURE ACCURATE DATA
  AND CallsHandled <> 0
  AND Target is not null
Group By
  racf, agent_stats.SkillTargetId, agent_stats.SkillGroupSkillTargetID, aht_target.EnterpriseName, aht_target.target
),
agent_split_stats_with_weight AS (
-- calculate weights
-- one row = one advocate + split
SELECT 
    agent_split_stats.*,
    agent_split_stats.n_calls_handled/SUM(agent_split_stats.n_calls_handled) OVER(PARTITION BY agent_split_stats.skilltargetid) AS [weight]
FROM agent_split_stats
),
agent_split_effectiveness AS (
-- calculate the raw Effectiveness score for each eligible advocate/split
-- one row = one agent + split, with their raw Effectiveness score and the components of that
SELECT 
    agent_split_stats_with_weight.*,
    -- these are the components of the Effectiveness score
    (((agent_split_stats_with_weight.target * agent_split_stats_with_weight.n_calls_handled) / agent_split_stats_with_weight.total_handle_time)*100)*agent_split_stats_with_weight.weight AS effectiveness_sum
FROM agent_split_stats_with_weight
), -- this is where we show effectiveness per split  select * from agent_split_effectiveness
agent_effectiveness AS (
-- sum all of the individual effectiveness raw scores for each agent to get each agent's raw score
SELECT 
    racf AS SaRacf,
    ROUND(SUM(effectiveness_sum),2) AS WpeScore
FROM agent_split_effectiveness
GROUP BY racf, skilltargetid
),
--GET FULL CLASS LIST AND TAU DATES
tau AS (
Select ID, SaRacf, FacilitatorRacf, TauSupvRacf, ClassType, TrainerType, TauStart AS TauStartDate, TauEnd, Graduated, MetGoalDate, TauYear, TermDate 
FROM tblTauClassList
)
--JOIN ALL DATA TOGETHER
SELECT tau.*, 
CASE WHEN WpeScore IS NULL   THEN (SELECT WpeNullScore FROM tblAvs1FltrScr WHERE WpeNullScore IS NOT NULL)
    ELSE WpeScore END
AS WpeScore
FROM tau
LEFT JOIN agent_effectiveness
  ON agent_effectiveness.SaRacf = tau.SaRacf
ORDER BY SaRacf

期望查询运行 <5 秒并提供结果，无论某人是否有"WpeScore"。如果他们没有，我有一个默认值入。

它可能是错误的索引，我在其他地方使用了大部分代码，并且它运行得很快。在这种情况下，它只是在这个部分完全蹲碎了床：

CASE WHEN WpeScore IS NULL   THEN (SELECT WpeNullScore FROM tblAvs1FltrScr WHERE WpeNullScore IS NOT NULL)
    ELSE WpeScore END
AS WpeScore

Answer 1

将所有

CTE 替换为临时表应该可以加快查询速度。

-- Normalized Effectiveness score = function(Effectiveness) {CASE WHEN ??? THEN Normalized Effectiveness Score}
-- Effectiveness = (Target AHT * # Calls Handled)/Total Handle Time; 
-- Weight = Eligible Calls Handled Per Split / Total Eligible Calls 
-- (calls from certain skill groups are ineligible)
-- split = call reason/call type
-- Ultimate goal is to arrive at a table with one row per advocate, with a Normalized Effectiveness Score (and all component parts) for each advocate

SELECT
        racf
       ,agent_stats.SkillTargetId
       ,agent_stats.SkillGroupSkillTargetID
       ,aht_target.EnterpriseName
       ,aht_target.target
       ,SUM(agent_stats.CallsHandled) AS n_calls_handled
       ,CASE
            WHEN (SUM(agent_stats.TalkInTime) + SUM(agent_stats.IncomingCallsOnHoldTime) + SUM(agent_stats.WorkReadyTime)) = 0
                THEN 1
            ELSE (SUM(agent_stats.TalkInTime) + SUM(agent_stats.IncomingCallsOnHoldTime) + SUM(agent_stats.WorkReadyTime))
        END                           
        AS total_handle_time
    INTO #agent_split_stats
    FROM tblAceyusAgntSklGrp AS agent_stats
    -- GET TARGETS
    INNER JOIN tblCrosswalkWghtPhnEffTarget AS aht_target
        ON aht_target.SgId = agent_stats.SkillGroupSkillTargetID
            AND agent_stats.DATETIME BETWEEN aht_target.StartDt AND aht_target.EndDt
    -- GET RACF 
    INNER JOIN tblAgentMetricCrosswalk AS xwalk
        ON xwalk.SkillTargetID = agent_stats.SkillTargetID
    --GET TAU DATA LIKE START DATE AND GRADUATED FLAG
    INNER JOIN tblTauClassList AS T
        ON T.SaRacf = racf
    WHERE DATETIME >= TauStart
        AND DATETIME <= TauEnd
        --AND Graduated = 'No'
        --WPE FILTERS TO ENSURE ACCURATE DATA
        AND CallsHandled <> 0
        AND Target IS NOT NULL
    GROUP BY
        racf
       ,agent_stats.SkillTargetId
       ,agent_stats.SkillGroupSkillTargetID
       ,aht_target.EnterpriseName
       ,aht_target.target
-- calculate weights
-- one row = one advocate + split
SELECT
        #agent_split_stats.*
       ,#agent_split_stats.n_calls_handled
        / SUM(#agent_split_stats.n_calls_handled)
        OVER (PARTITION BY #agent_split_stats.skilltargetid) AS [weight]
    INTO #agent_split_stats_with_weight
    FROM #agent_split_stats
-- calculate the raw Effectiveness score for each eligible advocate/split
-- one row = one agent + split, with their raw Effectiveness score and the components of that
SELECT
        #agent_split_stats_with_weight.*
       ,
        -- these are the components of the Effectiveness score
        (((#agent_split_stats_with_weight.target
        * #agent_split_stats_with_weight.n_calls_handled
        ) / #agent_split_stats_with_weight.total_handle_time) * 100)
        * #agent_split_stats_with_weight.weight AS effectiveness_sum
    INTO #agent_split_effectiveness
    FROM #agent_split_stats_with_weight
-- sum all of the individual effectiveness raw scores for each agent to get each agent's raw score
SELECT
        racf                             AS SaRacf
       ,ROUND(SUM(effectiveness_sum), 2) AS WpeScore
    INTO #agent_effectiveness
    FROM #agent_split_effectiveness
    GROUP BY
        racf
       ,skilltargetid
--GET FULL CLASS LIST AND TAU DATES
SELECT
        id
       ,SaRacf
       ,FacilitatorRacf
       ,TauSupvRacf
       ,ClassType
       ,TrainerType
       ,TauStart AS TauStartDate
       ,TauEnd
       ,Graduated
       ,MetGoalDate
       ,TauYear
       ,TermDate
    INTO #tau
    FROM tblTauClassList
--JOIN ALL DATA TOGETHER
SELECT
        tau.id
       ,tau.SaRacf
       ,tau.FacilitatorRacf
       ,tau.TauSupvRacf
       ,tau.ClassType
       ,tau.TrainerType
       ,tau.TauStartDate
       ,tau.TauEnd
       ,tau.Graduated
       ,tau.MetGoalDate
       ,tau.TauYear
       ,tau.TermDate
       ,ISNULL(WpeScore, ns.WpeNullScore) AS WpeScore
    FROM #tau
    LEFT JOIN #agent_effectiveness
        ON #agent_effectiveness.SaRacf = tau.SaRacf
    CROSS APPLY (
        SELECT TOP 1
            WpeNullScore
        FROM tblAvs1FltrScr
        WHERE WpeNullScore IS NOT NULL
    ) ns
    ORDER BY
        SaRacf

Answer 2

如果更改以下内容会发生什么：

SELECT tau.*, 
CASE WHEN WpeScore IS NULL   THEN (SELECT WpeNullScore FROM tblAvs1FltrScr WHERE WpeNullScore IS NOT NULL)
    ELSE WpeScore END
AS WpeScore
FROM tau
LEFT JOIN agent_effectiveness  ON agent_effectiveness.SaRacf = tau.SaRacf
ORDER BY SaRacf

自：

SELECT tau.*, 
CASE WHEN WpeScore IS NULL   THEN (SELECT top 1 WpeNullScore FROM tblAvs1FltrScr WHERE WpeNullScore IS NOT NULL)
    ELSE WpeScore END
AS WpeScore
FROM tau
LEFT JOIN agent_effectiveness  ON agent_effectiveness.SaRacf = tau.SaRacf
ORDER BY SaRacf

编辑：这工作得更好吗？

SELECT 
    tau.*, 
    CASE 
        WHEN WpeScore IS NULL THEN ns.WpeNullScore
        ELSE WpeScore 
    END AS WpeScore
FROM tau
LEFT JOIN agent_effectiveness ON agent_effectiveness.SaRacf = tau.SaRacf
cross apply (
    SELECT top 1 WpeNullScore FROM tblAvs1FltrScr WHERE WpeNullScore IS NOT NULL
) ns
ORDER BY SaRacf