SAP Knowledge Base Article - Public

2281699 - Large number of queries sent to the database when refreshing a report based on a Universe in Crystal Reports

Symptom

  • Report performance issue.
  • Report takes a long time to refresh.
  • Large number of queries are sent to the database.
  • When refreshing a report based on a Universe in Crystal Reports, it takes a long time to refresh due to a large number of SQL Queries sent to the data source.

Environment

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

Reproducing the Issue

  1. In the Universe Design Tool, create a Universe based on more than one data source.
  2. In Crystal Reports, create a report off the Universe.
  3. When refreshing the report, it is sending a large number of SQL Queries to the data source, which is causing performance issue.

Cause

  • In Crystal Reports, when reporting of a Universe that connects to multiple data sources, it sends one SQL Query for each data sources, and use the cache to perform the linking between the 2 data sources in Crystal Reports.
          
  • The cache size is set by default to 5,000 records for the SQL Query sent to the first data source, therefore, if the number of rows returned is greater than the cache size, then the behavior will be to call the SQL Query for the second SQL Query used in the Universe, the number of times equal to the number of records returned by the first SQL Query.
        
  • The reason the behavior is different when the number of records exceed the cache size, it's because when there is not enough memory to cache the data set returned to performed the linking, therefore, the linking between the 2 data sources has to be done one row at a time by sending one SQL Query for each row it link to the second data source.  This is causing multiple SQL Queries to be sent to the second data sources, which by consequent is causing the perfomance degradation.

Resolution

  • To avoid sending multiple SQL Queries to perform the linking between the first data source, and the second data source used in the Universe, increase the Crystal Reports cache size, by adding the registry key: NumberRowsetRecordsToCache
       
    WARNING: The steps below involve editing the registry. Read the SAP Knowledge Base Article 1323322 for information on how to backup the Microsoft Registry before applying any change.
                
    1. Close Crystal Reports.
       
    2. To open the Microsoft Registry Editor, select "Run", under the menu "Start" in MS Windows, and type: Regedit
        
    3. Navigate to the path:

      HKEY_LOCAL_MACHINE\Software\SAP Business Objects\Suites XI 4.0\Crystal Reports\Database\
        
    1. Add a DWord Value: NumberRowsetRecordsToCache
             
    2. Set NumberRowsetRecordsToCache, to a value greater than the maximum number of rows you will expect the SQL Query for the first Universe data source to return.
              
      For example, if the SQL Query for the first data source returns 10,000 rows, then set the registry key value to: 11,000. 

Keywords

CR, .UNV, CR Universe , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

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