Increase Report Performance

Here are the five most likely causes of poor performance and the steps you can take to overcome them.


Step 1: The Database

This may or may not be within your direct control. The fact is that not all databases are set up in an optimum way. The two commonest faults are:

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 faster simply by adding an index to an important field (important to the report filter, that is).

Using a view rather than a table. This can have a devastating effect on a report's performance. This is mainly because views don't have indexes. A view might be a collection of tables or a subset of rows or columns (much like a basic report); it's often used to simplify data for end users.

The only way to avoid the problem is to base the report directly on the underlying tables rather than the view. You can identify whether the source of a field is a table or a view by looking at the Database Expert, as this lists tables and views separately.

Identifying which tables make up a view can be trickier and you may need the help of the database documentation.

Also, with some databases (notably Oracle), turning off the case sensitive option on queries can really speed up reporting times, although this may require existing reports to be amended.

Step 2: The ODBC driver

ODBC drivers are used by Crystal Reports to attach to the database. There are usually several ODBC drivers available for a given type 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 with the database is usually the best option.

Step 3: Sub Reports

Each sub report is like another report accessing the database, and if that subreport is placed in the Detail Section it will run for every record the main report loads. Even if it's placed in a Group Section the subreport could be run many times.

Report Sections are usually the ideal place for 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 overcome the performance issue caused by sub reports is to not use them. Ninety-nine percent of subreports are not necessary and the same result can be achieved using other methods such as grouping, running totals, formulas, etc.

Step 4: Table Linking

Anything other than a link type of equals ('=') will cause a massive degradation in performance.

The Link Options window (accessible through right-clicking on a specific link) will allow you to change the link type.

If there is a need for this type of link, the same result can be achieved through group selection or formatting (and hiding the unwanted records) once they are loaded into the report.

Step 5: Record Selection

When the 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 Select Expert will guarantee that any filter created will be evaluated on the database and be as efficient as possible.

An additional point which can make a difference in some cases is the time that the report is scheduled to run. Heavy network traffic or database usage can impact a report's running time.

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.