SAP Knowledge Base Article - Public

1218033 - Slow performance when refreshing reports based on Command Objects linked to Tables in Crystal Reports

Symptom

  • Report is slow.
  • It takes time to refresh the report.
  • Slow report performance when refreshing a report in Crystal Reports that have linked Command Objects, and Tables.
  • Why is the report performance slower than expected? How do you improve report performance?

Environment

  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
  • SAP Crystal Reports 2020

Reproducing the Issue

  1. In Crystal Reports, create a report off any data source.
  2. Add at least one table to the report.
  3. Add at least one command object on the report in which you add your own SQL Query.
  4. Link the table(s) and command object(s)
  5. Add database fields from the table and command object to the report.
  6. When refreshing the report, it takes a long time to display the data. Why is it slow?

Cause

  • The report performance is slower than expected because command objects have custom SQL Query. When a command object is linked to a table, or is linked to another command object, two separate SQL Query are executed on the database server.
            
  • All the records from both SQL Query are returned to Crystal Reports, then the join between the command object and tables or the join between the two command objects is performed locally in a temporary file by Crystal Reports. These behaviors affect the performance of the report.

Resolution

  • To improve the report performance, do not generate a link between command objects and the tables in Crystal Reports. Instead, use one of the following methods:
         
    • Modify the command object to use the table within its custom SQL Query, and removes the tables from the reports.
            
    • Create a stored procedure that creates a single SQL Query on the server and then returns a single result set to Crystal Reports.
        
  • For other tips on how to improve report performance in Crystal Reports, see the SAP Knowledge Base Article:  1636361

 

Keywords

COMMAND OBJECT CR10 CR SPEED PERFORMANCE SLOW LINKS Crystal Reports Performance Command Objects , c2016331 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

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