Long time to output report

[BigChris]

Hi - I've got a report that seems to run reasonably quickly. I can view the tabular data for the query behind it, and I can run the report to screen successfully. I can go from the first page on the screen to the last page without any problems, and in a couple seconds. When I try to output the report, by saving it as HTML, or as Excel, the report just spins without producing any output.

I've tried running the report interactively, and also running it as a background activity. I've left it running for over 30 minutes without any signs of it coming back. Can anyone suggest anything that I should check that might be causing this report to flounder?

I'm running it on Cognos 10.2, over an SQL relational database. The output that I'm expecting is approximately 8,000 rows, so not huge but not tiny either.

TIA, Chris

[New_Guy]

Hi,
Did you try running the generated sql directly in the database and check how long it takes to render all the rows? And how complicated is the report?
Good luck
New guy

[BigChris]

Hi - thanks for the reply, and yes those are good suggestions. I built the report from the ground up, checking the generated SQL against the database at each stage. Where necessary I've added indexes against tables where they've shown up as missing after doing an estimated execution plan.

There are about five queries that are joined to produces the output. When I run the report to screen I can navigate to the top of the report, then to the bottom of the report, and move between pages etc. But when I try to save the output (after running it to screen) or if I run the report as a background task it takes about an hour and 30 minutes to complete. That's the same regardless of whether I try to save it as HTML or Excel (I haven't tried PDF yet, but I'm expecting the results to be the same).

[bdbits]

This may or may not be part of it... When you run it interactively, it does not render the entire report, just enough to show you that page. When you save it out to a file, it builds the entire report in memory. 8000 rows is likely well over 100 pages, and if you have a lot of formatting going on, it can become significant in a hurry.