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