Large SQL in Report studio with prompts and performance of oracle

[vaguayo]

Hi,

A client  of ours is having problems with a report they created. They had basically taken their Crystal report (Command SQL) and adapted it to insert it as an SQL in Cognos.
Because this report is run against an Oracle DB used by another application, running the report somehow impaceted the DB and therefore the other application started to slow down considerably.
I have been asked to re-do the report in an attempt to improve performance.  (see attached SQL used)

My first thought was that since this SQL is used for one report only, attempting to model it in FM does not make sense. Please correct me if I am wrong in this assumption.

The report uses prompts directly in the sql, so my thoughts are that this can actually be good for performance as the filter is applied before going into the DB.

I am also planning on taking some of the CASE statements (which are not necessary in the SQL), would this actually help?

would creating a function or SP directly in ORACLE help (this implies the use of FM)?
I have found an article where I can use prompts in this way and pass them on to FM. i have not attempted this yet.
http://www.biprofessional.com/2010/06/returning-table-from-oracle-function/

would creating a view help? in this scneario I would need to re-engineer the sql to have the filters at the WHERE statement and then use an SQL in report studio to place the filtes then. This option sounds less optimal.

are there any other ideas as to how to improve this query's performance?

thanks,

Victor

[Lynn]

I suggest you work with the DBA to see the execution plan for the SQL and figure out what the problem is. You can make a lot of guesses about how to fix it but it should be easier to narrow the choices if you have a better understanding the cause. It could be as simple as an index on the database.

As for the comment about the SQL being used for one report only, I'm not a big fan of that approach in most cases. Is the data in that database now and forever always until eternity to be used for this one and only one report? Modeling in FM would support a wide variety of reporting requirements for the data so it makes sense to find out if there are any such requirements.

[vaguayo]

Hi Lynn,

thanks for your reply.
I am also not a fan of writing sqls per report. The problem is that I did not develop their framework nor their reports.
Client just asked my company to fix this for them and I was the lucky one to be assigned th task. Client wants us to fix this single report for them a.k.a. improve performance. There is very little advantage in developing a framework for more reporting needs at this point I would say, mainly because client is unlikely to leverage the power of Cognos in that respect. They are used to Crystal and simply placed their SQLS in their reports in Cognos in the same way.

I think my best shot, like you say is to work with a DBA on this and re-write the query in the best way.

Victor

[navissar]

I can think of another advatage of a FM model in this case: Finer control over how SQL is performed. For example, do you use literal values for parameters, or parameter markers? It is often better to use parameter markers as hard parse, soft parse and optimization will be cached from the first query; however, in some caeses literal values will improve performance.

Also, if I understood correctly the problem is not the report response time but rather the impact on an application DB. If that's the case, perhaps a simple solution would be to have a DB script dump the data daily to a table (Or even just identical tables on a different schema) and work on that.

[vaguayo]

Hi Nimrod,

Thanks for your advise on parameters, I have read about this now and I was not aware of the governance option in FM. I will keep that in mind.
I am considering using FM however, there are some complex joins which i am trying to understand how to model. I will get back on that on another post if I decide to go with FM

Cheers,
Victor