It is complete, your masterpiece report. Not only does it meet your customer's expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways.
Then....
Disaster!
You try to run the report for a month against the live database and not the two days test data you used for development.
Suddenly your report's runtime goes from twenty seconds to two hours.
Every experienced Crystal Reports developer has experienced this situation and it can be one of the most frustrating aspects of report design.
Thankfully there are a variety of things that can be done to combat bad performance, any one of which can reap huge benefits.
Here are the five most likely causes of poor performance and how to mitigate their effects.
1. The Database Set Up.
This may or may not be within your direct control to alter, but databases are not set up ideally.
Two top contenders are:
a. The fields you are filtering on are not indexed. You can check whether or not this is the case by referring to the Linking Tab in the Database Expert window. Indexed fields have colored markers next to them.
I have personally seen reports run hundreds of times quicker due to the addition of an index being added to an important (to the report filter) field.
b. Using a view rather than a table to report from can be devastating to a report's performance. This is mainly due to views not having indexes. A view is a collection of tables (much like a basic report) and is often used to simplify data for end users.
A report based solely on a view can be very efficient, but once that view is linked to a table the performance nosedives dramatically.
The only way to avoid this is to report on the tables which make up the view. Identifying whether the source of a field is a table or a view can be done via the Database Expert as tables and views are listed separately.
Identifying which tables make up a view can be much trickier and you may need the help of the database documentation.
Also, when using Oracle databases, turning off the case sensitive option on queries can really speed up reporting times but may require existing reports to be rewritten.
2. Using the wrong ODBC driver.
ODBC drivers are how Crystal Reports attaches to the database. There is usually a variety of ODBC drivers which will work for any particular make of database and some are better than others.
The only way to really test this is to run the report with all the suitable ODBC drivers and see which is the most efficient.
Experience has taught me that the ODBC driver provided with the software associated to the database is usually the best option. These should on your develop PC already and will appear in the list of available drivers. If not, installing the software which uses the database will also install the driver you need.
Note: while the basis for every database connection is an ODBC driver, they can be wrapped up as part of a DAO, OLE DB(ADO) or JDBC so it pays to look through these options as well as the basic ODBC(RDO) when trying to identify the most capable option.
3. Excessive Use of Sub Reports
Each sub report is like another report accessing the database, and if that sub report is placed in the Detail Section it will run for EVERY record the main report loads. Even if placed in a Group Section the sub report will still be run numerous times.
Report Sections are usually the ideal place to home a sub report as they will only run once. But this still turns one report into two as far as performance is concerned.
The best way to negate the performance issue caused by sub reports is to not use them.
Ninety nine percent of sub reports are not necessary and the same result can be achieved using other methods through grouping, running totals and / or formulas.
4. Table Linking
Anything other than a Link Type of equals ('=') will cause a massive degradation in performance beyond the accepted overhead of returning extra fields.
For example, using a '>=' link to return records with a date greater than the date in the parent table can be realised more efficiently by linking on ID and then comparing the date fields in the Record Selection.
If there is a need for this type of link, the same result can be achieved through the Group Selection or through formatting (and hiding the unwanted records) once they are loaded into the report.
5. Record Selection
When code for the record selection is written correctly, Crystal Reports will pass all the logic to the database as SQL and only return the data needed.
If the record selection is not written in an SQL friendly way, Crystal Reports will bring back all the data and then filter it locally. This can be drastically slower than when calculated on the database.
Using the Record Selector Expert will guarantee that any filter created will be evaluated on the database and be as efficient as possible.
Obviously, writing the report filter as a Record Selection formula is the easiest method for creating complicated criteria, but once finished the Record Selector Expert can be opened and the formula viewed to identify which parts are SQL friendly (or not).
If the formula criteria is displayed as a tab at the top of the Record Selector Expert window, all is fine.
An additional point which can make a difference in some cases is when the report is scheduled to run. Heavy network traffic or database usage can impact a report's running time.
To measure a reports performance and identify any specific impacts to efficiency Crystal Reports has a tool which identifies how long each part of the process takes to run. This is imaginatively called "Performance Information" and is accessible through the Report menu.
This helpful instrument makes it possible to locate the weak links in the report and enable fine tuning.
Working through the above points will enhance the efficiency of your slowly running reports. Building your reports with all this in mind from the beginning will save you redevelopment time later.