Best Practices

[maryb0224]

Are there any "Best Practices" for report writing?
As the DBA, I was asked to troubleshoot the slow refresh on a report written by a Data Analyst. The issue was that the end user received a new workstation, and the report was running slower than on the old workstation.

The report has three subreports and they are linked to the container using a calculated field. I know that this CAN be done, but SHOULD it be done?

The report runs in 45 minutes using the calculated field links on the old workstation.

The report is still running after two hours using the calculated field link on the new workstation.

The report runs in 11 minutes using a database field link on the old workstation.

The report runs in 22 minutes using a database field link on the new workstation.

To me - the DBA - it seems like a lot of overhead to join container reports and subreports based on calculated fields rather than database fields.

So - any "Best Practices" for more efficient report writing??

Thanks!

 

[Ngolem]

you would have to describe the report in a lot more detail.

For example...how many times are these subreports hit???

Are the select statements all being passed to the server for processing or is Crystal doing all the bull work.....

Many many issues involved. You should give details of the record selection of all the subreports and main report/subreport linkages.

Jim Broadbent

 

[akabatman2]

Hi Mary,

There are certain reasons that subreports are used. It depends on what the desired output is and where the data is and how the data relates.

However, if you are just asking for a few 'Best Practices',
I would suggest whenever possible, write a stored proc on the database server, I find that temp tables work well to gather smaller datasets and then build a final dataset to pass back to Crystal. I beleive that Crystal expects only one returned dataset. As far as I know, if the report needs more than one dataset, subreports need to be used.
It is helpful whenever possible to get the data returned from the database in 1 dataset.
The more you use the processing power of the server rather than the client and Crystal, the better for speed. I was able to improve one companies Crystal Reports processing time by a reduction of 60% by using stored procs and temp tables and passing Crystal one specific dataset with only those fields necessary. The more formulas you have in Crystal, the slower your report will run.

Jim, do you know if it is possible in Crystal Reports 9 to allow a stored proc to return multiple datasets to be used in one master report, without using subreports? I know when I try to link to an command using ADO OLE for SQL Server, I get a 'Not Supported' error.

Tom

 

[Ngolem]

Aka - I am not an expert at using Stored Procs... SynapseVampire is much better at it. However as far as reading datasets is concerned, Crystal does only one read of presented data and I believe for a Stored Proc to be valid it must present only one set of data as a result.

I know you can do marvellous database manipulations with data prior to making this presentation but the result must be a single record set.

There are so many issues with respect to speeding up a CR, most of them come down to how much work is done by Crystal and how much is pushed down to the server when retrieving the data to report.

With respect to Subreport this is of course crucial since a subreport is often repeated over and over again.

Careful evaluation of the Record Selection formula is necessary when not using Stored Procs to make sure the data collection work is being done on the server as much as possible...one classic slow point is using a formula in your record selection since this is evaluated on the second pass through the records

Jim Broadbent