title: slow cognos report
body: <p>We just migrated a report from Cognos reportnet to Cognos 8.4 and the report is too slow now.</p>
  
  <p>The report just has a crosstab nested inside a list with aggregates over periods/quarters/halfs/years</p>
  
  <p>Report Design:</p>
  
  <ul>
  <li>The mainqueryitem (queryitem) gets
  data via manual sql.</li>
  <li>The manual sql has 4 queries inturn
  unioned.</li>
  <li>All the 4 queries are just selects
  from different tables joined (no
  groups/sorts/filters).</li>
  <li>The PlanningLevel (queryitem) gets
  data from mainqueryitem.
  (eg: <code>if mainqueryitem.name = 'Black' then mainqueryitem.quantity else null</code>.
  All the DataItems of PlanningLevel uses the above format)</li>
  <li>The Report Page consists of a
  crosstab nested inside a list
  (segmented).</li>
  <li>The List is associated to a
  masterquery.</li>
  <li>The crosstab is associated to
  planning level.</li>
  <li>The crosstab contains aggregates
  also.</li>
  <li>The prompt page contains a
  multiselect list.</li>
  </ul>
  
  <p>The report was very slow even for lesser prompt values.</p>
  
  <p>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)</p>
  
  <p>The report then ran faster for lesser no. of criteria (&lt;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)</p>
  
  <p>The mainqueryitem sql when run in toad for the largest report takes &lt;5 minutes to execute.
  The largest report takes 3.5 hrs which was running in minutes in reportnet.</p>
  
  <p>Any ideas how to improve the performance?</p>