: Performance issue

[Maple]

We are having performance issue with cognos 8.1. when we query data directly from the database then the responce is really fast but when we run the report from the query studio or from the Report studio then the responce is really slow...is there any clues ...

[COGNOiSe administrator]

Which database? What type of outputs?

[Maple]

SQL server 2000
Report out put

[COGNOiSe administrator]

HTML/ PDF / Excel?

[Maple]

HTML and PDF

[goose]

You will probery find the sql that you execute and the sql generated by Cognos is different. In report studio check the sql  for the query subject or you can always run a trace on sql server. Things to look out for is when cognos runs multiple queries and then joins them localy.

[COGNOiSe administrator]

Try to force your queries to Database Only. If they can't run, that means your data is processed locally. Also, processing PDFs require alot of resources.

[Maple]

This is not only the PDF issue...reports are also slow when they rendering into HTML
What do u mean by 'Force your queries to database only'?

[larsonr]

Once you are in a query in query explorer, you have the option of telling it to run locally or database only (left side of the screen).

Do you have access to the database to see what queries are being hit against it?  Do you have the audit tables running for Cognos?

We ran into the same problem in that better reports can be written if you understand the SQL being generated by Cognos.

Ex. We use Oracle, so we used Enterprise Manager (before loading the audit tables) to see what SQL was being sent to the database from the report.  With the audit tables, you can capture the native sql being generated (same effect as using enterprise manager).  You'll be surprised what Report Studio will generate if you're not careful in your Query construction or Framework modeling.

If you cannot find the solution on your own as to why the performance is lacking, try working with your DBA to understand what the reports are generating against the database.  Also, are you getting data from multiple databases in one query?  That may also be presenting some issues on how it was modeled and queried.  Just some thoughts.

[Maple]

hi larsonr,

Yes, you are right. I'll definitely take a look...

Thanks,

[goose]

A quick an dirty way to get the sql from inside RS is to:

1. Click Tools --> Validate with options

2. Select Information as the validation level, click ok

3. Look for a UDA-QOS-0004 Native SQL being executed: message that is the sql that get passd to the db

Hope this helps

[MDXpressor]

The best way to get at the SQL/MDX is to click on the Query, then in the properties pane, there is a line to show the Generated SQL/MDX.  You will be able to toggle it back and forth between Native and Cognos versions of the languages.

Further to the performance issues, many performance based bugs were addressed in 8.2, so if all else fails, a move may be in order.

[larsonr]

I will concur, 8.2 did address many issues we were having against oracle and its sql generation.  It may definitely be of an advantage to think about using 8.2.

[Maple]

hey larsonr,

Well, I got the code for my report from the report studio and i ran this code from the  SQL Editor ....its was quite faster then the report studio's out put....

Any clues?

[blom0344]

SQL tools are often configurated to fetch a partial set (which may give you false results as to performance). A better check is to run a SQL statement that actually counts the output like:

SELECT COUNT(TEMP.*)
FROM
(------- [SQL STATEMENT]-----------------) TEMP

and see how fast this is.