Excel output taking long time - 8.1

[cognosun]

There is a report which is taking 5 mins if we run that Report's SQL against the database.

But same report is taking 20 mins in Excel 2000 single sheet or other excel formats ( or PDF in that case)

We are using Cognos 8.1

Please help on this like what are the settings i need to check.

[phanicrn]

run the report in background and save copy, it willl be faster..

phani

[cognosun]

can you tell me why the background process works faster than the interactive process ?

[goose]

How many pages is the report? If the report has thousands of pages then it could take some time to render the report. Also if the Cognos server(s) is very busy then that can also impact rendering time. Are you sure the sql your running directly against the database is the same sql that cognos is generating?

Besides all that 5 minutes is still a very long time to wait for an interactive report. As phani said maybe running the report in the background or scheduling the report will result in a better end user experience i.e. they wont have to "wait" for the report (I can't see how it will greatly reduce the overall time of creating the report though).

[cognosun]

The report output has only 1 page !

[goose]

Then you will probably find that the sql that cognos generates is not the same as what you are executing. Depending on your data source for the report the easiest way to get the exact sql would be to trace the session on the database. If you cannot do that then you can setup logging for the report service and get the sql that way. Another easy but unreliable way is get the sql from the query via report studio but there is not guarantee that is what get executed in my experience but at least if that is missing filters etc you know somethings up.

[cognosun]

Didn't get when you said...

"the sql that cognos generates is not the same as what you are executing "

I'm executing the Native SQL, generated by the Report studio report. 

[twlarsen]

What he said was the Native SQL that you are seeing when you look at the generate SQL in Report Studio is not the actual SQL that is executed.  You will need to run a trace on your database to see the SQL that is actually executed.  Sometimes it's different, it's confusing, but thats how it works :)

[cognosun]

can you tell me why the background process works faster than the interactive process ?

When our server was almost idle, we ran a report in both interactive and batch mode, with Excel ouput, and both formats took 30 mins to give output.

This report is bit large...with 50k rows.

[goose]

I ran some tests, a simple list report fetching data from SQL Server limited to 50K rows via top 50000.

The SQL used for the report is below and it completes in 2 seconds when run directly against the database including pulling the data through to my machine.

select top 50000 memonumberto             as memonumberto,
                 settlecurrencyg5         as settlecurrencyg5,
                 settlecurrencycodepayb6  as settlecurrencycodepayb6,
                 settlecurrencycoderecvb5 as settlecurrencycoderecvb5,
                 classtl                  as classtl,
                 assetgroupag             as assetgroupag,
                 sum(amortizationbasebq)  as amortizationbasebq,
                 sum(amortizationamtao)   as amortizationamtao,
                 sum(costbasebo)          as costbasebo,
                 sum(costvalueco)         as costvalueco
from   transactions
group  by memonumberto,
          settlecurrencyg5,
          settlecurrencycodepayb6,
          settlecurrencycoderecvb5,
          classtl,
          assetgroupag

These are the stats for generating the report to the various excel formats via Cognos Connection and choosing to save the output.   

Excel 2007 2 min 17 secs
Excel 2002 1 min 35 secs
Excel 2000 Single Sheet 1 min 45 secs   

Will post the server hardware stats later but nothing special probaly 4GB Xeon/Itanium box