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

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/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

Resolution

1- Login to CABI as "superuser" 

If External: http(s)://<cabiServer>:<port>/jasperserver-pro/
or if bundled:
http(s)://<cabiServer>:<port>/cabijs/

 

2- Go to the "Manage" menu > "Server Settings" 

3- Enable these settings in "Ad-Hoc" settings

4- Run the Ad Hoc Report and when you encounter a slow response, use the "Show SQL" button to see the query ( View 1558699586361000106024_sktwi1f5rjvs16jsm.gif attached to this document):

 

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

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:

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