Question & Answer
Question
How can I make my report run faster?
Cause
There are many different possible causes for a report taking a long time to run. While it is not possible to discuss all of them here, this TechNote will cover the most common reasons. If the suggestions here do not help you get the report to produce as quickly as desired, you may need assistance with the report or model design, or with capacity planning in your environment.
In those cases you would need to go to:
https://www.ibm.com/analytics/us/en/services/advanced-analytics.html?lnk=hm
and use the 'Contact Us' button to reach out to IBM Analytics Services for assistance.
In those cases you would need to go to:
https://www.ibm.com/analytics/us/en/services/advanced-analytics.html?lnk=hm
and use the 'Contact Us' button to reach out to IBM Analytics Services for assistance.
Answer
A - Server side Issues:
1 - Tuning
A default Cognos Analytics install is limited to running 2 BIBusTKMain.exe processes. This is the maximum that a system with a single CPU core would be able to run and it limits the number of reports that can be run simultaneously. If the server has more than one CPU core it may be able to launch more BiBus processes than that. The upper limit is 2 per CPU core, however there also has to be sufficient RAM for each process.
Each BIBus process should have 4G of physical RAM available to it that is over and above the RAM required by the Query service, other Cognos processes, and whatever else puts demands on the system.
For most systems the default settings will not make effective use of the hardware resources. This can create a performance bottleneck when the server is under load. You also do not want to have so many BIBus process running that you reach a point where they require more physical RAM than what the server can provide.
The upper limit on the number of BIBus processes that Cognos will allow to run at the same time is controlled by the Max Number of Processes tuning parameter. This setting exists for both the Report Service and the Batch Report Service, and is divided into settings for peak and non-peak hours. So you have in fact 4 settings:
Maximum number of processes for the batch report service during non-peak period
Maximum number of processes for the batch report service during peak period
Maximum number of processes for the report service during non-peak period
Maximum number of processes for the report service during peak period
Keep in mind that there are 2 settings for each period, so if the number of processes for the batch report service during peak period is set to 2, and the number of processes for the report service during peak period is set to 4, you could have up to 6 BIBus processes running at the same time during the peak period.
2 - Environment
Cognos cannot produce a report faster than the database server is able to supply the data for the report over the network. The database server performance and network performance then impact report performance.
A way to test if this is an issue is to select a slow running report with a single report query and get the SQL generated for the report. Then on the report dispatcher open a standalone query tool (SQLPlus, Toad, SQuirreL SQL etc.) and run the native SQL from there, Compare the time it takes to run the query that way with the time it takes the report to run. A large difference may indicate an issue like local processing (see below), but if the times are fairly close you may want to look at ways to improve the database and network performance.
Another environment change that may help is to add more dispatchers on different servers.
B - Report Design Issues
1 - Local processing
When a report is run, Cognos looks at the requirements of the data container in the report, the query in the report that the data container uses and the metadata in the report package. From that it generates a query in Cognos SQL. That query is then converted to the native SQL of the data source and sent to the database. Sometimes there are functions used in a report that cannot be converted to native SQL and sent to the database. In that case the native SQL is formed without those elements, and after Cognos receives the results it applies the functions that could not be sent down.
This has the potential of creating large performance issues. Say for example the function that can’t be sent to the database is inside a filter on a query. The native SQL then will not have that filter. It could result in a much larger set of rows being returned than needed, and Cognos will have to consume much time and system resources to process this large result set down to what is actually needed for the report.
An easy way to test if this is affecting a particular report is to set the Processing property on every query in the report to Database Only, then run the report. If the query requires local processing this change will result in the error:
UDA-SQL-0458 PREPARE failed because the query requires local processing of the data
Along with that error will be additional messages that will detail what function in the report requires local processing. Usually it is possible to change the report expression in a way that will allow it to be sent to avoid local processing.
2 - Output format
The process Cognos follows in generating report output depends in part on the output format being used. With an HTML report Cognos will fetch the data required to display the first page of the report in the browser window. When the user clicks on Page Down it will query more data and generate the next page. With file based formats like PDF and Excel Cognos must fetch all the data and build the entire output file before anything can be presented to the user. In some case this creates the appearance that the report runs much faster when it is run to HTML.
To get a better idea of the time required for a report when run to HTML, run the report to HTML in the background and save the output to the content store, then check the run history to establish how long it takes. There will likely still be a difference between running the same report to HTML and to other formats. If the report is displaying a large result set the difference could be significant. This is due in part to inherent differences between the different formats.
1 - Tuning
A default Cognos Analytics install is limited to running 2 BIBusTKMain.exe processes. This is the maximum that a system with a single CPU core would be able to run and it limits the number of reports that can be run simultaneously. If the server has more than one CPU core it may be able to launch more BiBus processes than that. The upper limit is 2 per CPU core, however there also has to be sufficient RAM for each process.
Each BIBus process should have 4G of physical RAM available to it that is over and above the RAM required by the Query service, other Cognos processes, and whatever else puts demands on the system.
For most systems the default settings will not make effective use of the hardware resources. This can create a performance bottleneck when the server is under load. You also do not want to have so many BIBus process running that you reach a point where they require more physical RAM than what the server can provide.
The upper limit on the number of BIBus processes that Cognos will allow to run at the same time is controlled by the Max Number of Processes tuning parameter. This setting exists for both the Report Service and the Batch Report Service, and is divided into settings for peak and non-peak hours. So you have in fact 4 settings:
Maximum number of processes for the batch report service during non-peak period
Maximum number of processes for the batch report service during peak period
Maximum number of processes for the report service during non-peak period
Maximum number of processes for the report service during peak period
Keep in mind that there are 2 settings for each period, so if the number of processes for the batch report service during peak period is set to 2, and the number of processes for the report service during peak period is set to 4, you could have up to 6 BIBus processes running at the same time during the peak period.
2 - Environment
Cognos cannot produce a report faster than the database server is able to supply the data for the report over the network. The database server performance and network performance then impact report performance.
A way to test if this is an issue is to select a slow running report with a single report query and get the SQL generated for the report. Then on the report dispatcher open a standalone query tool (SQLPlus, Toad, SQuirreL SQL etc.) and run the native SQL from there, Compare the time it takes to run the query that way with the time it takes the report to run. A large difference may indicate an issue like local processing (see below), but if the times are fairly close you may want to look at ways to improve the database and network performance.
Another environment change that may help is to add more dispatchers on different servers.
B - Report Design Issues
1 - Local processing
When a report is run, Cognos looks at the requirements of the data container in the report, the query in the report that the data container uses and the metadata in the report package. From that it generates a query in Cognos SQL. That query is then converted to the native SQL of the data source and sent to the database. Sometimes there are functions used in a report that cannot be converted to native SQL and sent to the database. In that case the native SQL is formed without those elements, and after Cognos receives the results it applies the functions that could not be sent down.
This has the potential of creating large performance issues. Say for example the function that can’t be sent to the database is inside a filter on a query. The native SQL then will not have that filter. It could result in a much larger set of rows being returned than needed, and Cognos will have to consume much time and system resources to process this large result set down to what is actually needed for the report.
An easy way to test if this is affecting a particular report is to set the Processing property on every query in the report to Database Only, then run the report. If the query requires local processing this change will result in the error:
UDA-SQL-0458 PREPARE failed because the query requires local processing of the data
Along with that error will be additional messages that will detail what function in the report requires local processing. Usually it is possible to change the report expression in a way that will allow it to be sent to avoid local processing.
2 - Output format
The process Cognos follows in generating report output depends in part on the output format being used. With an HTML report Cognos will fetch the data required to display the first page of the report in the browser window. When the user clicks on Page Down it will query more data and generate the next page. With file based formats like PDF and Excel Cognos must fetch all the data and build the entire output file before anything can be presented to the user. In some case this creates the appearance that the report runs much faster when it is run to HTML.
To get a better idea of the time required for a report when run to HTML, run the report to HTML in the background and save the output to the content store, then check the run history to establish how long it takes. There will likely still be a difference between running the same report to HTML and to other formats. If the report is displaying a large result set the difference could be significant. This is due in part to inherent differences between the different formats.
Another format related factor is that with the Excel output format, the report must be conformed to the cell based structure of an Excel spreadsheet. The processes of doing this conversion will add to the time needed to render the output file. On the other end of the scale, XML and CSV formats are in effect text files so they can be rendered much faster.
3 - Outer joins
Outer joins and cross products can arise either from the model or from the report design. In either case then tend to cause poor performance and should be avoided.
4 - Crosstabs with nested rows and charts
When report contains a crosstab or chart, Cognos will get the data from the query then build a cube in memory to use for the data container. If the amount of data being presented is large, or if there are many nested items in the data container, the size and complexity of the cube increases and in some cases can affect performance. It may work better to use drill up/down or drill through to move from higher level data to more focused details on a smaller segment.
5 - Zero suppression
The default method of zero suppression filters out individual records that are zero, but also filters out any detail records where aggregated values come to zero as well. Suppressing the aggregated values can slow down performance sometimes. A global change can be made so Cognos will only suppress individual records that are zero.
To make this change, do the following:
- Stop the Cognos service
- Rename the file "ansproperties.xml.sample" under the configuration folder directory to "ansproperties.xml"
- Add the following entry to the end of the file, being careful that it is placed before the </ans_property> tag at the end of the file:
<structure>
<property>Enable8Dot2Suppression</property>
<value type="long">1</value>
</structure>
- Save the file and restart the Cognos service
6 - Concurrent Query Execution
Using concurrent query execution might speed up a report, but It is also possible that it could slow it down. There is no way to tell in advance what the result of using concurrent query will be in a specific case. You would have to test it both ways to find out if it will be worth it or not.
One reason for this is that using Concurrent Query Execution places a higher demand on the system resource both on the Cognos server and the database server. If the demand it creates is greater than the capacity of either server, the end result is likely to harm performance rather than help it.
Even when there are sufficient system resources, using concurrent query mode does not mean every query will run in parallel. There are a number of situation where queries must be run sequentially no matter what. For example when you have a master-detail relationship, the master query must be run before the detail query. It is not possible to run them in parallel because the results of the master query are needed to form the SQL for the detail query.
When using concurrent query mode, Cognos will parse through the report spec at runtime to determine which (if any) of the queries flagged for concurrent query can be run concurrently and this adds slightly to the run time of the report. If enough queries can be run concurrently then that can offset the time taken by this step.
7 - Master-Detail Optimization (DQM only)
In CQM, when there is a master-detail relationship, Cognos will run the detail query for each row returned by the master query. This can result in the detail query being run a large number of time.
In DQM there is a governor in the Framework Manager model named ‘(DQM) Master-Detail Optimization’. When this governor is set to ‘Cache Relational Detail Query’ then the DQM engine is able to process master-detail relationship more efficiently than they can be in CQM.
8 - Parameter Info
When a report is run, Cognos will first parse the entire report spec to determine if there are required parameters that do not have a prompt created for them on a prompt page. If it finds any, Cognos will generate a prompt page for them and present that to the user first.
If the report spec is large and complex, it may take Cognos a few seconds to parse through it, causing a delay before anything is displayed to the user. If you set the ‘Use parameter info’ property on a report query to No, then that query will not be parsed. If you set all queries like that, Cognos will not be delayed from presenting the first prompt page in the report. The risk is that if there is a required parameter not covered on the prompt pages it will cause the report to fail rather than present a Cognos generated prompt page.
You may also want to check the performance related Proven Practice documents (see link below).
3 - Outer joins
Outer joins and cross products can arise either from the model or from the report design. In either case then tend to cause poor performance and should be avoided.
4 - Crosstabs with nested rows and charts
When report contains a crosstab or chart, Cognos will get the data from the query then build a cube in memory to use for the data container. If the amount of data being presented is large, or if there are many nested items in the data container, the size and complexity of the cube increases and in some cases can affect performance. It may work better to use drill up/down or drill through to move from higher level data to more focused details on a smaller segment.
5 - Zero suppression
The default method of zero suppression filters out individual records that are zero, but also filters out any detail records where aggregated values come to zero as well. Suppressing the aggregated values can slow down performance sometimes. A global change can be made so Cognos will only suppress individual records that are zero.
To make this change, do the following:
- Stop the Cognos service
- Rename the file "ansproperties.xml.sample" under the configuration folder directory to "ansproperties.xml"
- Add the following entry to the end of the file, being careful that it is placed before the </ans_property> tag at the end of the file:
<structure>
<property>Enable8Dot2Suppression</property>
<value type="long">1</value>
</structure>
- Save the file and restart the Cognos service
6 - Concurrent Query Execution
Using concurrent query execution might speed up a report, but It is also possible that it could slow it down. There is no way to tell in advance what the result of using concurrent query will be in a specific case. You would have to test it both ways to find out if it will be worth it or not.
One reason for this is that using Concurrent Query Execution places a higher demand on the system resource both on the Cognos server and the database server. If the demand it creates is greater than the capacity of either server, the end result is likely to harm performance rather than help it.
Even when there are sufficient system resources, using concurrent query mode does not mean every query will run in parallel. There are a number of situation where queries must be run sequentially no matter what. For example when you have a master-detail relationship, the master query must be run before the detail query. It is not possible to run them in parallel because the results of the master query are needed to form the SQL for the detail query.
When using concurrent query mode, Cognos will parse through the report spec at runtime to determine which (if any) of the queries flagged for concurrent query can be run concurrently and this adds slightly to the run time of the report. If enough queries can be run concurrently then that can offset the time taken by this step.
7 - Master-Detail Optimization (DQM only)
In CQM, when there is a master-detail relationship, Cognos will run the detail query for each row returned by the master query. This can result in the detail query being run a large number of time.
In DQM there is a governor in the Framework Manager model named ‘(DQM) Master-Detail Optimization’. When this governor is set to ‘Cache Relational Detail Query’ then the DQM engine is able to process master-detail relationship more efficiently than they can be in CQM.
8 - Parameter Info
When a report is run, Cognos will first parse the entire report spec to determine if there are required parameters that do not have a prompt created for them on a prompt page. If it finds any, Cognos will generate a prompt page for them and present that to the user first.
If the report spec is large and complex, it may take Cognos a few seconds to parse through it, causing a delay before anything is displayed to the user. If you set the ‘Use parameter info’ property on a report query to No, then that query will not be parsed. If you set all queries like that, Cognos will not be delayed from presenting the first prompt page in the report. The risk is that if there is a required parameter not covered on the prompt pages it will cause the report to fail rather than present a Cognos generated prompt page.
You may also want to check the performance related Proven Practice documents (see link below).
Related Information
[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSTSF6","label":"IBM Cognos Analytics"},"ARM Category":[{"code":"a8m500000008d78AAA","label":"Reporting"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)"}]
Was this topic helpful?
Document Information
Modified date:
02 July 2023
UID
swg22013793