Report Performance after creating Master detail relationship

[nshukla]

Hi Everyone,

I am facing performance issues after using master detail relationship between 2 queries in Cognos 10.2.1. I have inserted a chart (progress bar) within a crosstab after which the report has become extremely slow. I was recommended to try : http://www.ibm.com/developerworks/data/library/cognos/reporting/performance_and_tuning/page582.html, and I am working on implementing it. But was wondering if there is anything else that I should check/ do to improve the performance?

Thanks!
Niyati

[AnalyticsWithJay]

Note that for every parent item, Cognos has to execute SQL against the database to retrieve the details for the chart. You could try limiting the number of parent items, as the detail query needs to be executed many times.

You might also want to measure how much processing time is spent on the database end. Generate the SQL for your queries, and execute it directly in your database client. Be sure to multiply the detail query execution time by the number of detail items for a quick "approximate" execution time. If your total processing time is WAY longer than your DB processing time, then you know you have some local processing within Cognos that you need to look into. If performance times are similar, generate an explain plan for your query to ensure your DB is leveraging the proper indices.

For the report itself, ensure you're using database specific functions instead of locally processed functions. If you're using a cube or dimensional data source, ensure that you're using proper dimensional functions and filtering.