Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help needed with query

Posted on 2007-04-10
3
Medium Priority
?
279 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:halifaxman
  • 2
3 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18881247
>@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
 

Author Comment

by:halifaxman
ID: 18881312
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 18881363
>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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

'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 …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

927 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