1822696 - Poor performance when reporting off an Oracle Stored Procedure in Crystal Reports

SAP Knowledge Base Article - Public

1822696 - Poor performance when reporting off an Oracle Stored Procedure in Crystal Reports

Symptom

  • Slow when reporting off an Oracle Store Procedure in Crystal Reports.
  • Crystal Reports status bar shows records are coming from the Stored Procedure in small batches.
  • Native connection to an Oracle Stored Procedure in Crystal Reports takes much more times to retrurn the result than when using an ODBC connection to the same stored procedure.

Environment

  • SAP Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
       
  • Oracle 11g

Reproducing the Issue

  1. In Crystal Reports, create a report off an Oracle Stored Procedure returning large amount of records using a native Oracle connection.
  2. Refresh the report.
  3. Notice it takes a long time to return the data.

Cause

  • The reason it takes more time to fetch the data when connecting to an Oracle Stored Procedure in Crystal Reports when using the Native Oracle connection are due to:
    • The version of Oracle Client used.
    • The version of Crystal Reports used.
       
  • The version of Oracle Client is important because Oracle did not support pre-fetch feature for Stored Procedure prior to Oracle version 11.2.0.1, so the Oracle driver is not fetching data to optimize performance, therefore if the version of the Oracle Client is lower than 11.2.0.1, it will be slower.
               
  • The version of Crystal Reports could also be the cause of the report performance because in older versions of Crystal Reports, it did not take advantage of the new Oracle pre-fetch feature that allows to improve the performance, and this situation was reported to product under ADAPT01682544.

Resolution

  • To improve report performance when using a Native Oracle connection to a Stored Procedure in Crystal Reports:
     
    1. Confirm you have installed the Oracle Client 11.2.0.1 or higher, on the computer where Crystal Reports is installed, because the pre-fetch feature that help improve performace is an Oracle functionality that was added from Oracle 11.2.0.1
            
    2. Install the following version of Crystal Reports or above, as those versions can take advantage of the new Oracle pre-fetch feature to improve report performance:   
              
      • Crystal Reports 2008:
        • Fix Pack 5.5
        • Service Pack 7
               
      • Crystal Reports 2011:
        • Support Pack 08
                  
      • Crystal Reports 2013:
        • Support Pack 01 - Patch 1.2
        • Support Pack 02
                 
      • Crystal Reports 2016:
        • Support Pack 02

Keywords

CR, Oracle, stored procedure, performance , 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