极慢的代码。 [英] Extremely Slow Code.

问题描述

我在Crystal Reports中有一个报告，它应该捕获在给定时间范围内进行的所有呼叫尝试。我遇到的问题是代码非常慢......导致Crystal崩溃或查询超时。我无法保存对代码的任何更改，因为一旦我在数据库专家中进行编辑，Crystal就会冻结。我在SAS中测试了SQL并确认它有效。有没有人有任何想法为什么这个代码崩溃或我可以做些什么可能加快它？

与行为一样（
 
选择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，
 
例如当action_code ='4'然后'Home Phone'否则'Other Phone'结束为Phonetype，
 rmstrantim，rmsdateasg 
 
 
来自
 
 rptingdata .roactreacc a 
 left join report.rmaster b 
 on a.rmsfilenum = b.rmsfilenum 
 left join reporting.rcomker c 
 on a.rmsfilenum = c.rmsfilenum 
。在a.attrnycode = d.rmsrecvrcd 
左边加入reporting.rprdbal e 
 on a.rmsfilenum = e.rmsfilenum 
 left join reporting.rofcrcd on join join reporting.rsasrecv d 
 e.rmsoffcrcd上的f 
 = f.rmsoffcrcd 
 
其中
（
 （action_code ='4'
 
和替换之间的rmstrandte（Char（（演员（时间戳（{？Enddate} ||'000000'）作为日期）） -  1天，ISO），' - ' ，''）和{？Enddate} 
）
或
（action_code ='5'
 
和替换之间的rmstrandte（Char（（演员）（时间戳（{ ？Enddate} ||'000000'）As Date）） -  1天，ISO），' - '，''）和{？Enddate} 
）
 
）
 
和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，替换（字符（（演员（时间戳（{？Enddate} ||'000000'）作为日期）） -  1天，ISO），' - '，''）作为strtdt 
来自accts一个
加上一个b 
在a.rmsfilenum = b.rmsfilenum 
和a.phonetype = b.phonetype），
 
 uniqatty as（
 select rmsfilenum，rmsfilenum来自totcount组的attrnycode， attrnycode），
 
 attyplcdt as（
选择a.rmsfilenum，b.rmstrandte选择plcdt，c.rmstrandte as recalldt 
来自uniqatty a 
 left join reporting.rhistfl b 
 on a.rmsfilenum = b.rmsfilenum 
和b.rmsfldimpl ='ATTRNYCODE'和b.hstnewvalu = a.attrnycode 
 left join reporting.rhistfl c 
 on a。 rmsfilenum = c.rmsfilenum 
和c.rmsfldimpl ='ATTRNYCODE'和c.hstoldvalu = a.attrnycode 
）
从totcount a $中选择a。*，plcdt，recalldt 
 b $ b加入attyplcdt b 
 a.rmsfilenum = b.rmsfilenum 

解决方案

我看到了几个问题查询，除了令人厌恶的地方，你将两个字符串连接成一个时间戳文字，它被投射到一个日期 - 1天，最后再次投射到一个字符串文字，你删除破折号。 P !.

请考虑使用日期类型！



无论如何，我不知道你的索引，但我可以看到另外两个问题。

首先，您可以通过过滤表 rptingdata.roactreacc 来降低处理的数据量，然后再加入其他表。

  WITH  a  AS （
  SELECT  a.rmsfilenum，
 a.attrnycode，
 a.rmstrandte 
  FROM  rptingdata.roactreacc a 
  WHERE  mstrandte  替换（ Char  （（Cast（ Timestamp （{？Enddate} || '  000000'）作为 日期）） -   1 天，ISO），'   - '，' ）和 {？Enddate} 
）

然后你在外部联接表上有一个条件 f.rmsbrglvl4 in（'C123'，'C124'，'C125'）这实际上变成了一对外部联接到内部联接。

因此，如果将这些联接指定为内部联接，则优化程序可以使用哈希联接或 sort merge 而不是嵌套循环，就像这样。

，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  当 action_code = '  4' 然后 ' < span class =code-string> Home Phone'  else  ' 其他手机' 结束  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' 或 action_code = '  5' ）
 & f.rmsbrglvl4  in （'  C123'，'  C124'，'  C125'）
）

请注意，您有几列没有指定它们属于哪个表，因此查询可能需要对其进行调整。

如果action_code属于表 rptingdata。 roactreacc 该条件需要为mov编辑第一个CTE。



正确的索引当然也是必要的。

请用所有列的表前缀更新问题。

计划也不错。



< edit>在这种情况下，应该在mstrandte上添加索引对于性能至关重要< /编辑>

我当然希望您将日期存储为日期而不是字符串。即便如此，引擎可能正在转换它们，因此您应该将strtdt和Enddate存储为本地变量或类似日期。

我会将第一个WHERE子句更改为仅包含BETWEEN部分一次。你展示的是（A和C）或（B和C），但它可能更好，因为（A或B）和C

PIEBALD ，感谢您的输入。我很感激帮助。我还在处理这段代码。



Jorgen ，我也非常感谢你的帮助。我在这里说实话：我从别人那里继承了这段代码而且我很困惑。如何摆脱复杂的Timestamp Enddate减法部分，只有2个用户输入的参数（即开始日期和结束日期）？这个想法是用户将运行此报告并被提示输入开始日期和结束日期，报告将吐出该范围的数据。代码似乎从 where 子句开始打破。

行为为（
 
选择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 action ='4'那么'家庭电话'否则'其他电话'结束为Phonetype，
 rmstrantim，rmsdateasg 
 
 
来自
 
 rptingdata.roactreacc a 
左边加入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 
 
其中
（action_code in（'4'，'5 ）
 
和替换之间的rmstrandte（Char（（演员（时间戳（{？Enddate} ||'000000'）作为日期）） -  1天，ISO），' - '，''）和{？Enddate} 
）
 
和f.rmsbrglvl4 in（'C123'，'C124'，'C125'）
 
），
 totissue as（
 
选择rmsfilenum，phonetype，count（*）as totcount 
 
 from accts 
 group by rmsfilenum，phonetype 
），
 totcount as（
 
选择a。*，totcount，替换（Char（（演员（时间戳（{？Enddate} ||'000000'）作为日期）） -  1天，ISO），$ b来自a.rmsfilenum = b.rmsfilenum 
和a.phonetype = b.phonetype），
 
 uniqatty as（
选择rmsfilenum，来自totcount group的attrnycode由rmsfilenum，attrnycode），
 
 attyplcdt as（
选择a.rmsfilenum，b.rmstrandte为plcdt，c.rmstrandte为recalldt 
来自uniqatty a 
 left join report.rhistfl b 
 on a.rmsfilenum = b.rmsfilenum 
 and b.rmsfldimpl ='ATTRNYCODE'an d b.hstnewvalu = a.attrnycode 
 left join reporting.rhistfl c 
 on a.rmsfilenum = c.rmsfilenum 
 and c.rmsfldimpl ='ATTRNYCODE'和c.hstoldvalu = a.attrnycode 
）
选择a。*，plcdt，recalldt 
来自totcount a 
加入attyplcdt b 
 on a.rmsfilenum = b.rmsfilenum 

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

解决方案

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.

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.

,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>

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

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

这篇关于极慢的代码。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！