Improving performance when querying the _UserTableStat VST

Improving performance when querying the _UserTableStat VST
_UserTableStat has record sets for each user for each tablesizerange

The current implementation of the _UserTableStat Virtual System Table and does not have indexes. As a result performance issues can be expected when directly reading records from _UserTableStat when there are many users connected and tablerangesize is high.

To improve performance, use a temp-table with indexes and populate data from _UserTableStat.

Please note before considering the code example that follows:

1.  The database has to be running OpenEdge 10.1B or later where _UserTableStat and _UserIndexStat were introduced. If the database was a prior version database, updated to OpenEdge 10.1B, the update vsts utility needs to be run offline in order to access the latest VST definitions:
2.  The number of records in the _UserTableStat table is:

• By default the -tablerangesize is 50, or as specified by the -tablerangesize database startup parameter.
• -n is the total number of connections specified by the database startup parameters (default 20)
• -Mn is the total remote servers specified by the database startup parameters (default 4)

3.  If -tablerangesize is not specified or under-specified on the database startup parameters, tables that fall outside that range will not have data that can be queried.

• The full -tablerangesize setting can be found by the highest table number in the database: 

FOR EACH _file NO-LOCK WHERE _file-num < 10000 BY _file-num DESCENDING:
    DISPLAY _file-num LABEL "SET -tablerangesize = " WITH SIDE-LABELS.
    LEAVE.
END.

• The current -tablerangesize set when the database was started multi-user:

FOR LAST _TableStat no-lock:
  DISPLAY RECID(_TableStat).
END.

For further advice refer to Article How to verify if the current values of -basetable -tablerangesize are sufficient for the current schema?   

4.  Unlike _tablestat, as soon as the user is disconnected, their user stats are cleared out. 

This is because the _UserTableStat._UserTableStat-Conn is collected for the userid. When they disconnect they are cleared from memory.
When associating user stats with the userid:

• _Connect._Connect-usr = _UserTableStat._UserTableStat-Conn or 
• _Connect._Connect-Id = _UserTableStat._UserTableStat-Conn + 1

5. The same logic provided in the ABL Code example for _UserTableStat below, can be applied to the _UserIndexStat VST:

DEFINE TEMP-TABLE uistat LIKE _UserIndexStat  
INDEX _UserIndexStat IS  PRIMARY UNIQUE  _UserIndexStat-Id ASCENDING.

ABL Code example:  Using a temp-table (ustat) with indexes (_UserTableStat-Id, _UserTableStat-Num) to populate data from _UserTableStat.
   

DEFINE VARIABLE rr AS INTEGER  NO-UNDO.

DEF TEMP-TABLE ustat    LIKE _UserTableStat
    FIELD Totalactivity        AS INT64   FORMAT "->>>>>>>>>>>>>>>>>>>9"
    INDEX _UserTableStat-Num   IS PRIMARY   _UserTableStat-Num
    INDEX _UserTableStat-Id    _UserTableStat-Id    ASCENDING
    INDEX tactidx              Totalactivity        DESCENDING.

DO rr = 1 TO 3:
    PAUSE 3.
    FOR EACH _UserTableStat NO-LOCK:
        IF     _UserTableStat._UserTableStat-create <> 0 OR
               _UserTableStat._UserTableStat-delete <> 0 OR
               _UserTableStat._UserTableStat-update <> 0 OR
               _UserTableStat._UserTableStat-read   <> 0

        THEN DO:
            CREATE ustat.
            BUFFER-COPY _UserTableStat TO ustat.
            ASSIGN ustat.Totalactivity = ustat._usertablestat-create
                   + ustat._UserTableStat-delete + ustat._UserTableStat-update
                   + ustat._usertablestat-read.
        END.
    END.
    OUTPUT TO VALUE("/tmp/ttt" + STRING(rr) + ".txt").
    PUT UNFORMATTED "executed at: " STRING(TIME,"HH:MM:SS") SKIP. 
    FOR EACH ustat BREAK BY ustat._usertablestat-num
                         BY ustat._UserTableStat-Conn:
        ACCUMULATE
            ustat._usertablestat-create (TOTAL BY ustat._usertablestat-num BY ustat._UserTableStat-Conn)
            ustat._usertablestat-delete (TOTAL BY ustat._usertablestat-num BY ustat._UserTableStat-Conn)
            ustat._usertablestat-update (TOTAL BY ustat._usertablestat-num BY ustat._UserTableStat-Conn)
            ustat._usertablestat-read   (TOTAL BY ustat._usertablestat-num BY ustat._UserTableStat-Conn)
            ustat.Totalactivity         (TOTAL BY ustat._UserTableStat-num BY ustat._UserTableStat-Conn).
        IF LAST-OF(ustat._usertablestat-Conn) THEN
             DISPLAY ustat._usertablestat-num ustat._usertablestat-Conn
                 " " ACCUM TOTAL BY ustat._UserTableStat-Conn ustat._usertablestat-create
                           LABEL "CREATE"   FORMAT "->>>>>>>>>>>>>>>>>>>9"
                 " " ACCUM TOTAL BY ustat._UserTableStat-Conn ustat._usertablestat-delete
                           LABEL "DELETE"   FORMAT "->>>>>>>>>>>>>>>>>>>9"
                 " " ACCUM TOTAL BY ustat._UserTableStat-Conn ustat._usertablestat-update
                           LABEL "UPDATE"   FORMAT "->>>>>>>>>>>>>>>>>>>9"
                 " " ACCUM TOTAL BY ustat._UserTableStat-Conn ustat._usertablestat-read
                           LABEL "READ"     FORMAT "->>>>>>>>>>>>>>>>>>>9"
                 " " ACCUM TOTAL BY ustat._UserTableStat-Conn ustat.Totalactivity
                           LABEL "busyuser" FORMAT "->>>>>>>>>>>>>>>>>>>9"
             WITH DOWN FRAME yyy WIDTH 290.
        IF LAST-OF(ustat._usertablestat-num) THEN
             DISPLAY ustat._usertablestat-num
                 " " ACCUM TOTAL BY ustat._UserTableStat-num ustat._usertablestat-create
                           LABEL "CREATE"    FORMAT "->>>>>>>>>>>>>>>>>>>9"
                 " " ACCUM TOTAL BY ustat._UserTableStat-num ustat._usertablestat-delete
                           LABEL "DELETE"    FORMAT "->>>>>>>>>>>>>>>>>>>9"
                 " " ACCUM TOTAL BY ustat._UserTableStat-num ustat._usertablestat-update
                           LABEL "UPDATE"    FORMAT "->>>>>>>>>>>>>>>>>>>9"
                 " " ACCUM TOTAL BY ustat._UserTableStat-num ustat._usertablestat-read
                           LABEL "READ"      FORMAT "->>>>>>>>>>>>>>>>>>>9"
                 " " ACCUM TOTAL BY ustat._UserTableStat-num ustat.Totalactivity
                           LABEL "busytable" FORMAT "->>>>>>>>>>>>>>>>>>>9"
                 WITH DOWN FRAME zzz WIDTH 290.
    END.
    OUTPUT CLOSE.
    EMPTY TEMP-TABLE ustat.
END.

Progress Article : 

 How much memory is needed for -tablerangesize and -indexrangesize