Significant delay before report runs

[briancole]

Hi,

As you know the normal process when running a report with no prompt page is as follows:

1. User clicks on report
2. Browser displays "Your report is running. Please wait..."
3. Report is displayed.

I have a report which seems to be taking a significant amount of time between steps 1 and 2 and I don't understand what is causing the report to take so long to get to the point where it is running, and therefore I cannot optimise this process.

This particular report takes:

Step 1 to Step 2: 27 seconds
Step 2 to Step 3: 17 seconds

As you can see, it appears that the most time is being spent initialising the report before even getting to the point of running a query against the database (Step 2) and creating the report.

Can anyone offer advice as to what could be causing this? The report contains 11 queries in total, and a number of prompts (10-15).

[BigChris]

Hi Brian,

That's a tricky one to answer. Are the queries interlinked, or are the independent? And is the data for the queries coming directly from the table using a SELECT * FROM type query in Framework Model, or have you got more complicated SQL in there?

C

[briancole]

Hi Chris,

6 of the queries feed prompt boxes and are a simple "select * from".
1 query is complex and is the main driver of the report. This is based on a star schema based around a single fact table which is left outer joined to a number of dimensions (~10-15).
This query feeds another 3 queries for very specific counts\charts in the report. Deleting these three additional queries does not impact the run time of the report.

Since my original post, I have tried deleting all of the prompts from my report (Note: The prompt boxes are on the report itself as the report is essentially an interactive dashboard, I do not use a prompt page) and the report runs very quickly. Unfortunately those prompt boxes are integral to the functionality of the report so I must have them in the report.

It would appear that my issue lies in step 2 of the following diagram:


which has been taken from http://www.cognossource.com/run-cognos-report/. Every time the report is run, it is validating the report and checking for parameters, so my assumption is that as I have a large number of prompt boxes on my report this is taking a long time. Is there a way of skipping this step and saying to Cognos "I know this report is valid and I do not need you to check for parameters etc"?

Thanks,
Brian

[BigChris]

The way I tend to do that is to disable the prompts rather than deleting them...that way you don't have to recreate them again afterwards. If you disable them one at a time you might be able to find out if there's one in particular that's impacting your performance.

[briancole]

...and more progress has been made! I have now deleted all queries from my report (and associated charts\lists\visualisations) so all I have left are prompt boxes in the report which are either fed from static choices or blank.

The report still takes 27 seconds to open!  :-\

At least I can rule out any data-related issues though!

[briancole]

The way I tend to do that is to disable the prompts rather than deleting them...that way you don't have to recreate them again afterwards. If you disable them one at a time you might be able to find out if there's one in particular that's impacting your performance.

I'll try that next Chris, but my gut feeling is that it is the quantity of the prompts rather than an individual, but doing as you say will prove\disprove that theory!

[briancole]

I've just copied my report specification when the report only contains prompts from my Development environment to my Live environment and the report opens in <3 seconds! This is compared to 17 seconds for the same prompt-only report in our Development environment.

Something is very odd here. The hardward specifications of both environments aren't significantly different, so I believe it must be down to a configuration issue  :-\

[MFGF]

I've just copied my report specification when the report only contains prompts from my Development environment to my Live environment and the report opens in <3 seconds! This is compared to 17 seconds for the same prompt-only report in our Development environment.

Something is very odd here. The hardward specifications of both environments aren't significantly different, so I believe it must be down to a configuration issue  :-\

How big is the content store in your Dev environment? Is it possible it's big, fragmented and slow?

MF.

[briancole]

How big is the content store in your Dev environment? Is it possible it's big, fragmented and slow?

MF.

To be honest, it is very likely all of those things!

[MFGF]

To be honest, it is very likely all of those things!

Might be worth doing a little spring-cleaning in there if you get chance. :)

[briancole]

Will do. I'll try following this guide in the near future and hopefully it'll resolve the issue!

Thanks to both you and Chris!

[briancole]

I ran some maintenance scripts against the content store in our Development environment and now the report takes a total of 4 seconds to go from clicking to the report displaying! Brilliant stuff!

Thanks again for your help!

[davElvin]

Hi briancole,
Can you please let me know maintenance script you executed on the content store ? I have the similar issue of report loading time .