15 ObjectPAL: Autoincrement replacement

VERSIONS: All

SUBJECT: Generating unique identities

BY: JON KRANES

If you really want to optimize the system to the max, there is a clever trick I heard of:

Use a table with n records (e.g. 5). Initalize the table with values from 1 to n. Then use code like this to get the next number:

method GetNextID() LongInt
; assume tc is a global tcursor
var
  liNextId longint
endvar
while not tc.lockRecord()
   if tc.eot() then
      tc.home()
   else
      tc.nextRecord()
   endif
endWhile

liNextID = tc."NextID"
tc."NextID" = tc."NextID" + tc.nRecords() 
; increment by # of records in the table, e.g. 5
tc.unlockRecord()
return liNextID

endMethod

Please note this code is off the top of my head -- the original is probably better but this is the basic idea. The principle is that instead of everyone fighting over one record to lock (or one SetBatchOn that locks the whole system), multiple nextID records can be locked at the same time so users spend less time waiting. The more users you have, the more records you can put in the NextID table to spread out the conflicts.

You could also, I suppose, handle multiple autoincremented tables by putting an index on the table and using SetRange().

I've always thought this a clever idea but have never bothered to use it because we don't have enough users to make any difference (15-20 max).

This will give unique Ids but not in sequence. Also if traffic is light you will skip a lot of numbers. You could periodically resort the nextID table so that the smallest numbers are at the top. Then the unused ids will get used: e.g. if you have used 1,6,11,16 then resort the table so that the record with nextID 2 is first, then IDs 2,7,12,17 will get used. Again, this is off the top of my head, not a tested concept.

Looking through my notes I find the idea came originally from Tarik Ghbeish.

To index