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?
Release : 12.x and 21.x
Component: Automation Engine
DB limitation: huge amount of User records in table OH, impossible to improve the performances with the current two queries performed by the AWI.
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="0000100674"><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="0000100674"><request inactive="0" name="getuserlist" src="prm"/>...
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.
In the future versions of Automic 23.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.