Displaying the Users and Connections (getuserlist) takes long when many users are defined

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

On an AE environment there are almost 100000 users defined so it takes almost 24 seconds to display the list of  Administration - User Management - Users and almost 16 seconds to display the list of Administration - User Management - Connections

Example of messages about this in the WP log:

U00003434 Server routine  'UCDS_R/getuserlist' required '0' minutes and '8,939' seconds for processing.

Is there any way to optimize this query?

Environment

Release : 12.x and 21.x

Component: Automation Engine

Cause

DB limitation: huge amount of User records in table OH, impossible to improve the performances with the current two queries performed by the AWI.

Investigation:

When enabling the trace tcpip=2,db=4 in the WPs and xml=3 in the AWI, we could figure out where the time was spent while trying to display the list of Users

The DB query takes 3.5seconds:

20221229/140035.145 - SELECT USR.*, OH_Idnr, OH_Name, OH_Client, OH_TZ, OH_Archive1, OH_Archive2 FROM USR, OH WHERE OH_OType = ? AND OH_Idnr = USR_OH_Idnr AND OH_DeleteFlag = 0 AND USR_Active >= ? AND (OH_Idnr >= 100000 OR OH_Idnr = 5) ORDER BY OH_Client, OH_Name 
20221229/140035.250 - UCUDB32 SLCB RET 0000 HSTMT: 0x0000001e8d9ff0 VALUE: 0x00000000000032 ALL:  0.10470 DB:  0.10421 ODBC:  0.00005 UDB:  0.00044
20221229/140038.743 - UCUDB32 READ RET 0001 HSTMT: 0x0000001e8d9ff0 VALUE:            (nil) ALL:  0.00003 DB:  0.00000 ODBC:  0.00000 UDB:  0.00003
20221229/140038.743 - UCUDB32 CLST RET 0000 HSTMT: 0x0000001e8d9ff0 VALUE:            (nil) ALL:  0.00012 DB:  0.00000 ODBC:  0.00008 UDB:  0.00004

And it takes another 3.5 seconds to generate the XML

20221229/140038.743 -     STRT UCUSCRF      ah_idnr=0000000000 client=00000 opcode=0000020140
20221229/140042.348 -     EXIT UCUSCRF      RET: 0000000000  TIME: 0003,60471  RETTEXT=' '

Hence, there's not much that can be done as currently AWI performs two similar queries to display the list of Users (one with active and one with inactive users):

20221214/093737.494 - U00003434 Server routine  'UCDS_R/getuserlist' required '0' minutes and '4,897' seconds for processing.
20221214/093737.494 -          <uc-env clientVersion="12.3.9+build.1655235965775" request="prm_24" session="0000000206692044" useridnr="0000X00XXX"><request inactive="1" name="getuserlist" src="prm"/>...
20221214/093742.404 - U00003434 Server routine  'UCDS_R/getuserlist' required '0' minutes and '4,884' seconds for processing.
20221214/093742.404 -          <uc-env clientVersion="12.3.9+build.1655235965775" request="prm_27" session="0000000206692044" useridnr="0000X00XXX"><request inactive="0" name="getuserlist" src="prm"/>...

Resolution

The query cannot be optimized adding an index to the associated tables, so unfortunately there's nothing that can be done from the Automation Engine side for the moment.

The slowness is caused by the fact that there are almost 100000 Users defined in this Environment, only reducing the amount of Users defined would increase the performances.

Additional Information

In the future versions of Automic 24.x or superior, it's possible that this query is done differently via a JCP or Rest API and in that case maybe this will be improved to perform only one query instead.