No matter what your reporting requirements are, at some point you will probably print a report that takes longer to process than you would like. And you’ll scratch your head and think to yourself, “There has to be a way to speed this up.” And you are right. In most cases, there are various techniques you can use to make a report run faster. In this chapter we will look at the most common ways of improving performance. We also look at a few SQL tricks that make your reporting job easier by using the power of the SQL language.
When a report runs, there are three potential areas where bottlenecks can occur. First, the database server has to locate and process all the records prior to passing them across the network. At the same time, the database server is also doing work for other people on the network. You could have a report that is normally very efficient suddenly start running very slowly if someone else sent a large data request to the server at the same time. Fortunately, database servers are optimized to do this processing as efficiently as possible and this generally isn’t the reason for most performance problems.
Second, the database server has to pass all the records to the client computer. The speed at which these records are transferred is constrained by the physical limitations of the network design as well as the traffic already on the network. At a smaller company, the infrastructure might not support transferring thousands of records very quickly. At a larger company, the network is certainly more powerful, but now you are competing with everyone else who is passing files back and forth along the same network pipes.
Lastly, once Crystal Reports receives all the records, the report engine has to process them to create the report. Depending on the complexity of the formulas, sorting/grouping and whether subreports are used, this can be a very time intensive process.
As the report designer, your goal is to analyze each aspect of the reporting process and see if you can make changes to reduce the impact that each of these three potential bottlenecks can have.
The solution for improving most report performance is to transfer the workload from the client machine to the database server. This is called “pushing down data” to the server. Pushing down data derives a big improvement in performance by having the server perform the majority of the data processing. As mentioned in the beginning, database servers are designed to provide the optimum performance when processing massive quantities of data. It can filter out records that don’t meet a certain criteria in a fraction of the time that the report engine would take. Pushing down data has the secondary benefit of passing less data along the network back to the client. The report gets the data quicker and there is less data for the report to process. You have now reduced the impact of two out of the three possible bottlenecks and your report will run much faster.
If you are requesting an extremely large volume of data from the database server and this is causing performance problems, you should look into scheduling the reports to run overnight. This lets the database server dedicate its resources to working specifically on your reports and it doesn’t cause performance problems for other users who are running smaller reports during the day. You can upgrade to the enterprise edition of Crystal Reports or look at cheaper third-party solutions that perform report scheduling.
The key piece to the performance puzzle lies in the SQL statement. By understanding what is going on in the SQL statement, you can find areas for improving performance.
In the previous chapter, we discussed how the SQL statement is broken down into three primary parts (SELECT…FROM, WHERE, and ORDER BY/GROUP BY). We are going to break down each aspect of the SQL statement and see how we can change the report design to improve the SQL statement. Since the SQL statement is how Crystal Reports talks to the database server, making it more efficient results in faster performing reports.
