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?