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
Mark WilsonBI DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Mark WilsonBI DeveloperAuthor 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
Guy Hengel [angelIII / a3]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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.