SELECT
b.User_Id
,(CONVERT(varchar, DATEADD(hh, - 7, b.callstartdt), 101))as 'Dt'
,(COUNT(distinct b.SeqNum ) + Count(distinct c.SeqNum) + count(distinct d.seqnum)) as 'TotalCalls'
,COUNT(distinct b.SeqNum )as 'ACD'
,COUNT(distinct c.SeqNum)as 'AOD'
,COUNT(distinct d.seqnum) as 'Manual'
,COUNT(distinct e.SeqNum)as 'Contacts'
,COUNT (distinct es.seqnum) as 'Success'
FROM
[detail_epro].[dbo].[ACDCallDetail]as b
LEFT JOIN
[detail_epro].[dbo].[AODCallDetail]as c on c.User_Id = b.User_Id
LEFT JOIN
[detail_epro].[dbo].[manualCallDetail]as d on d.User_Id = b.User_Id
LEFT JOIN
(SELECT
USER_ID, CallStartDt, SeqNum
FROM
[detail_epro].[dbo].[AgentDispoDetail]
WHERE
Disp_Id IN
(100000150, 100000126, 100000137, 100000093, 100000133,
100000123, 100000094, 100000161, 100000162, 100000085,
100000084, 100000086, 100000096, 100000087, 100000157,
100000088, 100000097, 100000154, 100000148, 100000134,
100000131, 100000160, 100000156, 100000165, 100000166,
100000122, 100000121, 100000138, 100000130, 100000144,
100000132, 100000158, 100000098, 100000147, 100000100,
100000153, 100000139, 100000145, 100000101, 100000140,
100000102, 100000103, 100000104, 100000105, 100000106,
100000159, 100000112, 100000135, 100000090, 100000113,
100000141, 100000146, 100000115, 100000108, 100000092,
100000155, 100000125, 100000151, 100000136, 100000107,
100000142)
) AS e ON e.User_Id = b.User_Id
LEFT JOIN
(SELECT
USER_ID, CallStartDt, SeqNum
FROM
[detail_epro].[dbo].[AgentDispoDetail]
WHERE Disp_Id IN
(100000150, 100000137, 100000093, 100000133, 100000123,
100000094, 100000161, 100000085, 100000086, 100000157,
100000088, 100000131, 100000160, 100000156, 100000165,
100000166, 100000122, 100000121, 100000138, 100000144,
100000132, 100000098, 100000100, 100000153, 100000139,
100000145, 100000101, 100000140, 100000102, 100000103,
100000105, 100000106, 100000159, 100000112, 100000135,
100000141, 100000146, 100000115, 100000108, 100000092,
100000155, 100000125, 100000151, 100000136, 100000107)
) AS es ON es.User_Id = b.User_Id
WHERE
(CONVERT(varchar, DATEADD(hh, - 7, b.CallStartDt), 101)) = (CONVERT(varchar, DATEADD(hh, - 7, c.CallStartDt), 101))
AND (CONVERT(varchar, DATEADD(hh, - 7, b.CallStartDt), 101))= (CONVERT(varchar, DATEADD(hh, - 7, d.CallStartDt), 101))
AND (CONVERT(varchar, DATEADD(hh, - 7, b.CallStartDt), 101))= (CONVERT(varchar, DATEADD(hh, - 7, e.CallStartDt), 101))
AND (CONVERT(varchar, DATEADD(hh, - 7, b.CallStartDt), 101))= (CONVERT(varchar, DATEADD(hh, - 7, es.CallStartDt), 101))
AND (CONVERT(varchar, DATEADD(hh, - 7, b.CallStartDt), 101)) >= '08/01/2014'
GROUP BY
b.User_Id, b.CallStartDt
It's taking a lot longer than I would like to run this query, over a minute, I'm guessing it has a lot to do with the server but figured I would ask to see if anyone had any thoughts of making this faster
The query is to get some phone agent data, that isn't summarized by any tables
There are numerous issues with the query but the first thing I notice is the inefficient datetime conversions. So, I'd start with that part first, before examining the indexing and the execution plan.
I suppose you want to check if the various datetimes are in the same date (minus the 7 hours which is probably your timezone while the data are stored in UTC). So, lets try this, instead of that (horrible) WHERE
:
CROSS APPLY
( SELECT dt = DATEADD(hour, -7, b.CallStartDt) ) AS x
CROSS APPLY
( SELECT dt = DATEADD(day, +1, x.dt) ) AS y
WHERE
b.CallStartDt >= DATEADD(hour, +7, '20140801')
AND c.CallStartDt >= x.dt AND c.CallStartDt < y.dt
AND d.CallStartDt >= x.dt AND d.CallStartDt < y.dt
AND e.CallStartDt >= x.dt AND e.CallStartDt < y.dt
AND es.CallStartDt >= x.dt AND es.CallStartDt < y.dt
Explanation/notes:
the (CONVERT(varchar, DATEADD(hh, - 7, b.CallStartDt), 101)) >= '08/01/2014'
is utterly wrong. Not only it uses inefficient conversions, it will also return wrong results. Because the date (both as string and as date) '08/03/2014'
is after '08/01/2014'
but for other examples, it's the other way around: '09/09/2011' > '08/01/2014'
but obviously 2011 is before 2014.
All unnecessary calls to DATEDIFF()
and CONVERT()
have been removed. This way, not only several thousands calls (or million, depends on your tables sizes) to the functions will not be done but the optimizer will be able to use indexes for the various conditions, if there are indexes on these columns.
Only the (-7 hours) of b.CallStartDt
has been kept as there is no way to avoid that without altering the table (adding a computed column with an index though would help.)
Use sane formats for dates and datetimes, like '20140801'
('YYYYMMDD'
), which as Aaron Bertrand's blog explains is the only 100% secure format for using with dates in SQL-Server. See: Bad habits to kick : mis-handling date / range queries
With the DATEADD()
function, use the long forms. hour
instead of hh
, day
instead of dd
, minute
instead of mm
(or is it mi
?, or min
?, I keep forgetting.) Less error-prone.
More to do:
The 4 above conditions (the ones regarding c
, d
, e
and es
tables) should probably be moved to the respective LEFT
joins (as DRapp commented.)
Check the execution plan and whether indexes are available and used.
Minor details: