Solved

Help needed with query

Posted on 2007-04-10
3
268 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 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
convert in derived column 7 39
SYbase 4 37
SQL Syntax: How to force case sensitive query? 2 45
Pivot tables in SQL 1 20
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

821 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