Sub query slowing down performance

[IanWaterman]

I am trying to build some reports about Agent availability detailing how they spend their time during a shift.



Because the night shift agents can start any time between 5pm and 8pm I need to determine there log on and log off times so that I do not double count time, it is mainly because the logon record contains the total log on time. If I start report at a fixed time of say 8 pm then I can have agent activities but no logon on time if they logged in at say 19:50. I cant’ run the report from 19:00 to 20:00 on following day as I then capture two logon periods.

I therefore built this query to find first logon and last logoff to limit range for each agent

select agent_unique_ref, 
 min(case when event_code = 'LOGON' then Event_Date_Time else {ts '2100-12-31 00:00:00'} end) first_logon_time,
 max(case when event_code = 'LOGOF' then Event_Date_Time else {ts '1900-01-01 00:00:00'} end) last_logoff_time
                  from Agent_Details_001
                  where event_code in ('LOGON', 'LOGOF')
                  and Event_Date_Time >= dateadd(hh, -7,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)-1) AND 
                  Event_Date_Time < dateadd(hh, -4,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0))  AND
                  Agent_Unique_Ref in ('AGT100114' ,'AGT100053' ,'AGT100056' ,'AGT100063' ,'AGT100068' ,'AGT100094' ,'AGT100101' ,'AGT100102' 
                  ,'AGT100115', 'AGT100115','AGT100116' ,'AGT100118' ,'AGT100146' ,'AGT100176' ,'AGT100219' ,'AGT100241' ,'AGT100245' ,'AGT100247' 
                  ,'AGT100278' ,'AGT100315')
                   group by agent_unique_ref

This executes in less than a second.

I then incorporated this into another query

SELECT cd.Agent_Unique_Ref, RecordNo, Call_Type, Connected_Time, Start_Date_Time, Reason_Code, Arrival_Queue_Unique_Ref
 FROM   Call_Details_001 cd
 INNER JOIN (select agent_unique_ref, 
 min(case when event_code = 'LOGON' then Event_Date_Time else {ts '2100-12-31 00:00:00'} end) first_logon_time,
 max(case when event_code = 'LOGOF' then Event_Date_Time else {ts '1900-01-01 00:00:00'} end) last_logoff_time
                  from Agent_Details_001
                  where event_code in ('LOGON', 'LOGOF')
                  and Event_Date_Time >= dateadd(hh, -7,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)-1) AND 
                  Event_Date_Time < dateadd(hh, -4,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0))  AND
                  Agent_Unique_Ref in ('AGT100114' ,'AGT100053' ,'AGT100056' ,'AGT100063' ,'AGT100068' ,'AGT100094' ,'AGT100101' ,'AGT100102' 
                  ,'AGT100115', 'AGT100115','AGT100116' ,'AGT100118' ,'AGT100146' ,'AGT100176' ,'AGT100219' ,'AGT100241' ,'AGT100245' ,'AGT100247' 
                  ,'AGT100278' ,'AGT100315')
                   group by agent_unique_ref) as al
                  on cd.Agent_Unique_Ref = al.Agent_Unique_Ref
 WHERE  cd.Agent_Unique_Ref in ('AGT100053', 'AGT100056', 'AGT100063', 'AGT100068', 'AGT100094', 'AGT100101', 
 'AGT100102', 'AGT100114', 'AGT100115', 'AGT100116', 'AGT100118', 'AGT100146', 'AGT100176', 'AGT100219', 
 'AGT100241', 'AGT100245', 'AGT100247', 'AGT100278', 'AGT100315')
 AND Start_Date_Time >= first_logon_time
 AND Start_Date_Time <= last_logoff_time
 order by cd.Agent_Unique_Ref, Start_Date_Time

This also executes in less than a second.

However, this one runs and runs and runs.

SELECT  Event_Code, Event_Date_Time, Duration, ad1.Reason_Code, rc.Reason_Description, Full_Name, 
 ad.Agent_Unique_Ref, rc1.Reason_Description event_desc
 FROM   Agent_Details ad 
 INNER JOIN Agent_Details_001 ad1 
      ON ad.Agent_Unique_Ref = ad1.Agent_Unique_Ref
 INNER JOIN (select agent_unique_ref, 
            min(case when event_code = 'LOGON' then Event_Date_Time else {ts '2100-12-31 00:00:00'} end) first_logon_time,
            max(case when event_code = 'LOGOF' then Event_Date_Time else {ts '1900-01-01 00:00:00'} end) last_logoff_time
                  from Agent_Details_001
                  where event_code in ('LOGON', 'LOGOF')
                  and Event_Date_Time >= dateadd(hh, -7,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)-1) AND 
                  Event_Date_Time < dateadd(hh, -4,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0))  AND
                  Agent_Unique_Ref in ('AGT100114' ,'AGT100053' ,'AGT100056' ,'AGT100063' ,'AGT100068' ,'AGT100094' ,'AGT100101' ,'AGT100102' 
                  ,'AGT100115', 'AGT100115','AGT100116' ,'AGT100118' ,'AGT100146' ,'AGT100176' ,'AGT100219' ,'AGT100241' ,'AGT100245' ,'AGT100247' 
                  ,'AGT100278' ,'AGT100315')
                   group by agent_unique_ref) as al
                  on ad1.Agent_Unique_Ref = al.Agent_Unique_Ref
                  AND ad1.Event_Date_Time >= first_logon_time
                  AND ad1.Event_Date_Time <= last_logoff_time
 LEFT OUTER JOIN Reason_Codes rc
      ON ad1.Reason_Code = rc.Reason_Code 
      AND ad1.Reason_Unique_Ref =rc.Reason_Unique_Ref
 LEFT OUTER JOIN Reason_Codes rc1
      ON ad1.Event_Code = rc1.Reason_Code
 WHERE ad.Agent_Unique_Ref in ('AGT100053', 'AGT100056', 'AGT100063', 'AGT100068', 'AGT100094', 'AGT100101', 
 'AGT100102', 'AGT100114', 'AGT100115', 'AGT100116', 'AGT100118', 'AGT100146', 'AGT100176', 'AGT100219', 
 'AGT100241', 'AGT100245', 'AGT100247', 'AGT100278', 'AGT100315')
 and (case  when event_code in ('ARDY', 'ACFIN', 'CONN', 'DCONN', 'LOGOF', 'QSTAT', 'ACOFF', 'ALERT', 'HELD') then 1
                  when event_code = 'AWNR' and ad1.reason_code <> 'NORES' then 1 
                  when event_code = 'ANRDY' and ad1.reason_code = 'NORES' then 1 else 0 end ) = 0
 ORDER BY Full_Name, Event_Date_Time

If I remove the subquery and replace date range with

WHERE ad1.Event_Date_Time >= dateadd(hh, -7,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)-1)
 AND ad1.Event_Date_Time <= dateadd(hh, -4,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0))

It executes in less than a second.

Any suggestions on how to make the never ending query run faster?

Ian

 

[Crowley16]

without reading through and understanding all your code, as a rule, don't use sub queries, they often kill performance.
essentially, you are running a query for every single record returned in your main query, so even if the sub query takes like 1 sec, if your main query returns 10k records, then the minimum will be 10k secs...

although at times, sub queries can actually improve performance.

anyway, usually I avoid sub queries by either loading the required data into a temp table, and then joining on the temp table.

--------------------
Procrastinate Now!