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
Introduction:
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)?
Environment:
UIM 8.5.1 Sp1
cabi or cabi_external probe 3.20
Bundled or External CABI Reporting
Instructions:
1- Login to CABI as "superuser" (default password of "superuser")
If External: http(s)://<cabiServer>:<port>/jasperserver-pro/
or if bundled:
http(s)://<cabiServer>:<port>/cabijs/
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:
https://docops.ca.com/ca-unified-infrastructure-management/8-5-1/en/installing-ca-uim/ca-business-intelligence-with-ca-uim

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:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/display-an-actual-execution-plan?view=sql-server-2017

Oracle SQL Tuning - Analyzing SQL with Oracle SQL Tuning Advisor:
https://docs.oracle.com/database/121/TGSQL/tgsql_sqltune.htm#TGSQL540