OneClick reporting mysql processes hang for group reports run on the Jasper server

Products

• CA Spectrum

Issue/Introduction

All reports run okay except for group reports which hang in Jasper parameters page while loading the global collections to the drop down list.

Environment

Release : 21.2

Component : Spectrum Reporting

Cause

We reproduced the issue i.e., when they run the report it takes almost 15 mins to finish it. On the mySql prompt we ran "show full processlist" to see the following query is taking more time :

select distinct gc.gc_name from globalcollection gc inner join model m on m.mtype_h=66676 and m.model_name=gc.gc_name inner join reporting.v_security_string_accessibility_by_landscape sec on (m.landscape_h=sec.landscape_h) and (m.security_string=sec.security_string) and (is_security_enabled()= FALSE or sec.user_name='c5312508') ORDER BY gc.gc_name;

We then ran this same query with explain keyword prefixed to the query and we could see the table security_string_accessibility_by_landscape has 2.6 million rows in it and for some reason the joins query is consuming is more time. Then we ran the next query :

show create table security_string_accessibility_by_landscape;

We noticed that there is no indexing for this table and we suspect because of no indexing this query is taking more time to return.

 

 

Resolution

We resolved by adding the following mysql commands to add an index to speed up processing for group reports.

Please alter the security_string_accessibility_by_landscape table like below and then retry hopefully it should resolve the issue:

    1. mysql>ALTER TABLE security_string_accessibility_by_landscape ADD INDEX idx_username( user_name );

    2. mysql> ALTER TABLE security_string_accessibility_by_landscape ADD INDEX idx_model_landscape_secstring( landscape_h, security_string );