Skip to Content
0
Former Member
Apr 30, 2018 at 06:30 PM

Crystal Report based on Oracle query or Stored Procedure runs extremely slow

556 Views

Hi,

At the office, we have Crystal Reports 2016 working on Oracle 11gR2.

It turn out that I created a query stored within a "Package.Stored Procedure", which makes use of "Virtual Views" (e.g.: WITH "view1" as SELECT...). There are about 5 or 6 of these virtual views within the query. The number of rows returned is not high. Usually It's between 100 and 200. The Stored Procedure has a cursor as IN OUT parameter .

The Stored procedure runs fine in the database, and it does not take too long for returning the data.(1 minute or 2 at most).

My problem is when I use a report using the Stored Procedure. The reports only works fine the first time (the time when I am creating the Report). Then, when I try to execute it for a second time, it takes ages, in fact I have not seen it returning any data at all.

I even have copied the query within a report (not using the Stored Procedure at all), and I get the same outcome that when I have used the Stored Procedure.

I am using the Oracle Native driver included in Crystal Reports, since the ODBC driver throws an I.O Exception when I try to used it.

Can you tell me what may be wrong ? Thanks