Reporting performance using Concurrent Query Execution
In my last article called ‘How to work out how long queries in a Cognos Analytics report take to run’ we setup the logging to enable us to see exactly how long each query in a test report took to execute.
In this article, we are going to setup Concurrently Query Execution to speed up the execution of the report.
Before we start, let’s look at a list of pros and cons for this:
In order to achieve this, we are going to do this to our report execution:
In order to achieve this, we need to do the following:
- Set a series of advanced settings against the IBM Cognos ‘Batch Report’ and ‘Report’ services on each dispatcher.
- Set the ‘Execution Method’ query hint appropriately in your report.
- Execute the report.
- Monitor the results by looking at the Dynamic Query Mode (XQE) logs.
Setting Concurrent Query Execution settings
This is arguably the most complex part of the whole process so I will try to explain this as simplistically as possible to aid understanding.
The first setting is called RSVP.CONCURRENTQUERY.MAXNUMHELPERSPERREPORT, is the maximum number of query execution helpers for EACH report or batch report process.
The default value for this setting is 0 which means concurrent query execution is DISABLED.
If this setting remains at zero the maximum number of connections to the data source created by the report service is 16 (using all the other default out of the box settings).
- For each Report Service Process
- 2 x Low-Affinity Connections
- 2 x High Affinity Connections
If this setting is set to 2 the maximum number of connections to the data source created by the report service is 24 (using all the other default out of the box settings).
This is made up of:
- For each Report Service Process
- 2 x Low-Affinity Connections
- 2 x High-Affinity Connections
- 2 x Query Execution Helpers
The higher the setting for the number of helpers per process the more resources we have to process queries concurrently.
The second setting is called RSVP.CONCURRENTQUERY.NUMHELPERSPERREPORT, specifies the maximum number of query execution helpers per REPORT.
The default value is 1, however, without the number of helpers per-process setting having a value it is ignored.
The third setting is called RSVP.CONCURRENTQUERY.ENABLEDFORINTERACTIVEOUTPUT. By default, any interactive report will not use concurrent query execution without this setting being specified as ‘true’. An interactive report is one you are rendering as HTML and running on the screen.
Example:
Our report has 8 queries that can be executed concurrently.
If the number of helpers per process AND the number of helpers per report are both set to 4, the report uses all available helpers and other reports will not run concurrently until that report has been completed.
However, if we set the number of helpers per report to 2 this means that our report will execute and process the first queries whilst leaving some available capacity for a second report to also benefit from concurrent query execution.
HINT: It is important to recognise that in an ideal world your number of helpers per-process value should always be higher than your number of helpers per report setting. It should be expected that a period of ‘playing’ around with these two settings is needed to achieve your best result for your reporting environment.
Application of Advanced Settings
In order to apply the settings you need to go into the Cognos Administration console and choose the System section and drill down to the ‘Batch Report Service’.
When you edit the properties of the service, choose the ‘advanced settings’ option and enter the parameters in there as per the screenshot below.
Setting the Query ‘Execution Method’
In order to ‘enable’ a query to execute in concurrent mode, you need to work through each query in your report and change the query hint called ‘execution method’ as below. In our example, we have three queries and each will be set to concurrent mode.
It is important to remember that you can only execute independent queries in this way (i.e. those not needing input from parent queries).
Executing the report and analysing the results
When you execute the report and check the xqe log files. You will see entries like the ones below.
Sequential:
Concurrent:
In the red box above you will see that all three queries are starting at almost the same time and their completions are around the same duration as when run in sequence. However, due to them running concurrently the overall execution time is dramatically reduced. This is exactly the desired effect that we are looking for.
Other considerations
It is important to note that some parts of Cognos reporting and particular types of queries cannot make use of the concurrent query technique.
Examples of this are:
- The resulting queries of any join or union queries. However, the source queries for these can be used.
- Any prompt queries, you should use the ‘Use for parameter info’ query hint instead.
- Any element of an Active Report that has queries supporting a ‘data deck’, ‘data list’ or similar data driven element. These will always been sequential regardless of the execution method setting.
It is also possible that the performance of a report can be worse after implementing these changes. The reasons this could happen would be an additional burden placed on your data source or your dispatcher settings are not optimally tuned. As stated earlier in this document, an exercise of tweaking settings and evaluating results ideally in a reasonably sized non-production environment is recommended.
