SAP Knowledge Base Article - Public

1218033 - Slow performance when refreshing reports in Crystal Reports based on command objects linked to tables

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 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016

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 statements. When a command object is linked to a table or is linked to another command object, two separate SQL statements are executed on the server. However, the linking is not done on the server.
          
  • All the records from both SQL statements 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 BusinessViews Manager , Problem

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016