Mark Wilson
asked on
Help Needed With Query
Hi,
Im having a bit of a problem with the query below, it runs extremely slow and I just wandered if there is a more efficient way of writing it.
The date fields used in the query are datetime, so I am looking to compare against date only.
Any help would be apprecited.
@team int,
@start date,
@end date
SELECT a.Eventdate, a.resourceid, a.resourcename, a.startshift, a.endshift, DATEDIFF(s, a.startshift, a.endshift) AS shifttime, m.talk, m.hold, m.wtime, m.InboundCalls, n.outbound, n.outboundcalls, o.internal, d.Lunch,e.Personal
FROM
(SELECT CONVERT(VARCHAR, eventDateTime, 103) AS Eventdate, resourceid, resourcename, MIN(eventDateTime) AS startshift, MAX(eventDateTime) AS endshift
FROM AgentStateDetail INNER JOIN
resource r ON r.resourceid = agentstatedetail.agentid INNER JOIN
resourcegroup rg ON r.resourcegroupid = rg.resourcegroupid
WHERE rg.resourcegroupid = @team AND eventdatetime >= cast(@start as datetime) AND eventdatetime < cast(@end +1 as datetime)
GROUP BY CONVERT(VARCHAR, eventDateTime, 103), resourceid, resourcename) a
LEFT OUTER JOIN
(SELECT CONVERT(VARCHAR, c.startDateTime, 103) AS startDate, ad.resourceID, SUM(ad.talkTime) AS talk, SUM(ad.holdTime) AS hold, SUM(ad.workTime) AS wtime, COUNT(c.sessionID) AS InboundCalls
FROM DB_CRA_CCDR.dbo.ContactCal
ContactRoutingDetail crd ON c.sessionID = crd.sessionID AND c.sessionSeqNum = crd.sessionSeqNum INNER JOIN AgentConnectionDetail ad ON c.sessionID = ad.sessionID AND c.sessionSeqNum = ad.sessionSeqNum INNER JOIN Resource r ON ad.resourceID = r.resourceID INNER JOIN ResourceGroup rg ON r.resourceGroupID = rg.resourceGroupID
WHERE rg.resourcegroupid = @team AND c.startdatetime >= cast(@start as datetime) AND c.startdatetime < cast(@end +1 as datetime) and c.contacttype = 1
GROUP BY CONVERT(VARCHAR, c.startDateTime, 103), ad.resourceID) m ON a.resourceid = m.resourceID AND a.Eventdate = m.startDate LEFT OUTER JOIN
(SELECT CONVERT(VARCHAR, c.startdatetime, 103) AS startdate, c.originatorid, SUM(datediff(s, c.startDateTime, c.endDateTime)) AS outbound, COUNT(c.sessionid) AS outboundcalls
FROM DB_CRA_CCDR.dbo.ContactCal
Resource r ON c.originatorid = r.resourceid INNER JOIN
resourcegroup rg ON r.resourcegroupid = rg.resourcegroupid
WHERE c.contacttype = 2 AND (c.callednumber LIKE '9%' OR
c.callednumber LIKE '8%') AND rg.resourcegroupid @team AND c.startdatetime >= cast(@start as datetime) AND c.startdatetime < cast(@end +1 as datetime)
GROUP BY CONVERT(VARCHAR, c.startdatetime, 103), c.originatorid) n ON a.resourceid = n.originatorid AND
a.Eventdate = n.startdate LEFT OUTER JOIN
(SELECT CONVERT(VARCHAR, c.startdatetime, 103) AS Startdate, c.originatorid, SUM(datediff(s, c.startDateTime, c.endDateTime)) AS internal
FROM DB_CRA_CCDR.dbo.ContactCal
Resource r ON c.originatorid = r.resourceid INNER JOIN
resourcegroup rg ON r.resourcegroupid = rg.resourcegroupid
WHERE c.contacttype = 3 AND r.resourcegroupid = @team AND c.startdatetime >= cast(@start as datetime) AND c.startdatetime < cast(@end+1 as datetime)
GROUP BY CONVERT(VARCHAR, c.startdatetime, 103), c.originatorid) o ON a.resourceid = o.originatorid AND
a.Eventdate = o.Startdate LEFT OUTER JOIN
(SELECT CONVERT(VARCHAR, eventDateTime, 103) AS Eventdate, Agentid, SUM(DATEDIFF(second, eventDatetime, NextDatetime)) AS Lunch
FROM (SELECT a.Agentid, a.[eventDatetime],
(SELECT TOP 1 b.[eventDatetime]
FROM agentstatedetail b
WHERE a.Agentid = b.Agentid AND CONVERT(VARCHAR, b.[eventDatetime], 103) = CONVERT(VARCHAR,
a.[eventDateTime], 103) AND b.[eventDatetime] > a.[eventDatetime]) AS NextDatetime
FROM agentstatedetail a INNER JOIN
Resource r ON a.agentid = r.resourceid INNER JOIN
resourcegroup rg ON r.resourcegroupid = rg.resourcegroupid
WHERE a.reasonCode IN (7) AND rg.resourcegroupid = @team AND a.eventdatetime >= cast(@start as datetime) AND a.eventdatetime < cast(@end} +1 as datetime)) x
WHERE (NextDatetime IS NOT NULL)
GROUP BY CONVERT(VARCHAR, eventDateTime, 103), Agentid) d ON a.resourceid = d.Agentid AND a.Eventdate = d.Eventdate
LEFT OUTER JOIN
(SELECT CONVERT(VARCHAR, eventDateTime, 103) AS Eventdate,Agentid, SUM(DATEDIFF(second, eventDatetime, NextDatetime)) AS Personal
FROM (SELECT a.Agentid, a.[eventDatetime],
(SELECT TOP 1 b.[eventDatetime]
FROM agentstatedetail b
WHERE a.Agentid = b.Agentid AND CONVERT(VARCHAR, b.[eventDatetime], 103) = CONVERT(VARCHAR,
a.[eventDateTime], 103) AND b.[eventDatetime] > a.[eventDatetime]) AS NextDatetime
FROM agentstatedetail a inner join resource r on r.resourceid = a.agentid
WHERE a.reasonCode IN (58, 4) and r.resourcegroupid = @teamAND a.eventdatetime >= cast(@start as datetime) AND a.eventdatetime < cast(@end +1 as datetime)) x
WHERE (NextDatetime IS NOT NULL)
GROUP BY CONVERT(VARCHAR, eventDatetime, 103), Agentid) e ON a.resourceid = e.Agentid and a.eventdate = e.eventdate
Im having a bit of a problem with the query below, it runs extremely slow and I just wandered if there is a more efficient way of writing it.
The date fields used in the query are datetime, so I am looking to compare against date only.
Any help would be apprecited.
@team int,
@start date,
@end date
SELECT a.Eventdate, a.resourceid, a.resourcename, a.startshift, a.endshift, DATEDIFF(s, a.startshift, a.endshift) AS shifttime, m.talk, m.hold, m.wtime, m.InboundCalls, n.outbound, n.outboundcalls, o.internal, d.Lunch,e.Personal
FROM
(SELECT CONVERT(VARCHAR, eventDateTime, 103) AS Eventdate, resourceid, resourcename, MIN(eventDateTime) AS startshift, MAX(eventDateTime) AS endshift
FROM AgentStateDetail INNER JOIN
resource r ON r.resourceid = agentstatedetail.agentid INNER JOIN
resourcegroup rg ON r.resourcegroupid = rg.resourcegroupid
WHERE rg.resourcegroupid = @team AND eventdatetime >= cast(@start as datetime) AND eventdatetime < cast(@end +1 as datetime)
GROUP BY CONVERT(VARCHAR, eventDateTime, 103), resourceid, resourcename) a
LEFT OUTER JOIN
(SELECT CONVERT(VARCHAR, c.startDateTime, 103) AS startDate, ad.resourceID, SUM(ad.talkTime) AS talk, SUM(ad.holdTime) AS hold, SUM(ad.workTime) AS wtime, COUNT(c.sessionID) AS InboundCalls
FROM DB_CRA_CCDR.dbo.ContactCal
ContactRoutingDetail crd ON c.sessionID = crd.sessionID AND c.sessionSeqNum = crd.sessionSeqNum INNER JOIN AgentConnectionDetail ad ON c.sessionID = ad.sessionID AND c.sessionSeqNum = ad.sessionSeqNum INNER JOIN Resource r ON ad.resourceID = r.resourceID INNER JOIN ResourceGroup rg ON r.resourceGroupID = rg.resourceGroupID
WHERE rg.resourcegroupid = @team AND c.startdatetime >= cast(@start as datetime) AND c.startdatetime < cast(@end +1 as datetime) and c.contacttype = 1
GROUP BY CONVERT(VARCHAR, c.startDateTime, 103), ad.resourceID) m ON a.resourceid = m.resourceID AND a.Eventdate = m.startDate LEFT OUTER JOIN
(SELECT CONVERT(VARCHAR, c.startdatetime, 103) AS startdate, c.originatorid, SUM(datediff(s, c.startDateTime, c.endDateTime)) AS outbound, COUNT(c.sessionid) AS outboundcalls
FROM DB_CRA_CCDR.dbo.ContactCal
Resource r ON c.originatorid = r.resourceid INNER JOIN
resourcegroup rg ON r.resourcegroupid = rg.resourcegroupid
WHERE c.contacttype = 2 AND (c.callednumber LIKE '9%' OR
c.callednumber LIKE '8%') AND rg.resourcegroupid @team AND c.startdatetime >= cast(@start as datetime) AND c.startdatetime < cast(@end +1 as datetime)
GROUP BY CONVERT(VARCHAR, c.startdatetime, 103), c.originatorid) n ON a.resourceid = n.originatorid AND
a.Eventdate = n.startdate LEFT OUTER JOIN
(SELECT CONVERT(VARCHAR, c.startdatetime, 103) AS Startdate, c.originatorid, SUM(datediff(s, c.startDateTime, c.endDateTime)) AS internal
FROM DB_CRA_CCDR.dbo.ContactCal
Resource r ON c.originatorid = r.resourceid INNER JOIN
resourcegroup rg ON r.resourcegroupid = rg.resourcegroupid
WHERE c.contacttype = 3 AND r.resourcegroupid = @team AND c.startdatetime >= cast(@start as datetime) AND c.startdatetime < cast(@end+1 as datetime)
GROUP BY CONVERT(VARCHAR, c.startdatetime, 103), c.originatorid) o ON a.resourceid = o.originatorid AND
a.Eventdate = o.Startdate LEFT OUTER JOIN
(SELECT CONVERT(VARCHAR, eventDateTime, 103) AS Eventdate, Agentid, SUM(DATEDIFF(second, eventDatetime, NextDatetime)) AS Lunch
FROM (SELECT a.Agentid, a.[eventDatetime],
(SELECT TOP 1 b.[eventDatetime]
FROM agentstatedetail b
WHERE a.Agentid = b.Agentid AND CONVERT(VARCHAR, b.[eventDatetime], 103) = CONVERT(VARCHAR,
a.[eventDateTime], 103) AND b.[eventDatetime] > a.[eventDatetime]) AS NextDatetime
FROM agentstatedetail a INNER JOIN
Resource r ON a.agentid = r.resourceid INNER JOIN
resourcegroup rg ON r.resourcegroupid = rg.resourcegroupid
WHERE a.reasonCode IN (7) AND rg.resourcegroupid = @team AND a.eventdatetime >= cast(@start as datetime) AND a.eventdatetime < cast(@end} +1 as datetime)) x
WHERE (NextDatetime IS NOT NULL)
GROUP BY CONVERT(VARCHAR, eventDateTime, 103), Agentid) d ON a.resourceid = d.Agentid AND a.Eventdate = d.Eventdate
LEFT OUTER JOIN
(SELECT CONVERT(VARCHAR, eventDateTime, 103) AS Eventdate,Agentid, SUM(DATEDIFF(second, eventDatetime, NextDatetime)) AS Personal
FROM (SELECT a.Agentid, a.[eventDatetime],
(SELECT TOP 1 b.[eventDatetime]
FROM agentstatedetail b
WHERE a.Agentid = b.Agentid AND CONVERT(VARCHAR, b.[eventDatetime], 103) = CONVERT(VARCHAR,
a.[eventDateTime], 103) AND b.[eventDatetime] > a.[eventDatetime]) AS NextDatetime
FROM agentstatedetail a inner join resource r on r.resourceid = a.agentid
WHERE a.reasonCode IN (58, 4) and r.resourcegroupid = @teamAND a.eventdatetime >= cast(@start as datetime) AND a.eventdatetime < cast(@end +1 as datetime)) x
WHERE (NextDatetime IS NOT NULL)
GROUP BY CONVERT(VARCHAR, eventDatetime, 103), Agentid) e ON a.resourceid = e.Agentid and a.eventdate = e.eventdate
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, speeded up a lot
@start date,
@end date
but still do:
cast(@start as datetime)
which is double work, and even dangerous... remove the cast
also:
cast(@end +1 as datetime)
should be better:
dateadd(day, 1, @end)
anyhow, that should not solve the performance problem.
please upload somewhere the execution plan graph (in query analyser) of this query.