Report Speed

[TEM3]

Using Crystal Reports 8.5 accessing Oracle 9i tables:

I have a series of over a dozen "daily" reports that I have created and run early each morning. PDF files are created from each report and store in a communial server folder so that the Administration and Section Supervisors can determine what occured the day before.

Some reports load slowly to Crystal Reports (they are all loading off a local hard drive).
Some report load quickly.
Some reports run slowly.
Some reports run quickly.
Some reports quickly create their PDF output file.
Some reports seems to take forever to create the PDF file.

While I know the speed that the report runs (once it is loaded) is determined by the data, the calculations, number of subreports, etc. But I do find that similar reports exhibit varied run speeds.

I also find that similar reports will load either quickly or take forever. Likewise, some reports will take a long time to create the PDF and a seemingly similar report will create the file quickly.

For the life of me, I can not determine the pattern or the reasons why.

Also, some report files ask to be saved each time the report is run, even though no changes were made to the report (and all reports are set to not save data with the report). Most report close without the save file dialog.

Anyone have any clues as to the reason for some of this? Is there something I might be able to do to optimize some of the reports??

 

[Madawc]

Do the reports save data with the report? That's an option under file and would explain some of it.

For running, take a look at the SQL, that's an option under Database. If some of your selections are not shown there, they are done by the local machine and not the server, which is inefficient.

Also check how many records are being read. (I assume you've inherited these reports and did not write them.)

Subreports slow a report because each instance of a subreport needs a separate data access.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[TEM3]

All reports are set to NOT save data with the report.

I created all the reports (over the span of a couple years).

I will check the SQL....

Thanks.

 

[lbass]

thread149-1276307 might help.

On the issue of the report asking about saving. You might be using some kind of currentdatetime function somewhere in your report that is causing this. Just a guess.

-LB

 

[ImArchimedes]

May I also suggest you check your linking styles. You can have two reports with the same tables and one with unnecessary inner joins and that one will run in twice the time.

 

[ImArchimedes]

Also, running views instead of tables that are simply queries have been known to bog things down. If you have a report running off queries, try swapping out for tables, linking outer instead of inner when possible, and then see if that runs faster. Hope that helps.

 

[smcnulty2000]

There is probably more than one answer. Especially since these were built over a long period of time. And that you are seeing slowness at different stages of your process.

Maybe if you pick one of these to talk about we could look at it specifically? Your slowest? Then what you learn from that you could try and apply to the others. Then bring back your next slowest.

Also; an oracle database isn't a uniform thing. Some stuff in it will run faster than others.

I like to copy my sql statement out of crystal and plug it into a sql query tool to see how well it runs there. Usually this tells me quite a bit about where to look next.

Performance tuning is more of an art than a science.