How to Fix: CTE Running slow because of one field?

Question

First, here is the initial question I asked. Now suddenly the query is taking 3+ minutes to run. If I remove the final case statement at the bottom (right above FROM tau) it runs in literally 1 second.

I have rewritten my Scalar UDF to use table valued functions instead. This initially increased performance. I then created indexes on the tables. Honestly, I am not great at reading the execution plan, so I have included it below.

Actual Execution Plan

CODE:

-- 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

Expecting the query to run <5 seconds and provide a result regardless if a person has a "WpeScore" or not. If they do not, i have a default value that gets slotted in.

It could be bad indexes, I use the bulk of this code elsewhere and it runs failrly quickly. In this instance its just totally crapping the bed on this part:

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

SSMS , sorry the tags are Sql but I did not specify my interface. My apologies. — cdscivic, Aug 07 '19 at 14:58

score 1 · Answer 1 · answered Aug 07 '19 at 18:14

Replace all CTE with temp table should speed up your query.

-- 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

So i added this at the top: `DROP TABLE IF EXISTS #agent_split_stats DROP TABLE IF EXISTS #agent_split_stats.n_calls_handle DROP TABLE IF EXISTS #agent_split_stats_with_weight DROP TABLE IF EXISTS #agent_split_effectiveness DROP TABLE IF EXISTS #agent_effectiveness DROP TABLE IF EXISTS #tau` But why is that faster than a CTE? — cdscivic, Aug 08 '19 at 11:28
CTE is just a syntax sugar, it has no differents than a sub-select query, which means with a complex query using many CTE, all I/O will be performed on the original table. A temp table is a real table in 'tempdb', so all the aggregated temporary values are calculated only once then stored in the temp table, resulting much less I/O and computing cost. — Adam Yan, Aug 08 '19 at 13:58
I thought the CTE forced order of operations, that is why I have been using them. The difference is substantial, 4 minutes vs 2 seconds. — cdscivic, Aug 08 '19 at 14:26
@cdscivic Great to hear that. Please consider [accepting the answer](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work). :) — Adam Yan, Aug 08 '19 at 21:08
Accepted this as the answer, but i am still not 100% sure on the performance drop from the original CTE vs temp table. — cdscivic, Aug 08 '19 at 21:11
You can check Martin Smith's anwser [here](https://stackoverflow.com/questions/690465/which-are-more-performant-cte-or-temporary-tables) for more explaination about CTE vs Temp Table, which is the most persuasive for me so far. — Adam Yan, Aug 08 '19 at 21:24
Plot twist, i am not able to use TempTables in a Sql View. Any other suggestions? — cdscivic, Aug 12 '19 at 16:54
The easiest way is to materialize the view to a real table if you don't need real time data. — Adam Yan, Aug 13 '19 at 15:35

Chris Hackett · Answer 2 · 2019-08-07T16:54:55.623

What happens if you change:

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

to:

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

EDIT: Does this work any better?

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

Same run time. Tmp tables below were fast as heck though. Trying to see why, it's weird. — cdscivic, Aug 08 '19 at 12:00

How to Fix: CTE Running slow because of one field?

2 Answers2