Report Prompt Page Loading takes more time

[Chakkarapani]

Hi,

I am having a report with just two value prompts in it. One Prompt loads the distinct Name fields and other prompt loads code values for the same. I used two queries for each prompts eventhough it is from the same physical table. when I have only the first prompt my prompt page gets loaded with the values in fraction of seconds. But when I include the other prompt also it is taking 18 secs to load the prompt page with values. I have also tried with single prompt query but same time for that too. Kindly help me on this issue. Thanks in advance.

[tjohnson3050]

You have three options:

Option 1 - Performance tune your queries.  Take the generated sql for the report, examine the sql generated for the prompt queries, take those queries and use tools for your data source to examine the query plan (For example - Oracle Explain Plan or SQL Server Query Execution Plan).  If you don't know how to do this, enlist the help of a DBA.

Option 2 - Request new tables from your ETL group for the sole purpose of supporting your prompts.  These are sometimes referred to as List of Value tables or Lookup Tables.  In the ETL layer, do work to return the distinct values and store them in a table.  Publish these tables in your FM package with no relationships to anything else.

Option 3 - Schedule the prompt pages to run once a day and cache the values.  In Cognos Connection, create a job that runs the report.  In the job, you can set options for the report.  One of the these options is a choice to either run the report, or refresh the report cache.  If you select refresh the report cache, this will execute the queries for the report prompts and cache the values.

Good Luck!