User Table Stats

If you've got your -tablerangesize parameter set big enough (to cover all the tables in your database), then you've got loads of information about who is using each table the most right at your fingertips. Unfortunately, the more tables you have, the more records there are in the _UserTableStat table, thus the more reads you have to do and it's not particularly well indexed. 

Hope is at hand though. Each time a user logs on, enough _UserTableStat records are created to cover each table in the database for that user. And they're maintained in an order which we can predict. 

So find the last _TableStat record first:

FIND LAST _TableStat.
ASSIGN
  lv-MaxTable = RECID(_TableStat).

Now we can work out the range of records for the user:

ASSIGN
  lv-UserStart = (lv-MaxTable * iUsr) + 1
  lv-UserEnd   = lv-UserStart + (lv-MaxTable - 1).

Now it's a simple job to find the user's information:

FOR EACH _UserTableStat
  WHERE _UserTableStat._UserTableStat-Id GE lv-UserStart 
  AND   _UserTableStat._UserTableStat-Id LE lv-UserEnd
  NO-LOCK,
  FIRST _File FIELDS(_File-Name _Hidden)
  WHERE _File._File-number  = _UserTableStat._UserTableStat-Num
  AND _File._Hidden = FALSE
  NO-LOCK:

END.

Note: iUsr is the _Connect._Connect-Usr

 

Something similar can be achieved for _UserIndexStats. 

Copyright © 2018 James Palmer
GS RU