Solved

Help needed with query

Posted on 2007-04-10
3
269 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

'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 …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

732 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