Oracle stored procedures with CR

[hegdemk]

Hi,

We are developing a web based reporting system using ASP, CR 8.5 (DEV) and Oracle. The system is working fine but it is very slow. It is mainly because of the complexity of the Oracle queries - there are many functions, multiple table joins, multi level dynamic views and unions in the queries. Hence the queries are mostly doing 'table scan' and there are too many complexities to solve just by indexing. (I can give you an example query that runs 3 pages !)

This is because one Crystal Reports report (.rpt file) can accept only one resultset. So a single dynamic SQL is getting all the data necessary fileds for the report. Hence the query is complex and is slow.

What are the possible ways to improve the query performance?

We are exploring the possibilities of moving the dynamic SQL from .rpt file to an Oracled stored procedure. That will eliminate the parsing time at runtime. But as per my understanding even the stored procedure can contain only one SQL, so all the complexities of the query still remain. I cannot break the query into simpler queries for optimized, indexed access. I could break the queries if Crystal Reports supported the PL/SQL tables (arrays) as return values from Stored Procedure and process them as a result set. Then I can construct the Array with appropriate cursor, looping and smaller queries and return it.

Does anyone know whether ASP can accept PL/SQL arrays for storing into a ADO result set? Then I can assign the result set as the data source for Crystal Reports. Example code or pointer to example code would really help.

Also can there be Master/Slave (Parent/Child) kind of relationship between main report and sub-reports in Crystal Reports? Then may be I can try to divide the complex report into parts and when the parent section of a report executes its SQL, subreport will fetch the corresponding data child for the child sections, automatically. If you think this could be a solution alternative, please give me an example code.

Thanks in advance for all your suggestions and help.

 

[lyanch]

You can easily set up a parent child relationship between the main report and subreports. I try to avoid this if the subreport will be reproduced many times (i.e a subreport that links at the detail level with 1000's of detail lines means 1000's of queries hitting the DB .. one each time the sub is called).

The other alternative is to set up a Stored Procedure that puts the data in a format that crystal can easily handle. Even if you use the same complex query in the SP, you can at least give hints as to the best way for the DB to approach it. Also, in the Stored Proc you can more easily avoid using views etc, which tend to be slower than directly accessing the tables.

Lisa

 

[Naith]

What's that about PL/SQL only containing one SQL? You can process loads of SQL chunks in PL/SQL, and if your existing queries are very slow, this would be the way I would be inclined to favour.

What's the state of your database? I only ask because I've seen the return time of complex queries cut down from 12 hours to 12 seconds simply by prefixing the query execution with an analyze database script. (N.B. If you do analyze tables prior to running your report script, ensure that you analyze all the tables in the database, or you may actually see a decrease in performance before you see an increase.)

Naith

 

[Naith]

Oh yeah, and if you do try analyzing your database, make sure you unanalyze it afterwards by running "Delete Statistics". Your DBA should already know this (and will probably be the guy doing all this part), but just to make sure - otherwise you may find your previous analyze halts the database after your report runs.

Naith

 

[hegdemk]

Hi, Thanks for the reponse. Naith, here is the answer to your question, "what's that about PL/SQL only containing one SQL?" -

I know that inside the stored proc you can normally execute any number of SQLs. But CR interaction with Oracle stored proc is special. Please refer to "Oracle Stored Procedures and Crystal Reports" and "Stored Procedure Support" PDF documents on Crystal Decisions Knowledge Base.

The way CR interacts with a Oracle Stored proc is via a REF CURSOR IN OUT parameter, which should be the very first parameter to Stored Proc. All you can do inside the Stored Proc is to 'OPEN this REF CURSOR with a single SQL' and CR will perform the FETCH from the cursor.

Since we cannot manipulate the cursor variables (unlike PL/SQL tables (i.e. arrays) we cannot break the complex query and populate the REF CURSOR.

Hi Lyanch, I will try out the Report/Subreport approach. But, I am afraid some of my queries are too complex and return too many rows to be broken up that way.

Regards,
Mahesh

 

[lyanch]

Mahesh,

You stated:

"Since we cannot manipulate the cursor variables (unlike PL/SQL tables (i.e. arrays) we cannot break the complex query and populate the REF CURSOR."

But you can, just break it up however you need in the Stored Proc. The last thing you do is open the REF CURSOR. I usually, by that point, have a simple query from one or two temp tables and maybe one regular table. I do all the hard hitting calcs/groups etc first and intset those into temp tables.

Lisa