Author Topic: Best Practices on Report Development to improve Performance  (Read 13380 times)

Offline master27

  • Associate
  • **
  • Join Date: May 2010
  • Posts: 2
  • Forum Citizenship: +0/-0
Hi All,

Any recommendations on how to design a report or model a DMR that achieves optimal performance? Currently, we had designed a report that originally has a runtime of 4 minutes. We were able to reduce this down to a minute, but is still unacceptable. All data was not yet loaded into the testing environment, only a small scale for development purposes. Another thing that makes it unacceptable is that this report was not yet deployed to the users, so if in the testing environment it already requires 1 minute to up, what more would it require if concurrent users were already using it? The database was already designed as star schema, primary keys and indexes were all set there so that Framework Manager would just inherit everything from it. Use of local cache was of great help but only after the initial running of the report. Other options that would still reduce the 1 minute report runtime? Documentations would be of great help.

Thanks!

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,300
  • Forum Citizenship: +665/-10
  • Cognos Software Muppet
Re: Best Practices on Report Development to improve Performance
« Reply #1 on: 05 May 2010 02:58:41 am »
Where is the time being taken - in the processing of the SQL query by the database, or in the rendering of the report by Cognos 8?

MF.
Meep!

Offline master27

  • Associate
  • **
  • Join Date: May 2010
  • Posts: 2
  • Forum Citizenship: +0/-0
Re: Best Practices on Report Development to improve Performance
« Reply #2 on: 05 May 2010 04:01:50 am »
It's in the rendering of the report by Cognos 8.

Offline deva.adi

  • Associate
  • **
  • Join Date: Mar 2011
  • Posts: 1
  • Forum Citizenship: +0/-0
Re: Best Practices on Report Development to improve Performance
« Reply #3 on: 13 Apr 2011 04:12:42 am »
One thing I have found with all this performance tuning is a way to speed up all the initial report pre-processing.
 
If you have a report with several queries Cognos reads each query and attempts to satisfy any Parameters or Macros. 
 
One way to reduce this time is to create a dummy query “Q_Param_Dummy” and put all the filters in this query.  You can also copy any column that uses parameters, parameter maps or Macros into the objects panel.  This query should not be ties to any visualization and will not be executed when the report runs, it’s only purpose is to satisfy all the pre-processing.
 
After this is completed you then need to set a Query property on every query in the report.
 
“Use for Parameter  Info” – Set this to “Yes” for Dummy query and “No” for all others.
 
Other things that have seemed to have helped is setting the “Execution Method” to “Concurrent” on queries that can run without information from others.
 
On the Dummy Query, I also move this to the top of the list and set it to “Sequential” in the “Execution Method” property.
 
 

Offline nmcdermaid

  • Senior Consultant
  • Community Leader
  • *****
  • Join Date: Apr 2010
  • Posts: 76
  • Forum Citizenship: +1/-0
Re: Best Practices on Report Development to improve Performance
« Reply #4 on: 21 Jan 2013 09:04:30 pm »
To troubleshoot the rendering part, you can press F12 in IE, and go to Profiler. It will tell you how long each individual element took to render. There is a lot of information there but it might shed some light on it for you.