UMP USM portlet stuck at loading window after upgrade to UIM 8.51/UIM 8.51SP1

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

As seen in database profiler for every client that connects we are seeing this query being run against the database which is MySQL 5.6 but this query never ever ends/loads. 

If you manually try to run the query also it does not complete.The query is also very long.

SELECT dev.cs_id, 

     meci.master_id, 

       cim.ci_metric_id, 

       dev.dev_id 

FROM   CM_DEVICE dev 

         JOIN 

       CM_CONFIGURATION_ITEM ci ON ci.dev_id = dev.dev_id 

         JOIN 

       CM_CONFIGURATION_ITEM_METRIC cim ON cim.ci_id = ci.ci_id 

         LEFT JOIN 

       CM_CONFIG_ITEM_TO_MASTER meci ON meci.ci_id = ci.ci_id 

WHERE  1=1 

 and  (dev.dev_id IN ( 'D0ece9a1d810dd039c56b1c1d857603d0','D043BA3B5CB3B253453AA98BD717FF42A','D5323bd4bb84c2502a8ff28336cdb2710',

 'DD522CCA63542EF4B7026ABF8DB712C63','D598ecd177fc8207b4d27a01fb1b921ad','DC81FD47B3BE5EA2C8074DB48AB4F5112','D9678ED856D87484ADB7651A50EBA3388',

 'D3ECF2FF6DE9B39685AA6DB3A861C405C','D530F4BF1F779C61F59A795455F202D30','D22de76ebc89d542e8d24c3489bdcc9a3','D901DABBFDCE8C586F4D542F7EE6E22C0',

 'DE009516F2166DDDFB32B02D257EE8247','DCC916CA4425393CCAF226214DE05C9A4','DEB452E298CEED4924C08C308C5127149','D4ef9284f9cf61aa4e9175f06f65a43ca','

 'D3C1888E01ADD067068FA93675A1FB856',

 ...

 ...

 ...

'D330A86A8F4BC4D69FD3FCB1','D770A32F51484D51E03F5CA34DC6A999F','DBE43840EC35182B559D7EBE796F602B4','DD956A1E181F30ECBE61268D51F413EF1' ) )

 

Environment

UIM 8.5P SP1Mysql database 5.6

Resolution

Added a non-unique index for the column name ‘ci_id’ to the table ‘CM_CONFIG_ITEM_TO_MASTER’ 

create index cm_config_item_to_master_idx01 on cm_config_item_to_master(ci_id); 

 

After this the USM was loading faster within 15-30 secs