Cognos report performance tuning - Force Cognos engine to generate a single query for master detail report

Check list of all posts and the book 

This approach is from Cognos best practice, (http://www.ibm.com/developerworks/data/library/cognos/reporting/performance_and_tuning/page582.html) to improve master detail report performance. The whole idea is to send a single query to database instead of sending 1 (master query) + number of master query records (detail query).

In case when queries take most of report generation time, this method could be very helpful. The test result is very positive for a single master and detail; however, it won’t work against one master and multiple details, such as list contains two different kinds of charts in two columns.

The process is documented below for your quick reference:

Step 1: Change Cognos server setting:

add <entry name="RequestHintLocalCacheHasHigherPriority" value="0"/> on a new line underneath the <section name="QueryEngine"> to CQEConfig.xml

and reboot Cognos server.

Step 2: Combine master and detail query as consolidated base query and define master and detail query derived from base query as diagram below

Step 3: set master query parameter as

Step 4: set Detail query parameter as

Step 5: optional:  Check SQL profile where you can see SQL command sent to SQL server. Check performance before and after.