Skip to Content
avatar image
Former Member

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Apr 30 at 09:20 PM

    I think Virtual View is what causing the problem. I know Crystal Reports application does not like Temp tables in Stored Procedures, I guess the same story with Virtual Views.

    To find what is going on I would suggest CRLogger:

    https://apps.support.sap.com/sap/support/knowledge/public/en/1603398

    CrLOgger captures communication between datasource and Crystal Reports. You can collect the log for the first run and then compare with the second run. It should tell you if there is any difference in CR calls to Stored Procedure.

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 30 at 06:41 PM

    It's good that you're using a native Oracle connection - that tends to be much more responsive and stable than ODBC to Oracle.

    Are the "virtual views" used in the stored procedure or are they linked together in your report?

    Are you linking the stored procedure to anything else in the report or are you just using a single stored proc?

    Does the stored proc update data?

    -Dell

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 30 at 08:48 PM

    Hi Dell,

    Thanks for the Help.

    The Virtual Views are used within the Stored Procedure, not the report, and the Stored Procedure is not linked to anything other else. I only use it as Data Source for my Report, and since currently I am only testing it, I just add a couple of fields in the Report and then I execute it. I have only 3 parameters in it. 2 of them are IN parameters and they are Number Type. The other parameter is a ref Cursor and it is used for returning the query.

    Every time I do my testing, the stored procedure runs fine when I execute it in the Database. When I am configuring the Report, I set the testing parameters, and the when I refresh for first time, runs fine, I see the columns in the report. Even if I copy the query within the report, it runs fine only the first time.

    The Stored Procedure is not updating any Table, it is executing a SELECT command only.

    I have followed all the steps to work with Oracle Stored Procedures as specified in the CR Guide (such as the Date format that we must use when dealing with dates).

    I wonder what could be the reason.

    Add comment
    10|10000 characters needed characters exceeded