UIM - Slow running Queries in CABI when creating an Ad Hoc report

Document ID : KB000106024
Last Modified Date : 11/07/2018
Show Technical Document Details
Slowness in CABI (Jaspersoft) Ad-Hoc reports can happen for a few reasons.
This can be a burden when trying to develop custom reports.
What can we do to improve the performance of Ad Hoc reports (especially during the process of developing new reports)?
UIM 8.5.1 Sp1
cabi or cabi_external probe 3.20
Bundled or External CABI Reporting
1- Login to CABI as "superuser" (default password of "superuser")
If External: http(s)://<cabiServer>:<port>/jasperserver-pro/
or if bundled:
UIM CABI Login as superuser
2- Go to the "Manage" menu > "Server Settings"
Manage Server Settings in CABI to enable Debug and Logging

3- Enable these settings in "Ad-Hoc" settings
User-added image

4- Run the Ad Hoc Report and when you encounter a slow response, use the "Show SQL" button to see the query:
Show SQL Button in Ad Hoc Report

5- The query can be used in MS SQL Mgmt. Studio (or Oracle SQL Tuning Advisor) to get the explain plan and statistics, as well as index suggestions.

Additional Information:
CA UIM - CABI Product Documentation:

It is also possible to have the queries logged to the "/opt/CA/SharedComponents/CA Business Intelligence/apache-tomcat/webapps/jasperserver-pro/WEB-INF/logs
jasperserver.log" file:
Debug in jaspersoft
For example:18-07-10 07:47:53,562 DEBUG JRJdbcQueryExecuter,pool-6-thread-6115:362 - SQL query string: select count(distinct(
       CASE WHEN sqs.sampletime is not NULL 
               THEN ccs.cs_id 
               ELSE NULL
       END)) as active_device_count, 
       count(distinct (ccs.cs_id)) as total_device_count 
from cm_computer_system ccs 
       left join cm_device cd 
               on ccs.cs_id = cd.cs_id 
       left join cm_configuration_item cci 
               on cd.dev_id = cci.dev_id 
       left join cm_configuration_item_metric ccim 
               on cci.ci_id = ccim.ci_id 
       left join s_qos_data sqd 
               on ccim.ci_metric_id = sqd.ci_metric_id 
       left join s_qos_snapshot sqs 
               on sqd.table_id = sqs.table_id 
               and CAST(sqs.sampletime as datetime) >= DATEADD(MINUTE, -30, CURRENT_TIMESTAMP)​

Display a MS SQL Query Execution Plan:

Oracle SQL Tuning - Analyzing SQL with Oracle SQL Tuning Advisor: