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 is 10 times slower than an ODBC connection to the same stored procedure.

Environment

• Crystal Reports 2008
• SAP Crystal Reports 2011
• SAP Crystal Reports 2013
     
• Oracle version 11.2 

Reproducing the Issue

1. In Crystal Reports, create a report off an Oracle Stored Procedure returning large amount of records (about 10 000) 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 using the native Oracle connection, it's because Oracle did not support pre-fetch feature for store procedure prior to Oracle version 11.2.0.1, so the Oracle driver is not fetching data to optimize performance.
• In newer version of Oracle, the pre-fetch feature should allow to improve the performance.
• This situation was reported to product under ADAPT01682544.

Resolution

• The performance has now been improved when reporting off an Oracle stored procedure using the native Oracle connection in the following version of Crystal Reports:
     
• Crystal Reports 2008:
• Fix Pack 5.5
• Service Pack 7
    
• SAP Crystal Reports 2011:
• Support Pack 08
       
• SAP Crystal Reports 2013:
• Support Pack 01, Patch 1.2
• Support Pack 02

Keywords

CR, Oracle, stored procedure, performance

Header Data

Released On	09.01.2014 00:25:27
Release Status	Released to Customer
Component	BI-RA-CR Crystal Reports designer or BusinessViews Manager
Priority	Normal
Category	Problem
Database	ORACLE 10.0