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 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.
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:
1.
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.
2.
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 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.
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.
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.
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.
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.
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 development time later.