Extremely Slow Code.

Question

0.00/5 (No votes)

See more:

I have a report in Crystal Reports that is supposed to capture all call attempts made during a given time range. The problem I am having is that the code is extremely slow...to the point that it cause Crystal to crash or the query times out. I am unable to save any changes to the code because Crystal freezes up as soon as I make edits in the database expert. I tested the SQL in SAS and confirmed that it works. Does anyone have any ideas as to why this code is crashing or what I could do to possibly speed it up?

with accts as (

select a.rmsfilenum, d.misddesc,a.attrnycode,b.rmsacctnum,c.rmszipcode, f.rmsbrglvl4, e.rmsoffcrcd,
cast(char(action_code) as char(16) ccsid 37)  as action_code,

cast(char(result_code) as char(16) ccsid 37)  as result_code,
a.rmstrandte,

case when action_code = '4' then 'Home Phone' else 'Other Phone' end as Phonetype,
rmstrantim,rmsdateasg


from

rptingdata.roactreacc a 
left join reporting.rmaster b 
on a.rmsfilenum =  b.rmsfilenum
left join reporting.rcomker c 
on a.rmsfilenum = c.rmsfilenum
left join reporting.rsasrecv d
on a.attrnycode = d.rmsrecvrcd
left join reporting.rprdbal e
on a.rmsfilenum = e.rmsfilenum
left join reporting.rofcrcd f
on e.rmsoffcrcd = f.rmsoffcrcd

where 
   (
           (    action_code = '4' 
            
and rmstrandte between  Replace(Char((Cast(Timestamp({?Enddate}||'000000') As Date)) - 1 days, ISO),'-','')  and {?Enddate}
           )
  or
           (    action_code = '5'
            
and rmstrandte between  Replace(Char((Cast(Timestamp({?Enddate}||'000000') As Date)) - 1 days, ISO),'-','')  and {?Enddate}
           )

   )

and f.rmsbrglvl4 in ('C123','C124','C125')

),
totissue as (

select rmsfilenum,phonetype, count(*) as totcount

from accts 
group by rmsfilenum,phonetype
  ),
totcount as (

select a.*, totcount , Replace(Char((Cast(Timestamp({?Enddate}||'000000') As Date)) - 1 days, ISO),'-','') as strtdt
from accts a 
join totissue b
on a.rmsfilenum = b.rmsfilenum 
and a.phonetype = b.phonetype),

uniqatty as (
select rmsfilenum,attrnycode from totcount group by rmsfilenum,attrnycode),

attyplcdt as (
select a.rmsfilenum, b.rmstrandte as plcdt, c.rmstrandte as recalldt
from uniqatty a
left join reporting.rhistfl b
on a.rmsfilenum = b.rmsfilenum
and b.rmsfldimpl = 'ATTRNYCODE' and b.hstnewvalu = a.attrnycode 
left join reporting.rhistfl c
on a.rmsfilenum = c.rmsfilenum
and c.rmsfldimpl = 'ATTRNYCODE' and c.hstoldvalu = a.attrnycode
)
select a.*, plcdt,recalldt
from totcount a 
join attyplcdt b 
on a.rmsfilenum = b.rmsfilenum

Posted 3-Jun-14 11:17am

Yeppy12

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Answer 1 · 2014-06-04T12:18:00

I'm seeing several problems with this query, besides the abomination where you concatenate two strings into a timestamp literal that's cast to a date - 1 day and finally cast again to a string literal where you remove the dashes. Phew!
Please consider using the date type!

Anyway, I don't know about your indexes, but I can see two more problems.
First you can lower the amount of data that you handle by filtering table rptingdata.roactreacc before joining with the other tables.

SQL

WITH a AS (
    SELECT  a.rmsfilenum,
            a.attrnycode,
            a.rmstrandte
    FROM    rptingdata.roactreacc a
    WHERE   mstrandte between  Replace(Char((Cast(Timestamp({?Enddate}||'000000') As Date)) - 1 days, ISO),'-','')  and {?Enddate}
    )

Then you have a condition on an outer joined table f.rmsbrglvl4 in ('C123','C124','C125') which effectively turns a couple of the outer joins into inner joins.
So if these joins are specified as inner joins instead the optimizer have the possibility of using hash joins or sort merge instead of nested loops, like this.

SQL

,accts as (
    SELECT  
            a.rmsfilenum,
            d.misddesc,
            a.attrnycode,
            b.rmsacctnum,
            c.rmszipcode,
            f.rmsbrglvl4,
            e.rmsoffcrcd,
            cast(char(action_code) as char(16) ccsid 37)  as action_code,
            cast(char(result_code) as char(16) ccsid 37)  as result_code,
            a.rmstrandte,
            case when action_code = '4' then 'Home Phone' else 'Other Phone' end as Phonetype,
            rmstrantim,
            rmsdateasg
    FROM    
            a
    JOIN    reporting.rprdbal e
        ON  a.rmsfilenum = e.rmsfilenum
    JOIN    reporting.rofcrcd f
        ON  e.rmsoffcrcd = f.rmsoffcrcd
    left OUTER JOIN reporting.rmaster b
        ON      a.rmsfilenum =  b.rmsfilenum
    left OUTER JOIN reporting.rcomker c 
        ON      a.rmsfilenum = c.rmsfilenum
    left OUTER JOIN reporting.rsasrecv d
        ON      a.attrnycode = d.rmsrecvrcd
    WHERE   (action_code = '4' OR  action_code = '5')
        AND f.rmsbrglvl4 in ('C123','C124','C125')
)

Note that you have a few columns where you haven't specified which table they belong to, so the query might need adjusting for that.
If action_code belongs to table rptingdata.roactreacc that condition needs to be moved to the first CTE.

Proper indexing is of course also necessary.
Please update the question with table prefix for all columns.
A plan would also be nice.

<edit>Should probably add an index on mstrandte is crucial for performance in this case</edit>

PIEBALDconsult · Answer 2 · 2014-06-03T12:28:00

Solution 1

I sure hope that you are storing dates as dates and not as strings. Even so, the engine may be converting them, so you should store the strtdt and Enddate as local variables or similar as dates.
I would change that first WHERE clause to only contain the BETWEEN part once. What you show is like ( A and C ) or ( B and C ), but it might be better as ( A or B ) and C

Posted 3-Jun-14 12:28pm

PIEBALDconsult

Comments

Jörgen Andersson 4-Jun-14 17:13pm

I'm afraid it is stored as strings. :wtf:

Yeppy12 · Answer 3 · 2014-06-09T11:41:00

PIEBALD, thanks for the input. I appreciate the help. I am still working on this code.

Jorgen, I also appreciate your help. I am going to be honest here: I inherited this code from someone else and am confused myself. How can I get rid of the complicated Timestamp Enddate subtraction part and just have 2 parameters that the user will input (i.e., start date and end date)? The idea is that a user will run this report and be prompted to enter a start date and an end date, and the report will spit out the data for that range. The code seems to break starting with the where clause.

with accts as (

select a.rmsfilenum, d.misddesc,a.attrnycode,b.rmsacctnum,c.rmszipcode, f.rmsbrglvl4, e.rmsoffcrcd,
cast(char(action_code) as char(16) ccsid 37)  as action_code,

cast(char(result_code) as char(16) ccsid 37)  as result_code,
a.rmstrandte,

case when action_code = '4' then 'Home Phone' else 'Other Phone' end as Phonetype,
rmstrantim,rmsdateasg


from

rptingdata.roactreacc a 
left join reporting.rmaster b 
on a.rmsfilenum =  b.rmsfilenum
left join reporting.rcomker c 
on a.rmsfilenum = c.rmsfilenum
left join reporting.rsasrecv d
on a.attrnycode = d.rmsrecvrcd
left join reporting.rprdbal e
on b.rmsfilenum = e.rmsfilenum
left join reporting.rofcrcd f
on e.rmsoffcrcd = f.rmsoffcrcd

where 
             (    action_code in ('4', '5')
            
and rmstrandte between  Replace(Char((Cast(Timestamp({?Enddate}||'000000') As Date)) - 1 days, ISO),'-','')  and {?Enddate}
           )
    
and f.rmsbrglvl4 in ('C123','C124','C125')

),
totissue as (

select rmsfilenum,phonetype, count(*) as totcount

from accts 
group by rmsfilenum,phonetype
  ),
totcount as (

select a.*, totcount, Replace(Char((Cast(Timestamp({?Enddate}||'000000') As Date)) - 1 days, ISO),
from accts a 
join totissue b
on a.rmsfilenum = b.rmsfilenum 
and a.phonetype = b.phonetype),

uniqatty as (
select rmsfilenum,attrnycode from totcount group by rmsfilenum,attrnycode),

attyplcdt as (
select a.rmsfilenum, b.rmstrandte as plcdt, c.rmstrandte as recalldt
from uniqatty a
left join reporting.rhistfl b
on a.rmsfilenum = b.rmsfilenum
and b.rmsfldimpl = 'ATTRNYCODE' and b.hstnewvalu = a.attrnycode 
left join reporting.rhistfl c
on a.rmsfilenum = c.rmsfilenum
and c.rmsfldimpl = 'ATTRNYCODE' and c.hstoldvalu = a.attrnycode
)
select a.*, plcdt,recalldt
from totcount a 
join attyplcdt b 
on a.rmsfilenum = b.rmsfilenum

Extremely Slow Code.

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0