Performance issue

[bi4business]

Hi All,

At my customer we have build a Report Studio with a simple list object.
When we run the report it takes about a hour run run.

When I take the SQL (Tools -> Show Genereted SQL/MDX) and copy/past the exact SQL directly on the Oracle database it just takes 84 seconds.
I never had seen this behavour before on Cognos

What could cause this issue and/or what can I test to analize this?

BI4BUSINESS

[bdbits]

Have your database people do a trace. They can capture the database queries and performance from their side. However you may find that the sql is running relatively fast. There are a number of phases in report processing, and the poor performance may be on the Cognos server.

There is a report tracing facility on the Cognos server, but be sure the data source is not the issue first. I don't have the specifics handy, but you can probably find how to enable the trace with a forum search as this is where I first learned about it. If not post back and I will try to look it up when I have some time.

[bi4business]

Hi BDBITS,

Thanks for your input.
The database people see the same SQL I use to put directly to SQL
Developer (90 seconds).
Running the report it's taking more than one hour....

This customer is using the Pure Application System, so the IBM Cognos BI Server should not be the issue I think.
Note sure, but i don't know how to test this architecture right now.

BI4BUSINESS

[charon]

hi bi4b,

i agree with bdbits, this might be most likely the cognos server which takes so long. for instance, if you have some "local processing" enabled this might cost you a lot, you might wanne check your fm model and the queries in your report for that option. also, a lot of data might run fast from db (relational), but if you work with some logic in a dmr in charts e.g. this might cost you as well in my experience.

also, ask the cognos server (most likely windows server admin) guy to check the IO/Drive/ memory/CPU of the cognos server machine while the report runs, this will give you an idea if this report generates a lot of traffic. Also, you might wanne run further reports to check if there is an instrastructual issue. in case it is this special report alone which has a performance issue, run each query of the report. ah, and btw, how many data do you receive from db with the queries?

regards
charon

[navissar]

Report performance improvement is a major part of what I do, and it's equal parts science and art. Let's start with the science:
Does your report use master-detail relations at all? Page sets, sections?
How many rows are you pulling per page?
What is the data base vendor? Are you using CQM or DQM? Is the model relational or DMR?
When going over the report query and running it in tabular does it also take long?
How many objects are in the report page?

We'll start with these answers and we'll move on from there.

[CognosPaul]

In addition to what Charon and Bdbits wrote, a few ideas off the top of my head.

Try checking the Cognos SQL that is generated. The Native SQL that you see is probably not the only step involved in processing the query. Are there any additional filters, calculations, or joins being made? That is one of the indicators of local processing.

Did you section the list? Or in any other way create a master detail relationship? Depending on how you built your query, it is very likely that every detail is a separate query thrown to the database.

What output is the report, and how many rows are being returned? Exporting to Excel 2002 will always take significantly longer than exporting to Excel 2007. If there are thousands of rows being returned, the wait can be excruciating.

Were you testing the SQL using the same user as Cognos? There may be situations where the query can run differently depending on the user running it.