Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Crystal report is executing very slow. Post Reply Post New Topic
Author Message
Anamika
Newbie
Newbie


Joined: 14 May 2009
Online Status: Offline
Posts: 6
Quote Anamika Replybullet Topic: Crystal report is executing very slow.
    Posted: 18 May 2009 at 4:43am
Hello,
 
We have a query which is used as a command object in Crystal reports. This report is executed against IBM DB2 database. It is taking 40 min to getback the results. Here is the Query. Please suggest to improve the performance of this as another reporting tool is taking 2-3 min time to fetch the results with the same query.
 
 
SELECT
A1.CARR_NUM ,  A1.CST_CTR_NUM ,A1.CLM_CLOSE_STS_CD ,   A1.CLM_RJCT_STS_CD ,A1.CLM_VOID_STS_CD ,  A1.PMT_MTCH_DT , A1.ASC_TRANS_TYP_CD ,  A1.DRUG_DESC ,               
A1.BATCH_NUM ,   T5.TPA_RJCT_CD ,  A1.DIV_NUM , A1.INVC_NUM ,  A1.SERVICE_DT ,  A1.ACTL_REFILL_NUM ,  A1.CLM_DSPSN_CD ,  A1.INVC_RSBMT_CD , A1.TP_TOTAL_DUE_AMT ,   
A1.INTL_BILG_MTHD_CD , A1.CARR_EOB_1_CD ,  T5.CARR_EOB_CD_DESC ,  A1.NTNL_DRUG_CD ,  A1.PTNT_FRST_NM ,  A1.PTNT_MDDL_INTL ,  A1.PTNT_LST_NM ,  A1.PTNT_BIRTH_DT ,
A1.CRDHLDR_ID_NUM   
FROM
 (SELECT DISTINCT
    T1.CARR_NUM ,  T1.CST_CTR_NUM ,  T1.CLM_CLOSE_STS_CD ,  T1.CLM_RJCT_STS_CD ,   T1.CLM_VOID_STS_CD ,  T3.PMT_MTCH_DT ,   
    T3.ASC_TRANS_TYP_CD ,  T2.DRUG_DESC ,  T4.BATCH_NUM , T1.DIV_NUM ,  T1.INVC_NUM ,  T1 . SERVICE_DT ,  T1.ACTL_REFILL_NUM ,                                         
    T1.CLM_DSPSN_CD ,  T1.INVC_RSBMT_CD ,T1.TP_TOTAL_DUE_AMT ,  T1.INTL_BILG_MTHD_CD ,T3.CARR_EOB_1_CD ,T1.NTNL_DRUG_CD ,  T2.PTNT_FRST_NM ,         T2.PTNT_MDDL_INTL , T2.PTNT_LST_NM ,  T2.PTNT_BIRTH_DT ,  T2.CRDHLDR_ID_NUM 
   FROM  
                PDRD2LO.VINVC_CLM T1                             
               , PDRD2LO.VPMNT_DTL T3                             
               , PDRD2LO.VINVC_CLM_2 T2                           
               , PDRD2LO.VINVC_ITM_PHARM  T4                     
                    WHERE  
  T1.CLM_CLOSE_STS_CD ='OP'                     
              AND  T1.CLM_RJCT_STS_CD ='RJ'                       
              AND  T1.CLM_VOID_STS_CD ='NV'                       
              AND  T3.ASC_TRANS_TYP_CD ='40'                      
              AND  T1.CARR_NUM = T3.CARR_NUM                      
           AND   T1.CST_CTR_NUM = T3.CST_CTR_NUM                  
           AND   T1.DIV_NUM = T3.DIV_NUM                          
           AND   T1.INVC_NUM = T3.INVC_NUM                        
           AND   T1.SERVICE_DT = T3.SERVICE_DT                    
           AND   T1.CLM_CTRL_NUM = T3.CLM_CTRL_NUM                
           AND   T1.ACTL_REFILL_NUM = T3.ACTL_REFILL_NUM          
           AND   T1.INVC_ITM_NUM = T3.INVC_ITM_NUM                
           AND   T1.REFILL_TRANS_NUM = T3.REFILL_TRANS_NUM        
           AND   T1.CARR_NUM = T2.CARR_NUM                        
           AND   T1.CST_CTR_NUM = T2.CST_CTR_NUM                  
           AND   T1.DIV_NUM = T2.DIV_NUM                          
           AND   T1.INVC_NUM = T2.INVC_NUM                        
           AND   T1.SERVICE_DT = T2.SERVICE_DT                  
           AND   T1.ACTL_REFILL_NUM = T2.ACTL_REFILL_NUM        
           AND   T1.CLM_CTRL_NUM = T2.CLM_CTRL_NUM              
           AND   T1.INVC_ITM_NUM = T2.INVC_ITM_NUM              
           AND   T1.REFILL_TRANS_NUM = T2.REFILL_TRANS_NUM      
           AND   T1.INVC_CTRL_NUM = T4.INVC_CTRL_NUM) A1        
LEFT OUTER JOIN  PDRD2LO.VCARR_EOB T5                
            ON    A1.CARR_EOB_1_CD = T5.CARR_EOB_CD             
            AND   A1.CARR_NUM = T5.CARR_NUM                     
            AND   A1.DIV_NUM = T5.DIV_NUM        
 
Thanks!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 18 May 2009 at 6:34am
Based on your description it may not be the fetching that is taking the time but rather the execution of the report design.
Do you have sub reports in this report or are you doing anything else in the report that might account for the difference?
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 18 May 2009 at 7:00am
as far as the query, I would check its speed on the server.  I would change the joins to inner joins if possible.  Perhaps make a view for table a1.
 
next is the getting values to use for parameters or data for the main report.  if for the main report, make a stored proc and use that as the data source.
 
absolutely, look at what DBlank recommends, sub reports can slow a report down.  is there additional filtering of the data in the report...if you are returning a massive number of rows and then getting rid of most of the them, filter them on the server...it is way more efficient.
IP IP Logged
Anamika
Newbie
Newbie


Joined: 14 May 2009
Online Status: Offline
Posts: 6
Quote Anamika Replybullet Posted: 18 May 2009 at 10:43pm
Thanks for reply.
I dont use Subreports in this. Working is straightforward. We are just displaying the columns on the report design of first select statement.
 
IP IP Logged
Anamika
Newbie
Newbie


Joined: 14 May 2009
Online Status: Offline
Posts: 6
Quote Anamika Replybullet Posted: 18 May 2009 at 10:49pm

Business users has not replied for these questions like creating a view,SP. They are non-techincal persons. but since the same query is working fine in another reporting tool; they want the same.

I think the searching criteria took a lot of time. the records went upto (0 of 300000) in crystal, later i stopped it.
 
Inner join is getting the result in 12 min. But i was trying to know is their any other way where we can improve the performance of this. please suggest
 
Thanks
 
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 19 May 2009 at 6:43am
outer joins, obviously, will take longer as more rows are returned...potentially. 
 
Another thought, and you should get the same results is basically to remove table a1.  you are selecting distinct, why not just move the select into the main select clause instead of building the virtual table?  The only part that would be affected is the outer join to t5.
 
Regardless, without knowing how long it takes to run on the server...1 or 40 minutes it is hard to know where to optimize.  without know if there are or could be any indices that would help, and how much data etc.
 
I think that both DBlank and I are suggesting to figure out how long the select statement takes to run...not in Crystal but on the server.  This may not be a Crystal issue, and trying fix a part that isn't broken is...difficult.  If the same query works fine in another report, then we would say that there must be something in the report that is slowing it down. 
 
If the users are set on this query, there isn't really much that you or we can do, as they want to look at the same information, but from a different angle.
 
Sorry I can't be more helpful.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.