We have all been there. Staring at the screen, watching the little counter, waiting as Crystal Reports takes its time exporting what should be a simple report to a file format of your choosing. Worse still, the report takes so long that when you schedule it to run, you have to schedule it to run in the middle of the night just so the data is ready when you walk into the office in the morning.
In this post, I am going to share with you 6 tips on how to really speed up your Crystal Reports to run in seconds instead of hours.
1. Use a Stored Procedure: running your Crystal Report based off a stored procedure will improve speed by the simple fact that stored procedures are cached on the server as are their execution plans. This means that your server is ready to return the data to your report by the time your report requests it. It also means that the query processing will occur on the server instead of the client and typically servers have more processing power.
2. Avoid Using Formulas or Crystal Functions In The Record Selection Formula: Here is how a normal Crystal Report works when it is executed; the record selection formula is parsed and converted to a standard SQL query. That query is then run against your database to get the data. This is a good thing because if you have thousands of records in your database tables, only the records that match the SQL query are returned to Crystal Reports. Guess what happens once you place a Crystal formula inside the record selection formula? The entire process described above is thrown out of the window. Crystal is no longer able to parse the record selection formula into a full SQL query and will create one with NO SELECTION CRITERIA at all. If you have thousands of records in your tables, Crystal Reports will bring them all back and one by one, test them against the record selection formula (since it has to evaluate any formula within it first). This is by far the biggest cause of slow Crystal Reports and removing formulas from the Crystal Reports selection formula can yield dramatic increases in processing speeds.
3. Avoid Putting Database Fields or Formulas Inside Textboxes: This is self explanatory, report processing speed will increase if you place formulas and database fields outside of/next to textboxes instead of dropping them inside the textbox.
4. Avoid OLE Objects: The more OLE objects you have in your report, the slower it will run as it the report has to do more processing at run time.
5. Choose Native Over ODBC Database Connections: ODBC connections are great in that they can be shared among reports and applications. They provide a single place to manage database connectivity. Compared to native connections however, ODBC will always return run slower as information must be “translated” by the ODBC driver before being sent to your database for retrieval, and then again on the way back, the returned information must pass through the ODBC driver to be translated into your report.
6. *BONUS* Supress Sections With No Drill Down: If you have any sections in your Crystal Report that you no longer need displayed, not even for drill down, then choose SUPRESSS (no drill-down) instead of just SUPRESS. This is because in the latter, the fields in the supressed sections are still processed by the report.
I hope this post will help you making your report processing faster and more efficient.
