How to Speed Up Reports and Prevent Memory Crashes in Crystal

[DellSC]

SAP Crystal Reports (CR) is very dependent on the amount of RAM and disk space that's available when reports that use large datasets are run.  Crystal doesn't use Windows swap space when it fills up RAM.  Instead, it creates temporary report files with filenames like "~*.rpt" in the current user's temp folder.

If CR crashes, the temp file for the report that was running does not get cleaned up.  This file might be very large, so it's very important that the file is manually deleted if this happens.

If a reports are not connected to a file based database such as Access or Excel files, there are things that can be done in the report design to make things run faster and help prevent crashes due to memory issues.

1. Whenever possible, filter data in the Select Expert when joining tables together or in the Where clause of a Command (SQL Select statement) instead of suppressing data in the report.  This will limit the amount of data that comes through the network from the database, so there is less data to load into memory.


2. When using the Select Expert, do not use Crystal formulas or If/then statements in the selection criteria.  The use of this will cause CR to pull data into memory and filter it there because these things cannot be pushed down to the database for processing.  To replace If/then statements, use a combination of "and" and "or" with the appropriate parentheses.  For formulas, often creating a SQL Expression that does the same thing using the syntax from the database will work well.  Remember, SQL Expressions can only return a single value!


3. When using a Command to get the data for a report, DO NOT use the Select Expert to filter data.  Also, a report (or subreport) should use a single Command to pull all of the data for the report - do not join multiple Commands or tables and Commands.  For more information on this see https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/.


4. If a report is displaying only a summary of a large dataset, consider using a Command to summarize the data in the database instead of having CR calculate the summaries.  CR does the calculations in memory and, if data has been swapped to disk, it has to pull data into memory to do this.  This can cause a report to be very slow or to CR to crash if there's too much data.  By pushing the calculations down to the database, not only is less data returned over the network, but the database server is optimized to run these types of summary calculations more efficiently.