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.
  lv-MaxTable = RECID(_TableStat).

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

  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
  FIRST _File FIELDS(_File-Name _Hidden)
  WHERE _File._File-number  = _UserTableStat._UserTableStat-Num
  AND _File._Hidden = FALSE


Note: iUsr is the _Connect._Connect-Usr


Something similar can be achieved for _UserIndexStats. 

Copyright © 2019 James Palmer