Click here to Skip to main content
15,028,671 members
Please Sign up or sign in to vote.
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

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>
   
v2
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
   
Comments
Jörgen Andersson 4-Jun-14 17:13pm
   
I'm afraid it is stored as strings. :wtf:
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
   
Comments
Jörgen Andersson 10-Jun-14 16:00pm
   
First - Reply using the reply button, I would get a mail telling me that you've replied to my solution then.
Secondly - Update your question instead of posting a solution, and I'll update my solution in response if necessary.
Third - using a range would be as simple as: rmstrandte between {?Startdate} and {?Enddate}
But that would of course depend on how you call the query.

Have you tried my suggestion?

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900