Need help - Query with performance issues

[roberteliasson]

Hi all,

I'm having a report which need serious improvements as its taking such a long time to run.

I believe that the reason of this is because I need to calculate how many calls we have coded with a wrapup code, divided by how many calls we have (both inbound and outbound) - see TCD.CallsCoded

Does anyone have a clue on what I can improve with the query below in order to make it a bit faster? ANY improvement is greatly appreciated!

Thanks in advance!

SELECT

Convert(date, :startDate)  as WeekCommencing,

Agent_Team.EnterpriseName as TeamName,

Agent_Team.EnterpriseName as TeamName2,

(Person.FirstName + ' ' + Person.LastName) as FullName,

SUM(ISNULL(AI.LoggedOnTime,0)-ISNULL(AED.LunchTime,0) * 1.0) as LoggedOnTime,

SUM(ISNULL(ASGI.CallsAnswered,0)) as CallsAnswered,

SUM(ISNULL(ASGI2.AgentOutCalls,0)) as AgentOutCalls,

SUM(ISNULL(TCD.CallsCoded,0))+SUM(ISNULL(TCD.Trans,0))+SUM(ISNULL(TCD.NoAns,0)) as CallsCoded,

(SUM(ISNULL(TCD.CallsCoded,0))+SUM(ISNULL(TCD.Trans,0))+SUM(ISNULL(TCD.NoAns,0)) * 1.0) / (SUM(ISNULL(ASGI.CallsAnswered,0) * 1.0) + SUM(ISNULL(ASGI2.AgentOutCalls,0) * 1.0) * 1.0) as PerCoded,

(SUM(ISNULL(ASGI.WorkNotReadyTime ,0) * 1.0) + SUM(ISNULL(ASGI.WorkReadyTime ,0) * 1.0) + SUM(ISNULL(ASGI.ReservedStateTime,0) * 1.0) + SUM(ISNULL(AI.AvailTime,0) * 1.0) + SUM(ISNULL(ASGI.TalkInTime,0) * 1.0) + SUM(ISNULL(ASGI.HoldTime,0) * 1.0) + SUM(ISNULL(AED.AfterCallWork, 0) * 1.0) + SUM(ISNULL(ASGI2.TalkOutTime,0) * 1.0) + SUM(ISNULL(AED.OutboundCall,0) * 1.0))    /    SUM(ISNULL(AI.LoggedOnTime,0)-ISNULL(AED.LunchTime,0) * 1.0) as PerReady,

(SUM(ISNULL(ASGI.WorkNotReadyTime ,0) * 1.0) + SUM(ISNULL(ASGI.WorkReadyTime ,0) * 1.0) + SUM(ISNULL(ASGI.ReservedStateTime,0) * 1.0) + SUM(ISNULL(AI.AvailTime,0) * 1.0) + SUM(ISNULL(ASGI.TalkInTime,0) * 1.0) + SUM(ISNULL(ASGI.HoldTime,0) * 1.0) + SUM(ISNULL(AED.AfterCallWork, 0) * 1.0) + SUM(ISNULL(ASGI2.TalkOutTime,0) * 1.0) + SUM(ISNULL(AED.OutboundCall,0) * 1.0)) as TimeReady,

(SUM(ISNULL(ASGI.WorkNotReadyTime ,0) * 1.0) + SUM(ISNULL(ASGI.WorkReadyTime ,0) * 1.0) + SUM(ISNULL(ASGI.ReservedStateTime,0) * 1.0) + SUM(ISNULL(ASGI.TalkInTime,0) * 1.0) + SUM(ISNULL(ASGI.HoldTime,0) * 1.0) + SUM(ISNULL(AED.AfterCallWork, 0) * 1.0) + SUM(ISNULL(ASGI2.TalkOutTime,0) * 1.0) + SUM(ISNULL(AED.OutboundCall,0) * 1.0))    /    SUM(ISNULL(AI.LoggedOnTime,0)-ISNULL(AED.LunchTime,0) * 1.0) as PerUtilisation,

(SUM(ISNULL(ASGI.WorkNotReadyTime ,0) * 1.0) + SUM(ISNULL(ASGI.WorkReadyTime ,0) * 1.0) + SUM(ISNULL(ASGI.ReservedStateTime,0) * 1.0) + SUM(ISNULL(ASGI.TalkInTime,0) * 1.0) + SUM(ISNULL(ASGI.HoldTime,0) * 1.0) + SUM(ISNULL(AED.AfterCallWork, 0) * 1.0) + SUM(ISNULL(ASGI2.TalkOutTime,0) * 1.0) + SUM(ISNULL(AED.OutboundCall,0) * 1.0))   as TimeUtilisation,

(SUM(ISNULL(AI.LoggedOnTime,0)-ISNULL(AED.LunchTime,0) * 1.0) - (SUM(ISNULL(ASGI.WorkNotReadyTime ,0) * 1.0) + SUM(ISNULL(ASGI.WorkReadyTime ,0) * 1.0) + SUM(ISNULL(ASGI.ReservedStateTime,0) * 1.0) + SUM(ISNULL(AI.AvailTime,0) * 1.0) + SUM(ISNULL(ASGI.TalkInTime,0) * 1.0) + SUM(ISNULL(ASGI.HoldTime,0) * 1.0) + SUM(ISNULL(AED.AfterCallWork, 0) * 1.0) + SUM(ISNULL(ASGI2.TalkOutTime,0) * 1.0) + SUM(ISNULL(AED.OutboundCall,0) * 1.0))) / SUM(ISNULL(AI.LoggedOnTime,0)-ISNULL(AED.LunchTime,0) * 1.0) as PerNotReady,

(SUM(ISNULL(AI.LoggedOnTime,0)-ISNULL(AED.LunchTime,0) * 1.0) - (SUM(ISNULL(ASGI.WorkNotReadyTime ,0) * 1.0) + SUM(ISNULL(ASGI.WorkReadyTime ,0) * 1.0) + SUM(ISNULL(ASGI.ReservedStateTime,0) * 1.0) + SUM(ISNULL(AI.AvailTime,0) * 1.0) + SUM(ISNULL(ASGI.TalkInTime,0) * 1.0) + SUM(ISNULL(ASGI.HoldTime,0) * 1.0) + SUM(ISNULL(AED.AfterCallWork, 0) * 1.0) + SUM(ISNULL(ASGI2.TalkOutTime,0) * 1.0) + SUM(ISNULL(AED.OutboundCall,0) * 1.0)))  as TimeNotReady,

SUM(ISNULL(AI.AvailTime,0) * 1.0) / SUM(ISNULL(AI.LoggedOnTime,0)-ISNULL(AED.LunchTime,0) * 1.0) as PerAvail,

((SUM(ISNULL(ASGI.WorkNotReadyTime,0) * 1.0) + SUM(ISNULL(ASGI.WorkReadyTime ,0) * 1.0) + SUM(ISNULL(AED.AfterCallWork, 0) * 1.0)) / SUM(ISNULL(ASGI.CallsAnswered,0) * 1.0)) / (( SUM(ISNULL(ASGI.TalkInTime,0) * 1.0) + SUM(ISNULL(ASGI.HoldTime,0) * 1.0) + SUM(ISNULL(ASGI.ReservedStateTime,0) * 1.0) + SUM(ISNULL(ASGI.WorkNotReadyTime,0) * 1.0) + SUM(ISNULL(ASGI.WorkReadyTime ,0) * 1.0) + SUM(ISNULL(AED.AfterCallWork, 0) * 1.0)) / SUM(ISNULL(ASGI.CallsAnswered,0) * 1.0) ) as PerACW,

((SUM(ISNULL(ASGI.WorkNotReadyTime,0) * 1.0) + SUM(ISNULL(ASGI.WorkReadyTime ,0) * 1.0) + SUM(ISNULL(AED.AfterCallWork, 0) * 1.0)) )  as TimeACW,

(( SUM(ISNULL(ASGI.TalkInTime,0) * 1.0) + SUM(ISNULL(ASGI.HoldTime,0) * 1.0) + SUM(ISNULL(ASGI.ReservedStateTime,0) * 1.0) + SUM(ISNULL(ASGI.WorkNotReadyTime,0) * 1.0) + SUM(ISNULL(ASGI.WorkReadyTime ,0) * 1.0) + SUM(ISNULL(AED.AfterCallWork, 0) * 1.0)) / SUM(ISNULL(ASGI.CallsAnswered,0) * 1.0) ) as AHT,

(( SUM(ISNULL(ASGI.TalkInTime,0) * 1.0) + SUM(ISNULL(ASGI.HoldTime,0) * 1.0) + SUM(ISNULL(ASGI.ReservedStateTime,0) * 1.0) + SUM(ISNULL(ASGI.WorkNotReadyTime,0) * 1.0) + SUM(ISNULL(ASGI.WorkReadyTime ,0) * 1.0) + SUM(ISNULL(AED.AfterCallWork, 0) * 1.0)) ) as TimeAHT,

SUM(ISNULL(ASGI.WorkReadyTime ,0)) as WorkReadyTime,

SUM(ISNULL(ASGI.WorkNotReadyTime,0)) as WorkNotReadyTime,

SUM(ISNULL(ASGI.ReservedStateTime,0)) as ReservedStateTime,

SUM(ISNULL(AI.AvailTime,0)) as AvailTime,

SUM(ISNULL(ASGI.TalkInTime,0)) as TalkInTime,

SUM(ISNULL(ASGI.HoldTime,0)) as HoldTime,

SUM(ISNULL(AED.AfterCallWork, 0)) as DurationNotReady,

SUM(ISNULL(ASGI2.TalkOutTime, 0)) as TalkOutTime,

SUM(ISNULL(AED.OutboundCall,0)) as OutTime

FROM

(Select DateTime,

SkillTargetID,

CallsAnswered = SUM(ISNULL(CallsAnswered,0)),

WorkNotReadyTime = SUM(ISNULL(WorkNotReadyTime,0)),

WorkReadyTime = SUM(ISNULL(WorkReadyTime,0)),

ReservedStateTime = SUM(ISNULL(ReservedStateTime,0)),

TalkInTime = SUM(ISNULL(TalkInTime,0)),

TalkOutTime = SUM(ISNULL(TalkOutTime,0)),

HoldTime = SUM(ISNULL(HoldTime,0))

FROM Agent_Skill_Group_Interval

WHERE SkillTargetID IN (:AgentSkillTargetID)

and SkillGroupSkillTargetID IN (:SkillGroup)

and DateTime >= :startDate

and DateTime <= :endDate

GROUP BY DateTime, SkillTargetID) ASGI

LEFT OUTER JOIN (SELECT

DateTime,

SkillTargetID,

AgentOutCalls= SUM(ISNULL(AgentOutCalls,0)),

TalkOutTime= SUM(ISNULL(TalkOutTime,0))

FROM Agent_Skill_Group_Interval

WHERE SkillTargetID IN (:AgentSkillTargetID)

GROUP BY DateTime, SkillTargetID) ASGI2

ON ASGI2.DateTime=ASGI.DateTime AND ASGI2.SkillTargetID=ASGI.SkillTargetID

LEFT OUTER JOIN (SELECT

Interval=CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>15 AND DATEPART(minute,DateTime)<>30 AND DATEPART(minute,DateTime)<>45) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 15)*15 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 15)*15 ),0) END,

AgentSkillTargetID,

CallsCoded =             COUNT(CASE WHEN PeripheralCallType = 2 AND WrapupData is not null THEN ISNULL(WrapupData,0) END),

Trans =                    COUNT(CASE WHEN PeripheralCallType = 2 AND CallDisposition IN (28,29)  THEN ISNULL(AgentSkillTargetID,0) END),

NoAns =                    COUNT(CASE WHEN PeripheralCallType = 9 AND CallDisposition IN (10)  THEN ISNULL(AgentSkillTargetID,0) END)

FROM Termination_Call_Detail

WHERE  AgentSkillTargetID IN (:AgentSkillTargetID)

and SkillGroupSkillTargetID IN (:SkillGroup)

GROUP BY AgentSkillTargetID,

CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>15 AND DATEPART(minute,DateTime)<>30 AND DATEPART(minute,DateTime)<>45) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 15)*15 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 15)*15 ),0) END

) TCD

ON TCD.AgentSkillTargetID=ASGI.SkillTargetID AND TCD.Interval=ASGI.DateTime

LEFT OUTER JOIN (SELECT

Interval=CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>15 AND DATEPART(minute,DateTime)<>30 AND DATEPART(minute,DateTime)<>45) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 15)*15 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 15)*15 ),0) END,

SkillTargetID,

LunchTime = SUM(CASE WHEN ReasonCode IN ('07','107','126','49','63','77','91') THEN ISNULL(Duration,0) ELSE 0 END),

AfterCallWork =    SUM(CASE WHEN ReasonCode IN ('71','1','101','120','43','57','85')  THEN ISNULL(Duration,0) ELSE 0 END),

OutboundCall = SUM(CASE WHEN ReasonCode IN ('03','103','122','45','59','73','87') THEN ISNULL(Duration,0) ELSE 0 END)

FROM Agent_Event_Detail

WHERE Event = 3

AND ReasonCode IN ('07','107','126','49','63','77','91','71','1','101','120','43','57','85','03','103','122','45','59','73','87')

GROUP BY

CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>15 AND DATEPART(minute,DateTime)<>30 AND DATEPART(minute,DateTime)<>45) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 15)*15 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 15)*15 ),0) END,

SkillTargetID

) AED

ON AED.Interval=ASGI.DateTime AND AED.SkillTargetID=ASGI.SkillTargetID

LEFT OUTER JOIN (SELECT

Interval=CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>15 AND DATEPART(minute,DateTime)<>30 AND DATEPART(minute,DateTime)<>45) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 15)*15 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 15)*15 ),0) END,

SkillTargetID,

LoggedOnTime=SUM(ISNULL(LoggedOnTime,0)),

NotReadyTime=SUM(ISNULL(NotReadyTime,0)),

AvailTime=SUM(ISNULL(AvailTime,0))

FROM Agent_Interval

WHERE  SkillTargetID IN (:AgentSkillTargetID)

GROUP BY

CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>15 AND DATEPART(minute,DateTime)<>30 AND DATEPART(minute,DateTime)<>45) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 15)*15 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 15)*15 ),0) END,

SkillTargetID

) AI

ON AI.Interval=ASGI.DateTime AND AI.SkillTargetID=ASGI.SkillTargetID

LEFT JOIN Agent ON Agent.SkillTargetID=ASGI.SkillTargetID

LEFT JOIN Person ON Person.PersonID=Agent.PersonID

LEFT JOIN Agent_Team_Member ON Agent_Team_Member.SkillTargetID=Agent.SkillTargetID

LEFT JOIN Agent_Team ON Agent_Team.AgentTeamID=Agent_Team_Member.AgentTeamID

WHERE  ASGI.SkillTargetID IN (:AgentSkillTargetID)

and ASGI.DateTime >= :startDate

and ASGI.DateTime <= :endDate

GROUP BY

Agent_Team.EnterpriseName,

(Person.FirstName + ' ' + Person.LastName)

ORDER BY

Agent_Team.EnterpriseName,

(Person.FirstName + ' ' + Person.LastName)

[jacparke]

That looks like an expensive query.  SQLServer seems to handle virtual tables better for this sort of thing.  Have a look at the Agent Login/Logout template under CCE_TR_Historical, that was originally written like yours and took 20 minutes to run where it now takes 1-2.  The trick is to build the most exclusionary data sets first so that when you are finally ready to read the TCD table (which is of course not recommended on a live HDS) you can target it with as many conditions as possible. 

Also consider your grouping.  I've been caught a number of times grouping when the Interval tables will only have one row for whatever interval + the intersecting dimensions.  So Agent Interval will only have one row for a given agent at a given interval - and grouping is expensive.

Regards,

Jack Parker

[Jameson Gagnepain]

Man, that is a beast of a report. Good advice from Jack... here's my 2 cents as well.

From the standpoint of reducing calculations, I would start by redoing this part:

CallsCoded = COUNT(CASE WHEN PeripheralCallType = 2 AND WrapupData is not null THEN ISNULL(WrapupData,0) END),

Trans = COUNT(CASE WHEN PeripheralCallType = 2 AND CallDisposition IN (28,29)  THEN ISNULL(AgentSkillTargetID,0) END),

NoAns = COUNT(CASE WHEN PeripheralCallType = 9 AND CallDisposition IN (10)  THEN ISNULL(AgentSkillTargetID,0) END)

It should be fewer calculations to do it this way instead:

CallsCoded = SUM(CASE WHEN PeripheralCallType = 2 AND WrapupData is not null THEN 1 ELSE 0 END),

Trans = SUM(CASE WHEN PeripheralCallType = 2 AND CallDisposition IN (28,29)  THEN 1 ELSE 0 END),

NoAns = SUM(CASE WHEN PeripheralCallType = 9 AND CallDisposition = 10  THEN 1 ELSE 0 END)

One thing to keep in mind when optimizing anything with one or more "AND" statements: it will evaluate to false as soon as the first false term is found... so for the above example, if PeripheralCallType is not 2, then SQL won't bother checking if WrapupData is not null. If you know that one part of your AND statement is much more likely to be false (and is not an "expensive" statement), you can put that first to reduce overall calculations. If "CallDisposition = 10" is far less likely to be true in your environment than "PeripheralCallType = 9", then you can change the last line to shave off a few milliseconds:

NoAns = SUM(CASE WHEN CallDisposition = 10 AND PeripheralCallType = 9 THEN 1 ELSE 0 END)

What kind of time intervals are you running this for? What's the quantity of TCD rows for that time interval?

-Jameson