1260284 - Slow performance when reporting from multiple data sources in Crystal Reports

• Report is slow.
• Reports takes a long time to refresh.
• Slow performance when reporting from multiple data sources in Crystal Reports.

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

1. In Crystal Reports, create a report on more than one data source.
2. Link the tables.
3. Refresh the report and notice the slow performance.

• When refreshing a report based on multiple data sources, Crystal Reports will: 

1. Generate an SQL Query for each data source based on:     
• The database fields that are added to the report,    
•  The links between the tables,    
• The Record Selection Formula
    
2. Send an SQL Query to each data source.
     
3. Each data source will process the request.  
     
4. Each data source will send the result set to Crystal Reports. 
      
5. For each data set received, it will be stored in a temporary file on the local machine, and the linking between the data set for each data source is done in the temporary file, as well as the filtering of data if there is a record selection on the report. ( Large amount of data will decrease the report performance as it needs to perform the linking and filtering locally, which is not as efficient as an actual database server. ) 
       
6. The resulting data set will be displayed in Crystal Reports.  

   

For example: If we create a report based of the following data source:

• Oracle
• MS Access 

And we add the following tables:

• Orders - From Oracle data source
• Product - From MS Access data source 

Then link the tables on "Order ID"

When refreshing the report, Crystal Reports will:

1. Generate the following SQL Queries:   
   • Oracle: Select "Order ID", "Quantity"   From Orders    
   • MS Acess: Select "Order ID", "Product Name"   From Product 
         
2. Send the SQL Query to the respective database. ( Oracle and MS Access )
      
3. Oracle and MS Access will process the requests.
      
4. Each data source will return the result set to Crystal Reports
   • Oracle will send back to Crystal Reports for example 100 records 
   • MS Access will send back to Crystal Reports for example 250 records 
         
5. Both result set will be temporary stored in a file, and Crystal Reports will perform the link on "Order ID" between the two tables in the temporary file. 
      
6. If there is a Record Selection, it will filter the data set.
        
7. Then it display, for example, 150 records that resulted in the linking between the two tables and the filtering.

• This is normal behavior as the report have to query multiple data sources, then needs to perform the link between the tables and the data filtering on the client computer in the temp directory.
    
• A suggestion to improve report performance will be to, whenever possible, insert all the necessary objects ( Tables, Views, Data,... ) in one database instead of reporting of multiple data source.

Slow report performance, Crystal Reports, CR, multiple data source, multiple database , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

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