When creating reports using Crystal no matter what version there are some general practises that you should adopt in order to ensure that the reports when executed will perform in a manner that is both acceptable and with the minimum possible server resources used.
Before we begin talking about best practises we first need to appreciate how the Crystal report runtime engine works from a high level so that we can understand the number of stages involved when generating a report.
Multi-pass reporting engine
Figure 1 below describes the multi-pass reporting engine that Crystal uses.
So lets take a look at some of the key phases in the above diagram from a performance rather than a functionality point of view.
- In the Pre-pass phase only constant formulas are evaluated for example 3+4=7, these formulas are evaluated before any records have been read from the database and are not re-evaluated. The event “BeforeReadingRecords” is fired at this time, I shall cover Crystal events in another post as that’s a subject in itself.
- Next comes the the first pass where the data retrieval, recurring formulas, local selection formulas, sorting & grouping and then the final step is to save the datagenerated by the end of the phase to memory or temp files. In terms of performance there are two things to note in this phase,
- firstly all the dynamic formulas are evaluated these are the formulas that use database fields for a calculation so the more formulas in the report the more the report server has to work, if possible its best to do the formula calculations at the database level before Crystal gets the data
- and secondly the local selection formulas are the selection formulas that are too complex for Crystal to push down to the database server what happens if this occurs is that the whole result set will get returned to the report server where it is then filtered, this normally happens when the selection expression wasn’t created using a proper SQL format. If the whole result set is returned to the server for filtering not only will it consume additional CPU and RAM but also temporary disk space as Crystal will need to cache some data whilst doing the filter. The event “WhileReadingRecords” is fired at the end of this phase.
- Pre-pass 2 this is where the group sorting (not to be confused with the record sorting) and any hierarchical grouping occurs, ie group 2 belongs under group 1, etc…
- Then comes pass two which includes, group selection formulas, running total calculations, print time formulas ie user created running totals, subreports and charts, OLAP and cross tab grids and page on demand which handles as you may guess new page initialisations. In this phase the major area to concentrate on is subreports which we will cover below. The event “WhilePrintingRecords” is fired at the end of this phase.
- The third and final pass does the page count calculations. This is where the data comes from where you specify “Page N of M” from the special field options, if you don’t need “Page N of M” then don’t use it that way you will save on doing pass three and speed the report up a little.
Quick definition: a subreport is a Crystal report that is embedded in another Crystal report which is linked by some common values. The above multi pass engine gets executed against each subreport that you have attached to your main report because as the definition says these subreports are just another Crystal report, and in addition each subreport will do a separate database operation in order to retrieve any required records thus putting more load on your database and report server, so it goes without saying that the fewer subreports that you have the better for performance.
If you have a section in your report that you only want to print in a specific circumstance then you should use the “suppress section (no drill-down)” option rather than just “hide section”, this is because when you use “hide section” Crystal still calculates and saves the drill down information where as with the suppress section (no drill-down) it doesn’t and just skips over the section.
In the options menu in Crystal (FileOptions…) on the database tab you will see under advanced options an option called “Use Indexes on Server” this should be ticked as default and you should check that it is. If Crystal is querying the underlying database tables directly then this tells Crystal to try and use table indexes if they are present to quicken the read process up. You should also double check that the table you are querying has an index that covers the fields that you are filtering on, this is important as if it doesn’t you will start doing table scans which will drastically slow down your read times and if you are not careful this may lead into database locking issues under certain circumstances.
By far the best way to get data into your report is to use a Stored Procedure. With the stored procedure you can manipulate, group, sort and retrieve the data at the data source. Database engines like SQL server and Oracle will also cache query plans and help determine the best strategy for returning your data back into Crystal. By using a Stored Procedure you can also you more fine grained isolation control on the tables such as using the NOLOCK table hint to endure that the database engine does not hold any locks against your read (see my NOLOCK post for more information on this). Also by using a stored procedure you may be able to limit the number of subreports as you have a lot more flexibility to do sorting and grouping tasks, etc…
More and more often there are occasions where a Crystal report that I have been asked to take a look at is based on a Stored Procedure which is great but then someone has edited the report and added a subreport to return an additional field from the same table that the SP is based on rather than change the SP itself, now the database tables are getting hit twice for a read when in real terms it should only be once. Always though it is the same reason why the subreport was added rather than the SP changed and that is the person editing the report was not comfortable with SQL code and changing SP’s, but in my opinion if you intend on doing report writing against enterprise databases then one of the core skills that you should have is SQL knowledge and how queries are interpreted and executed against the data store.
Report Performance Metrics
Once you execute your report from with the Crystal editor performance metrics are captured and you can view these in the Performance Information dialog box which is under the “Report” menu. In the dialog you will see your report information along with all the subreports that you may have on your report, remember from earlier that each subreport is a report in its own right. From here you can see things like how many database fields are in your report, number and different types of formulas used, how long the report took to execute, how long the database query took, etc… For more assistance on what all the different metrics mean check out the online help in Crystal its pretty good but most of them are self explanatory.
Have fun …