- Report is slow.
- Reports takes a long time to refresh.
- Slow performance when reporting from multiple data sources in Crystal Reports.
- SAP Crystal Reports 2011
- SAP Crystal Reports 2013
- SAP Crystal Reports 2016
- SAP Crystal Reports 2020
Reproducing the Issue
- In Crystal Reports, create a report on more than one data source.
- Link the tables.
- Refresh the report and notice the slow performance.
- When refreshing a report based on multiple data sources, Crystal Reports will:
- 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
For example: If we create a report based of the following data source:
- 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:
- Generate the following SQL Queries:
- Oracle: Select "Order ID", "Quantity" From Orders
- MS Acess: Select "Order ID", "Product Name" From Product
- Send the SQL Query to the respective database. ( Oracle and MS Access )
- Oracle and MS Access will process the requests.
- 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
- 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.
- If there is a Record Selection, it will filter the data set.
- 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
|Multiple Data Source.bmp|