Performance at cognos server

[Daljith]

A query when executed directly to the database takes 15 minutes but when the report that executes the query is executed, it takes 1 and a half hour.
Can the cgonisers suggest me how do I identify where is the issue and how do I improve it.

[bdbits]

I am going to assume you have a relational model published with CQM. DQM can complicate troubleshooting, and cubes would be another discussion altogether.

15 minutes is quite a long time for a query to run, even with many millions of rows (in my experience). I would take a look at the SQL and see if perhaps something can be done to improve that first, which might mean changes to your Cognos model. Have your DBAs capture the actual SQL that is executing when you are running the report. Is this the same as the SQL that takes 15 minutes running in a query tool? Looking at the SQL, is the database doing all the work needed for the report, or do you have local processing running on the Cognos server? You normally want to push work to the database as that is generally going to be more efficient.

Query issues aside, the type of report (HTML, PDF, Excel) and the layout and formatting can also have a significant effect on the total run time. Try building a really basic report based off the same query or queries. If that runs much quicker than the desired report, you might consider running a report trace on the Cognos server. This will show you the bottlenecks, but the traces invoke overhead so you will not want to run this on a production box.

[Daljith]

Thanks bbits. Thanks for the information and a kind of step-by-step approach.
This information is sufficient to troubleshoot this report.
I'll follow and try to find out the issue. would get back here if it is helping me fix the issue.