Crystal Reports 2011 running some reports slowly

[Former Member]

I have a few reports that pull from views with over a million records. We recently upgraded from Crystal Reports 10 to Crystal Reports 2011. Once we made this change these reports started taking a couple of hours to run. I am not sure what version these reports were originally written in as I inherited them. I have tried a number of things to get these reports to run faster but I wanted to see if anyone had any suggestions to help these reports run faster. Here is what has been tried:

• Redoing the View so it runs all the Formula's and SQL (instead of Crystal Reports running them)
• Had all extra columns not needed removed from the views
• Had joins done in the Views where possible.
• Recreated the report and all subreports (there are about 7 and the database developer says he can't lessen that).
• Shared NumberVar between the main report and the subreports

Are there any other solutions to help these reports? Thanks!

[former_member11696 · Accepted solution]

Hi Becca,

A few things to check off, open the report and go into Report Options and check off the Verify options, not required now that the reports are completed.

Make sure you are using the updated database client also.

Manually set location to the Data source again, if it's Oracle set location for each table, if used, and do the same for all of the subreports also. A delay could me CR is trying to find the old one also.

What DB are you using and which connector, OLE DB, ODBC, Native, etc.?

And check on No printer in the Page Set up to see if that helps, could be CR is trying to find the printer the reports were designed against.

As for updating your View, it's always recommended to do as much data processing Server Side, after all DB Server are designed to do this, Basically CR is designed to simply display the data...

The Processing you can do Server Side the less your local PC and CR have to do.

Go into Options and Database and check off the Auto Link also, this can cause delays when opening the reports also, CR will attempt to link all tables and fields. Although I don't believe this is the case for a view.

Don

[Former Member · Accepted solution]

Don-

Thank you for all the suggestions but the report still took literally 2 days to run.

Is there anything that would cause this report to run fine in Crystal Reports X but run slowly in Crystal Reports 2011?

-Becca

[former_member11696 · Accepted solution]

Need more info,

What DB are you using?

What Client and version?

How are you connecting, OLE DB, ODBC, Native or ????

How many subreports does the report have and where are they, which sections?

Can you trace the DB Server and see how long it takes to process the Request for CRW32.EXE?

We also need to compare what is being sent to the Server from CR so we need log files for both CR 10 and 2011 to see what the differences are, if any...

Bottom line is you need to enable logging, it will introduce more delays so stop it after 5 or 10 minutes, we can guess for a long time but we really need to see what the logs say is going on.

Don

[Former Member · Accepted solution]

What DB are you using? We are connecting to an Oracle 10g database

What Client and version?Oracle 11g

How are you connecting, OLE DB, ODBC, Native or ???? We are connecting through the Client and the TNS file.

How many subreports does the report have and where are they, which sections? There are 9 subreports and they are all in the Detail(s) section of the report.

Can you trace the DB Server and see how long it takes to process the Request for CRW32.EXE? From PL/SQL Developer the Main reports SQL runs in about 5 secs.

I have logging enabled on my Business Objects Server (sorry for the simple question) but is that where I can find the logging needed? On the server these logs are found at: C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\logging . Or is there a way to set up Logging on the Crystal Reports Client?

[former_member11696 · Accepted solution]

HI Becca,

WOW 9 subreports in the detail section.... Not sure what the differences are in the report designer, could be some report option or registry key that CR uses that either is not on set for CR 2011 or some other configuration...

Try using a tool called ProcessMonitor, it's from Microsoft and you can compare logs and look for CR registry keys being used. The registry tree will be different but the name and values will be the same.

Not sure which BOE Server logs will show you what is happening but if you have a support contract I suggest you log a case in Service Market Place and get a rep to investigate. Something is definitely not configured the same way.

What is happening when using subreports in the detail section, example:

Your main report has 100 records returned, for each record the value is passed to each subreport which then runs the query on the server, again or could be a new SQL. Once it's evaluated then the second record of the first 100 is the run again in the subreport.

So for 100 records returned the subreport runs 100 time X 9 subreports = 100 x ( 100 x 9 ) = 90000 queries being sent to the DB Server.... and that's only with 100 rows of data in the main report... And you say there are a million rows being returned ????? that would explain the delay...

Process is simplified of course because not sure how the subreports are liniked or supressed etc...

Bottom line is you need to optimize those reports and if possible use Server Side processing for the data. Add more filtering Server Side, a million rows is not all that much data but it all depends on how the subreports are set up.

Have a look at the report again and if subreports are not required, you can usually replace them with Groups, and those groups can be filter Server side...

Thanks again

Don

[Former Member · Accepted solution]

Don-

I feel a little sheepish. After your last response I began thinking and 9 subreports in Details is alot. I looked into it and moved all the subreports to the Report footer and now the report runs quickly. Thanks for all your help!

-Becca