crystal report low performance

[Former Member]

Hi,

I experienced some critical performance issue with some Crystal report. The report who got issues contains 25 sub-reports.

I'm using Crystal report 2013 (SDK 13_0_13) for Visual studio.net. ODBC connection towards an Oracle 11g database.

Is there a know fix for this problem (instruction, command) or even a patch to solve this issue ?

Thanks a lot.

[DellSC · Accepted solution]

That's a LOT of subreports.  What type of sections are the subreports in?

By their very nature, subreports will slow down a report.  EVERY time a subreport is generated, Crystal will connect to the database and run the subreport query.  So, if the subreport is in the report header or footer sections, it will run once every time the main report is run.  If it's in a detail section, it will run for every record from the main report.  If you have 1,000 records in the main report, the subreport will run 1,000 times!  There is NO way to share the data from one instance of a subreport to the next.  So the speed of the report is very much affected by where the subreports are located.

Some things you can do to speed things up within a Subreport:

1. Pay attention to where the filter on a subreport is processed - you want it to process on the database, not in Crystal.  One way to look for this is to go to the Database menu >> Show SQL Query.  If there are any conditions in the Select Expert that are not in this query, then they're being processed in memory by Crystal instead of being pushed to the database.  Some things that might cause this:

     a.  Filtering the results of a Stored Procedure.  All data filtering should be done through parameters to the SP.

     b.  Filtering based on a Crystal formula.  These can't be pushed to the database.

     c.  Filtering a command in the Select Expert instead of using parameters in the Where clause of the command.  NOTE:  These parameters have to be created in the Command Editor, NOT in the Field Explorer of the subreport.

     d.  Using the suppression of sections to filter out records that you don't want to display.  Instead, you need to find a way to filter them out at the database before they are pulled into the subreport.

2.  Efficiency of the query.  Crystal doesn't always create efficient SQL when linking tables together.

     a.  Make sure that you're only using tables that are directly needed to bring the data into the subreport - the fewer tables, the better.

     b.  Many times a Command, Stored Procedure, or View will be more efficient than linking tables.

-Dell

[former_member183750]

Why post the same query twice?

Once really is enough as per the

Please pick one Discussion to continue on and I will lock or delete the other one. But do read both Dell's and my replies to you and respond to both - in Discussion of your choosing.

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

  Follow me on

Twitter