Help needed with query

I have developed the following query below

SELECT
a.startDateTime,a.sessionseqnum,a.enddatetime,a.numbe,a.contactdisposition,a.customvariable2, a.connecttime, a.queuetime, b.talktime, b.worktime, b.holdtime
FROM        
(SELECT c.sessionid, c.startDateTime, c.sessionseqnum, c.enddatetime,
CASE WHEN c.calledNumber = '11527' THEN '11574' WHEN c.calledNumber IN (11577', '11578') THEN '11567' WHEN c.calledNumber = '11566' THEN '11562' WHEN c.calledNumber = '11586' THEN '11589' WHEN c.calledNumber IN ('11565',
'11571', '11548', '11505', '11769')  THEN '11534' WHEN c.calledNumber = '11504' THEN '11608' WHEN c.calledNumber = '11575' THEN '11609' ELSE c.calledNumber END AS number, c.contactdisposition, case when c.customVariable1 ='' then c.customvariable2 else customvariable1 end customvariable2, c.connecttime, crd.queuetime
                       
FROM          
ContactCallDetail c LEFT OUTER JOIN ContactRoutingDetail crd ON c.sessionSeqNum = crd.sessionSeqNum AND c.sessionID = crd.sessionID
WHERE (c.contactType IN (1, 3)) and c.startdatetime >= cast(@start as datetime) AND c.startdatetime < cast(@end +1 as datetime)) a LEFT OUTER JOIN
(SELECT     sessionID, sessionseqnum, SUM(talktime) AS talktime, SUM(holdtime) AS holdtime, SUM(worktime) AS worktime
FROM  agentconnectiondetail acd
where acd.startdatetime >= cast(@start as datetime) AND acd.startdatetime < cast(@end +1 as datetime)
GROUP BY sessionid, sessionseqnum) b ON a.sessionid = b.sessionID AND a.sessionseqnum = b.sessionseqnum
WHERE     a.number = @scheme

@start and @end are datetime parameters converted to date only and @scheme is an int.

The problem I have is that the date query is slow, there is not a huge amount of data being returned but the query chugs. The problem from the execution plan is around the aggregations and the WHERE     a.number = @scheme.

Is there a better way to write this?

Thanks
halifaxmanAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>sorry the fields are datetime and the parameters are date

means that you should have:

WHERE (c.contactType IN (1, 3))
AND c.startdatetime >= @start
AND c.startdatetime < dateadd(day, 1, @end)

and not
WHERE (c.contactType IN (1, 3)) and c.startdatetime >= cast(@start as datetime) AND c.startdatetime < cast(@end +1 as datetime))

>WHERE     a.number = @scheme
should be better in the subquery directly:

SELECT
  a.startDateTime,a.sessionseqnum,a.enddatetime,a.numbe,a.contactdisposition,a.customvariable2
, a.connecttime, a.queuetime, b.talktime, b.worktime, b.holdtime
FROM        
(SELECT c.sessionid, c.startDateTime, c.sessionseqnum, c.enddatetime
, CASE WHEN c.calledNumber = '11527' THEN '11574'
       WHEN c.calledNumber IN ('11577', '11578') THEN '11567'
       WHEN c.calledNumber = '11566' THEN '11562'
       WHEN c.calledNumber = '11586' THEN '11589'
       WHEN c.calledNumber IN ('11565','11571', '11548', '11505', '11769')  THEN '11534'
       WHEN c.calledNumber = '11504' THEN '11608'
       WHEN c.calledNumber = '11575' THEN '11609'
       ELSE c.calledNumber END AS number
, c.contactdisposition
, case when c.customVariable1 ='' then c.customvariable2 else customvariable1 end customvariable2
, c.connecttime, crd.queuetime
FROM ContactCallDetail c
LEFT OUTER JOIN ContactRoutingDetail crd
  ON c.sessionSeqNum = crd.sessionSeqNum AND c.sessionID = crd.sessionID
WHERE c.contactType IN (1, 3)
AND c.startdatetime >= @start
AND c.startdatetime < dateadd(day, 1,@end)
WHERE CASE WHEN c.calledNumber = '11527' THEN '11574'
       WHEN c.calledNumber IN ('11577', '11578') THEN '11567'
       WHEN c.calledNumber = '11566' THEN '11562'
       WHEN c.calledNumber = '11586' THEN '11589'
       WHEN c.calledNumber IN ('11565','11571', '11548', '11505', '11769')  THEN '11534'
       WHEN c.calledNumber = '11504' THEN '11608'
       WHEN c.calledNumber = '11575' THEN '11609'
       ELSE c.calledNumber END = @scheme
) a
LEFT OUTER JOIN
( SELECT     sessionID, sessionseqnum, SUM(talktime) AS talktime, SUM(holdtime) AS holdtime, SUM(worktime) AS worktime
  FROM  agentconnectiondetail acd
  WHERE acd.startdatetime >= @start
    AND acd.startdatetime < dateadd(day, 1, @end )
  GROUP BY sessionid, sessionseqnum
) b
ON a.sessionid = b.sessionID
AND a.sessionseqnum = b.sessionseqnum


however, this will probably not solve your query time.

to solve that, you will have to make some change.
to know what change this has to be, I will need to know how the

CASE WHEN c.calledNumber = xxx THEN yyy

has to work.
2 ideas
* either make that "translation/mapping" a computed field on the table ContactCallDetail, so you could index/query that computed field directly.
  -> solution only to be used if that list of mappings is fixed (which I doubt)
* create a mapping table that translates the callednumber(s) into the values you need for that query, so you can include that table in your query.





0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>@start and @end are datetime parameters
then, why do you put cast(@start as datetime) ?

anhdhow, what indexes do you have on your table?
can you upload the execution plan graph somewhere?
0
 
halifaxmanAuthor Commented:
sorry the fields are datetime and the parameters are date

indexes are on c.calledNumber on the ContactCallDetail table

I will upload the execution plan later, as systems are down at the moment
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.