slow cognos report

We just migrated a report from Cognos reportnet to Cognos 8.4 and the report is too slow now.

The report just has a crosstab nested inside a list with aggregates over periods/quarters/halfs/years

Report Design:

• The mainqueryitem (queryitem) gets data via manual sql.
• The manual sql has 4 queries inturn unioned.
• All the 4 queries are just selects from different tables joined (no groups/sorts/filters).
• The PlanningLevel (queryitem) gets data from mainqueryitem. (eg: if mainqueryitem.name = 'Black' then mainqueryitem.quantity else null. All the DataItems of PlanningLevel uses the above format)
• The Report Page consists of a crosstab nested inside a list (segmented).
• The List is associated to a masterquery.
• The crosstab is associated to planning level.
• The crosstab contains aggregates also.
开发者_开发知识库
• The prompt page contains a multiselect list.

The report was very slow even for lesser prompt values.

Then I changed the property 'OverrideDimInfo' to 'no' for PlanningLevel queryitem which had some DimensionInfos already when migrated from reportnet (don't know what it was)

The report then ran faster for lesser no. of criteria (<1 minute). (400x faster) But for more no. of options/criteria (>2), the report is still slower. (upto 3.5 hrs, for the biggest report-all criteria selected)

The mainqueryitem sql when run in toad for the largest report takes <5 minutes to execute. The largest report takes 3.5 hrs which was running in minutes in reportnet.

Any ideas how to improve the performance?

---

One thing I have observed in 8.4 when using a crosstab object nested within a list object, joined together with a master-detail relationship, is that your master query (associated with the list) should be as limited and simple as possible. I don't know about your situation, but usually the purpose of the list containing the master query is to segment the crosstab results into groups based on dimensional attributes, and the detail query is more complicated and includes fact information as well. In this situation, Cognos does not execute 2 queries that pull all the data and format on the Cognos server (as one would expect), but rather fires a separate query for each grouping. Sometimes you can gain some improvement in performance by simplifying the Master Query as much as possible. A lot of times people will just copy the detail query, rename it as the master and join back to detail query without any modification. Get rid of anything that you don't need in the master query. This may not be the case in your situation, but we've observed this behavior numerous times on our reports and tweaking the master query usually helps.

Another issue you can run into when using list sections (not sure if that's what you mean by segmenting) depending on how the report was built, is that Cognos will sometimes fire a duplicate query for each section. You can see how many queries are executed by selecting 'Tools > Show Generated SQL/MDX' from the menu.