Kbase P161995: Accessing _Lock VST takes a long time
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
15/12/2010 |
|
Status: Verified
SYMPTOM(s):
Accessing _Lock VST takes a long time
The time taken to read _lock does not scale linearly to the number of locks. It scales exponentially.
Reading _ lock is very slow.
Slow performance using the _Lock VST table.
-L (Lock table size) is large
FACT(s) (Environment):
All Supported Operating Systems
Progress/OpenEdge Product Family
Progress 9.x
OpenEdge 10.x
CAUSE:
This is expected behavior. The _Lock table is not indexed in any way so expected to be slow. Reading the lock table VST is slow because the underlying data structures of the lock table are not organized as a simple table. They are significantly more complex and also the structures change while they are being read. That makes the conversion into a flat table slow when the lock table is large.
FIX:
Once you get to a record with an unknown value for the lock status field you should exit the query as there are no more records to deal with.
Queries on _Lock can be speed up by aborting the loop if a field other than _lock-id becomes unknown. This will ensure you read the data only for the locks that are actually in use, instead of always reading -L records.
So for example:
FOR EACH _Lock:
IF _Lock._Lock-Usr = ? THEN LEAVE.
/* other code here */
END.
Specifying the check for unknown values in the WHERE clause is less effective due to how the query mechanisms work in this particular instance.
And obviously, it's not going to improve things much if you are locking large numbers of records?
PS: In some cases, the _userlock will suffice. It shows the first 256 locks associated with a particular user and is very fast since it does it in one request and stores the entries in an array.
The solution to the _lock issue is not currently scheduled.
Another possible solution to get around of this scanning only the non empty _lock records using a code similar to solution P122034.