Report Performance Tuning

[Johnd2202]

We have a report that has been timing out and I was wondering if anyone had any idea what I should check to help the performance. Are there any basic things I should look at to fine tune the report? I am sort of new to Cognos and I have taken over for someone else. The report was created by the person who is no longer here.

I can't really provide any details on the queries, but just looking for some basic tips and tricks.

[bdbits]

You can search these forums, of course. You did not say, but I am going to assume this is a relational package (i.e. not a cube or other dimensional source).

Is it Cognos or the database that is timing out? How long is it running before timing out? How large is the report or result set?

The first step is really to determine where the performance problem lies - Cognos or the database. There are profiling tools but I am going to guess you do not have access to those since they run on the server. So some basic steps.
* Copy the report SQL into your database's native query tool (SSMS, PL/SQL, etc.) or other SQL query tool and see how long it takes to run the query.
* If the problem is the query takes too long, visit with whoever created the package to look for optimizations there. You may also want to contact a DBA or if you know how, run explain plans and take action to tweak database performance.
* If the database query is not the problem, you need to look at what is happening local to Cognos when it runs the report. This can be challenging. If you Cognos admin is cooperative, there is a report profiling service that can provide pretty detailed information.
* Very heavy formatting on long reports can be a performance issue, especially if the server has limited resources.
* Using Cognos-specific functions where you could use a database function instead - this may cause localized query processing (that's bad).

[Johnd2202]

Our Cognos application is not used company wide, just mainly with our team. It was a package deal with our OM application, so I actually have full access to the system, including Cognos Administrator. This is the error I saw on this particular report:

RQP-DEF-0112 Query execution time exceeds the 1800 second limit specified for the user who has the identity....blah blah blah

It looks like the previous user was combining data points from multiple reports in to create this one, which is probably the problem since Cognos and the db have to process those reports in order to get the data for this one.

What is the report profiling service?

By the way, thanks for all of your help.
 

[SCI Guy]

Unrelated to the previous answer, I have found that using output format of PDF for larger reports takes exponentially longer and has caused time out on a few occasions.

[bdbits]

I found this old post about enabling a trace. (Calling it the "report profiling service" was not the right term.) It is v8; I am pretty sure it is still the same process but I've not had to run one in some time now. Make sure you disable it afterwards, or you WILL slow down your Cognos server. Should probably run it in off hours as well (or on a dev box if you have one).

http://www.cognoise.com/index.php/topic,14095.msg44719.html#msg44719

Do check the SQL first as discussed, though. The trace logs can be a lot of information to sort through.

Data points from multiple reports? I am not exactly sure what this means, but if it is causing local processing on the Cognos server that will quite likely have an impact on run times.

You can increase the timeout to a longer value if you have the Framework Manager model.

And yes, PDF reports do tend to take a lot longer, if that is your output format.