SAP Knowledge Base Article - Public

1636361 - How to design reports in Crystal Reports to improve performance?

Symptom

  • How to improve report performance?
  • How to reduce the time a report takes to run?
  • How to enhance the performance of a report in Crystal Reports?
  • When viewing a report in Crystal Reports, or in SAP BI LaunchPad, it is slower than I would like. How to run the report faster?

Environment

  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
  • SAP Crystal Reports 2020
        
  • SAP Crystal Reports for Enterprise 4.1
  • SAP Crystal Reports for Enterprise 4.2  
  • SAP Crystal Reports for Enterprise 4.3   

Cause

  • Many factors affect a report performance, like the computer specification, networking, database server,... but here we will concentrate on the report design factors that can affect the report performance. Below is the general list:
                 
    • Data
      • Record Selection
      • Unlinked Tables
      • Linking Tables on Non-Indexed Fields
      • Complex Queries
      • Grouping
           
    • Objects
      • Subreports
      • Page N of M or TotalPageCount
      • Unused objects and sections
      • Running Total objects
      • Large graphic files
             
    • Formulas
      • WhilePrintingRecords
      • Shared Variables
      • Repeated Calculations
      • Formula Loops
         
           
  • In the Resolution section, we will list the factors that affect performance in more details, with suggestions on how to help design the report to improve performance.

Resolution

Category Feature Potential Problem Recommendation
Data

Record Selection

Records will be filtered locally if there are statements in the Record Selection which cannot be transaled into SQL Query. This should be avoided especially with large data sets,as it results in unneeded records being retrieved from the database.

Ensure the Record Selection Formula can be translated in SQL Query, by avoiding using complex formula.  For example, avoid using IF THEN ELSE.
    
SQL Expressions can be used instead of complex formulas in the Record Selection formula.
Data Unlinked Tables

A cross-product of all unlinked tables will be created. This is generally unsupported unless the unlinked table contains a single record such as a logo.

 Subreports should be used to retrieve and display data that is unrelated to the data in the main report.
    
Data

Linking Tables on Non-Indexed Fields

Results in a longer running query to the database.

 Link only on indexed fields where possible.
   
Data Linking Tables to Command Objects or Stored Procedures
   
When linking Tables to Command Object, or Stored Procedure, it will send multiple queries to the database:
- One SQL Query to retreive the data set for the Tables,
- Another SQL Query for the Command Object, or Stored Procedure call, to retreive the data for those objects.
  
Then it will link all the data sets received from the Tables, and the data set received from the Command Objects or Stored Procerdure, in the temp directory on the local computer where Crystal Reports is installed. This will cause performance issues, and if you have large data set, it could cause out of memory issue.
     

Instead of linking Tables to a Stored Procedure, or Command Object, use only 1 Command Object, or 1 Stored Procedure on the report, and no Tables.

In the Command Object or Stored Procedure, include all the Tables that were used on the report, and all the filters, so all the data processing is performed on the database side, and it only send 1 data set to Crystal Reports.
Data Complex Queries Overly complicated SQL statements can increase processing time for the database before records are returned.
     

Keep the SQL Queries to the minimum required for the desired result. In some case, it is possible to optimize your SQL Query by creating your own SQL Query in a command object.
Data Grouping By default all grouping is done locally meaning all records are retrieved from the database and groups then created. This can result in unneeded records being retrieved from the database.
      

When only interested in groups and summaries turn on Perform Grouping on Server and suppress the Details section to utilize the Group By SQL clause to push grouping down to the database and limit records returned.
Objects Subreports

Subreports are a report within a report and can be expensive to process. If they are placed in the details section they will be processed for every record, and therefore send a SQL Query to the database every time it is executed. If you have 2 subreports in the Details section, and the report returns 100 records, then it will send 200 SQL Queries to the database. ( 2 subreports times 100 records = 200 SQL Queries )

Use subreports carefully and only for very specific purposes such as when an unrelated query to the database is needed. Avoid placing them in a repeated section such as details or groups whenever possible.

Review attached solution (Sample Report - Shared Arrays.rpt) to move subreport out of Details section
Objects

Page N of M or TotalPageCount

The function "Page N of M" and "TotalPageCount" require all pages to be counted before they can be displayed.

 Only to be used if it they are a hard requirement and not a nice to have.
     
Objects

Unused objects and sections

 Unused sections and objects cause wasted processing.
     

Suppress unused sections and remove unused objects especially tables, formulas and running totals.
Objects

Running Total objects

Running Total Objects require significant resources to maintain their state.

Use formulas for calculations instead of Running Total objects.
Objects

Large graphic files

Graphic files are stored at their original size even though they are rendered in the size set in the report. This can lead to large report files and slow processing.

Graphic files should be resized to the desired size outside of Crystal Reports and then inserted into the report.
Formulas

WhilePrintingRecords

 Forces a formula to process when the report is viewed which can add significant unexpected processing when viewing an instance. 
     

Should only be declared when explicitly require processing at view time.
Formulas

Shared Variables

 Causes a formula to process when the report is viewed which can add significant unexpected processing when viewing an instance.
    

Should only be declared when a value needs to be passed between main and sub report.
Formulas Repeated Calculations

Repeatedly updating a Global Variable with the same value wastes resources.

 Constant values should be calculated once and assigned to a Global Variable.
     
Formulas Formula Loops

Placing a formula containing a loop in the details section will cause all iterations of the loop to be executed for every record.

 Use Formula Loops carefully and only when there is no other option. Most string parsing can be done with the built in string functions. Avoid placing formulas with loops in a repeated section such as details or groups whenever possible.
        

Keywords

Slow, Delay, Optimization, Long Time, CR, report performance, slow report, report takes time, faster, quicker, shorter, CRFE, CR4E, CR for Ent , KBA , report takes a long time , slow report , crystal reports performance , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020

Attachments

cr9_evaluationtimes.pdf
Sample Report - Shared Arrays.rpt