Prompt performance

[dssd]

My prompt page is slow about 3 minutes. My report has 5 report pages. If i delete the report pages and retain all the queries though, the prompt page comes back in 5 seconds. Does report page also impact prompt performance?

[CognosPaul]

The report pages do actually have some affect on the prompt page. Specifically Cognos will go through the queries to validate the parameters. For example, if you have a prompt object in the prompt page set to multiselect, such as a checkbox, but in the query you have a filter [Data Item]=?My Param? Cognos will automatically convert the prompt object to a single select.

3 minutes does seem a bit excessive though. What happens if you validate the report with no errors, save, and try running from the cognos portal? You might also want to try caching the prompts.

[dssd]

I agree with your first line. What baffles me is if i delete all the pages but retain the query then the prompt comes back in 5 seconds. The validation of prompts against report queries would happen even then, correct.....one more thing, we have many report pages, does that hurt and page sets etc

[CognosPaul]

When the report runs Cognos will determine which queries to run by what is rendered on the page. If you create a Boolean variable set to 1=2, and apply that as a render variable to all of your queries, Cognos won't bother validating those queries at run time.

Pagesets shouldn't have too much of an impact on the prompt page, but I'm not 100% certain on that. Does the report run any faster when from Cognos Connection as opposed to Report Studio?

[dssd]

Its the same from portal or directly

[gosoccer]

COuld you please let me know if you had any luck with speeding up the Prompt Display based on Paul's suggestion or any other way. I have only 6-7 Prompts and it is taking for ever. I think the Validation that Paul is mentioning is the problem but not sure how to do the Boolean Variable in my reports. Steps to do for having this Boolean working would be great.  ;D

Thanks so much :) :) :)

[SpareTire]

Can I suggest creating a separate query for the prompts (6 if needed) and setting the Query hints to "Use For Parameter Info"?

[gosoccer]

Thank you! I'll try that. I see it under the Query Property.  :) :)

[gosoccer]

Well, that didn't work. I even tried the following,

use for parameter info: Yes
Execution Method:Concurrent
Use local cache: Yes
Execution Optimization: First row

I even tried building one Query and have all the parameters included in the Query Filter and setup the
use for parameter info: Yes, but no luck.

It seems like the first time I run the report is pretty quick but when I go back and try
running the report again or use the blue triangle to go back to the Prompt Page,
it chocks.

This is really killing me!

:'( :'(

[SpareTire]

gosoccer, just so we are on the same page:

You built a prompt query for the prompts and turned on parameter info to "yes", correct? For example, if you have a query that asks for Year=?Year?, then you built a prompt query with a data item called "year" and are using the prompt query to feed your prompt.

From reading your response below, it sounds like you put all the parameters into a query, rather then the data items that feed the prompt. The parameters should remain in their separate queries.

[gosoccer]

Hi,
thanks for your time,
Here is what I have for each Query
Prompt Queries:
I have nothing in the filter of each of the separate Queries. Just getting the data.
I have 6-7 Prompt Display Queries. None with large amount of data. 20-30 rows.

Main Query: Report Body
Here is what I have in one main query that is used for the Body of the Report:
[UCM Presentation View].[AUTO].[Z_CD] in (?z_param?)

So, after the Z_DECS is displayed in the Prompt Page, ZONE_CD is passed (USE).
The main Query is handling the actual filtration for all parameters passed.

Let me know what you think? 
Thanks alot:) :)

[SpareTire]

Just so we don't lose sight of the problem - Your Prompt Page is not rendering fast enough, fast being a few seconds.

When you run each prompt query (all 6 of them) do you get tabular data rather quickly? If not, then find out why. 20 rows of data should be lightning.

If yes, then go the report page and right click it and hit run. Do the default cognos parameter page jump up immediately? If not, then I would look at my report queries to see if any of the filters are set to "After Auto Aggregation".

[gosoccer]

Thank you,

I'm finally (hopefully) getting the security privilege to Open this report and others in our Semi-Prod environment where we have a much larger amount of rows.  Unfortunately, I don't have it yet and I don't re-produce the same problem in other environments.

In addition to checking the areas you are mentioning, I'm curious if we are using Description of Text (40-50 Characters) (not indexed) as a part of one or two of our Query Filters , if that will cause a slowness as well?

Thx so much!

I'll return as soon as I get the security privilege to run Report Studio in the Semi-Prod. :) :)

[gosoccer]

Unfortunately, they change their mind and I will not be able to access this environment with tons of records.

I'm becoming suspicious if the issue is related to the Cognos Server or Browser?

Well, this is strange: From 10:30 Am to 11:30 AM, all reports were able to display the prompt page very quickly.
All the sudden, around 11:30, the specific Report started to run much slower for displaying the Prompt Page.

Very unstable and I have no idea what's going on?

Answer to your questions:
When you run each prompt query (all 6 of them) do you get tabular data rather quickly?
Quickly

If yes, then go the report page and right click it  and hit run.
Do the default cognos parameter page jump up immediately?
Immediately.

If not, then I would look at my report queries
to see if any of the filters are set to "After Auto Aggregation".

All are set BEFORE Aggregation.

Thanks again from your time.

[SpareTire]

That sounds like a server issue to me. If your prompts appear quickly some of the time but not all of the time, then there is probably a huge load on the server (probably a very very large report) that is slowing everyone else's reports from running.

[gosoccer]

Yeap! You have a good point!

I'm actually putting some of the tuning recommendations from this forum and IBM support and having it ready as a part of the next Build. If this build's prototype report still runs 2-3 minutes to make it to the Prompt Page, I'll have a Matrix showing the decrease and increase in performance for the Server organization. Unfortunately, two different contractors are involved.

Thanks for your time. :) :)