Consultancy and Programming
By Jason Dove
It is complete, your masterpiece report. Not only does it meet your customer's expectations, it blows them out of the water. Everything they want is beautifully summarised 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 running time 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 you can do to combat poor performance, any one of which can greatly speed up your reports.
Here are the five most likely causes of poor performance and the steps you can take to overcome them.
This may or may not be within your direct control. The fact is that not all databases are set up in the optimum way. The two commonest faults are:
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.
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.
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 it's placed in a Group Section the sub report 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 sub reports are not necessary and the same result can be achieved using other methods such as grouping, running totals, formulas, etc.
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.
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 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.
About the author: Jason Dove is a senior consultant at Scry Business Intelligence and an instructor who has specialised in Crystal Reports and Business Intelligence his entire career, utilising it for everything from selling paint to counter-terrorism. He has provided Business Intelligence consultancy for some of the world's leading companies and is currently making the same service available to smaller businesses. He is also the author of Crystal Reports Formulas Explained, the most advanced book on the market which specialises in formulas. It's currently available with a free 70-page Crystal Reports XI tutorial. Expertise: Crystal Reports, Business Intelligence, SQL, ITIL. Email: Jason.Dove@scry-business-intelligence.com. Phone: 447779723043
Mike Lewis Consultants Ltd. February 2010.
These pages are maintained by Mike Lewis Consultants Ltd. as a service to the CR community. Feel free to download and use any code or components, and to pass around copies of the articles (but please do not remove our copyright notices or disclaimers).
The information given on this site has been carefully checked and is believed to be correct, but no legal liability can be accepted for its use. Do not use code, components or techniques unless you are satisfied that they will work correctly in your applications.
© Copyright Mike Lewis Consultants Ltd.