Crystal Reports: 5 Tests for Top Performance
by Jason Dove,
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 summarized and displayed in a myriad of ways.
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 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
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)
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.
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
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.
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.
The Link Options window (accessible through right clicking on a specific
link) will allow any values to be reset.
If there is a need for this time 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
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.
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.
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.
Jason Dove is a top Business Intelligence consultant and author who has
shared his expertise with some of the most respected businesses in the
world. Crystal Reports is his reporting software of choice and the subject
of his book "Crystal Reports Formulas Explained" which is the most advanced
book on the market. Currently available with a free 70 page Crystal Reports
This article is copyrighted by
Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be
freely copied and distributed as long as the original copyright is
displayed and no modifications are made to this material. Extracts are
permitted. The names Crystal Reports and Seagate Info are trademarks owned
by Business Objects.