Imran Alam's World

I have worked on various Crystal reports version starting with Crystal Reports 7.5 back in year 1999 to latest Crystal Reports 2016 & Crystal reports for Enterprise. It is one of the best tool to design pixel perfect reports. When it comes to performance, lot of developers think that it is NOT up to the mark and has lot of issues when working with large volume of data. This however, is perception of developers and lack of knowledge of “how” to design the reports in Crystal Reports.

This can be said about any tool, if it is not properly designed, it is bound to give you bad performance. Coming to Crystal Reports, its no different but people tend to think that anyone with little bit of knowledge can create reports in Crystal Reports and hence we see these un-professional designed reports which becomes pain to maintain with the kind of performance. So, let me get to what are some of the “key” things which you should be looking at while designing or reviewing a report which needs improvement on performance.

It is important to understand how the crystal reports engine works and what is “multi pass”. From the moment you hit “refresh” button, crystal reports engine does multiple things, first it generates the query needed to run on the server, retrieve the records, print it on page etc. So, if your report design includes connection to DB, formula fields, sub-totals, running totals etc, you need to understand at what point they are being called and control in such a manner that these are executed at precisely when “you” want and how much of data scan it will be doing back on the database tables.

1. Reading and extracting the records which are “not needed” to display from the Server

**The biggest impact will be what is being passed to the server as query using “WHERE” clause. When you do Database –> Show SQL Query, you will be able to see the query which is used to run on the database and that becomes your “volume” of data which is served to the report.

Say, for example, your report is designed to list customers in City of New York, you should be able to see that in your “where” clause that the query is bringing only the customers of New York, if not, you know that there is problem with your report design, your report might be scanning the entire table of “customers” reading and retrieving the entire list of “Customers” instead of reading the ‘customers’ belonging to city of New York.

If you “do not” see that in where clause, your report is reading say 500 million records maybe and then locally filtering to show 2000 customers from New York. This design is “worst” to have as you are wasting time in retrieval of the data from Server, passing “more than needed” data along your network and then preparing your report after filtering on the report pages. This is the “most” common mistake which developers do when designing report against base tables or views or sometimes even against ‘command’ based reports…they created the filters and parameters inside the report and not in the command..

Multi Pass Reporting Diagram (Can read more in HELP of CR designer)

2. DLLs used in your Connection

Make sure you are using the latest drivers whether those are “native” connections or “ODBC/JDBC”. Having these upgraded to work against the exact version of the database server helps. I remember helping one of the client which was using a Microsoft ODBC connection to run the reports against Oracle, just changing the ODBC connection from MS ODBC to Data direct drivers (CR drivers provided within Crystal reports installation) improved the performance of the report. The report which use to take 30 minutes or so, I was able to get it to run it under 5 minutes, just by changing the driver.

3. Indexing the columns on Database

This will not be my “first” option to go with if you are having significant issues, nevertheless it helps to make sure that the fields you are using for “filtering” or “having prompts” on are indexed so that it can directly go to the selected list of data needed for the report.

Troubleshooting:

When you get a report assigned to look for performance issues, first thing you should do is extract the query the report is running, do a database –> show SQL query, go over the things mentioned above and if they all are “checked” and you still are not satisfied with performance, then run this query against the a) same environment of the database used for report b) execute this query using the same user you have used to run the report, then compare the time it takes in your query analyzer tool of database to the actual report. They should be very similar. If the query takes 10 minutes to run in “toad” against oracle and report is taking 11 minutes, then the problem is “not” with the report design but with the query/database itself, you can then work with DBAs on making sure the query performance is looked at first and then the report.

Basic thumb rule to follow is that add 10 percent of more time for report to display than the amount of time the query takes to run against the database. Crystal reports has come long way and SAP has done tonnes of improvements on how the pagination works and it starts showing the initial pages even when the query is running in the background and delivering the rest of the data.

Btw, these concepts are not just limited to “Crystal Reports” but can be applied for any reporting tool out there.

Goodluck and comment or email me if you have any questions.