Solved

Help Needed With Query

Posted on 2007-03-29
3
282 Views
Last Modified: 2010-03-20
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.ContactCallDetail c INNER JOIN
                                                   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.ContactCallDetail c INNER JOIN
                                                   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.ContactCallDetail c INNER JOIN
                                                   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
0
Comment
Question by:halifaxman
3 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18814832
you have:
@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.

0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 18815500
is this any better ?

i've merged the inbound/outbound calculations into the one join / select
and the personal / lunch calculations as well...

could you also explain what the sql is trying to achieve...

but as angelIII has said we need the execution plan information to really assist with performance...

@team int,
@start date,
@end date

Declare @DateStart datetime,@DateEnd datetime

Select @DateStart=convert(datetime,@start,112)
      ,@DateEnd=dateadd(d,1,convery(datetime,@end,112))

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, n.internal, d.Lunch,d.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 >= @dateStart
                       AND eventdatetime < @DateEnd
                       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.ContactCallDetail c
                            INNER JOIN    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 >= @dateStart
                            AND c.startdatetime < @DateEnd
                            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(Case when c.contacttype=2
                                    then datediff(s, c.startDateTime, c.endDateTime)
                                    else null
                                    end
                                    ) AS outbound
                          ,sum(Case when c.contacttype=3
                                    then datediff(s, c.startDateTime, c.endDateTime)
                                    else null end) AS internal
                          ,COUNT(case when c.contacttype = 2 then c.sessionid else null end) AS outboundcalls
                            FROM          DB_CRA_CCDR.dbo.ContactCallDetail c
                            INNER JOIN    Resource r
                            ON c.originatorid = r.resourceid
                            INNER JOIN    resourcegroup rg
                            ON r.resourcegroupid = rg.resourcegroupid
                            WHERE rg.resourcegroupid @team
                            AND c.startdatetime >= @DAteStart
                            AND c.startdatetime < @DateEnd
                            AND ( (     c.contacttype = 2
                                    AND (c.callednumber LIKE '9%' OR c.callednumber LIKE '8%')
                                  )  
                                 OR      c.contacttype = 3
                                 )
                            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, eventDateTime, 103) AS Eventdate
                          , Agentid
                          , SUM(case when a.reasoncode=7
                                     then DATEDIFF(second, eventDatetime, NextDatetime)
                                     else null
                                     end
                               ) AS Lunch
                          , SUM(case when a.reasoncode in (58,4)
                                     then DATEDIFF(second, eventDatetime, NextDatetime)
                                     else null
                                     end
                               ) 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 a.agentid = r.resourceid
                                       INNER JOIN resourcegroup rg
                                          ON r.resourcegroupid = rg.resourcegroupid
                                       WHERE a.reasonCode in (7,58,4)
                                         AND rg.resourcegroupid = @team
                                         AND a.eventdatetime >= @datestart
                                         AND a.eventdatetime < @dateend
                                              ) x
                            WHERE      (NextDatetime IS NOT NULL)
                            GROUP BY CONVERT(VARCHAR, eventDateTime, 103), Agentid)   d
                            ON a.resourceid = d.Agentid
                            AND a.Eventdate = d.Eventdate
 

0
 

Author Comment

by:halifaxman
ID: 18833156
Thanks, speeded up a lot
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help writing a query 6 77
SQL Query including math and 2 tables problem 26 50
Is there any way to convert exponential value to number in sql server 5 38
string fuctions 4 25
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

775 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question