Overview

QUICKDEX.MFS and RIGHTDEX.MFS are programs that provide a quick method to obtain a list of all keys in a table, and both call the subroutine LRDEX.SUB. This stored procedure maintains a sorted list of keys in a special record named %RECORDS%. You can read this list in one operation. For example:

Keys = Xlate(‘MYTABLE’,’%RECORDS%’, ”, ‘X’)

However, there is a cost to maintaining this record. LRDEX.SUB must lock, read, scan, and if needed update the %RECORDS% row every time a row is written or deleted. This creates a potential bottleneck in a busy table. READNEXT operations must scan the result list to remove ‘%RECORDS%’.  The read method must rebuild the %RECORDS% row if it does not exist. The special %RECORDS% row is not compatible with other BFS’s. The LRDEX.SUB program originated with early versions of Advanced Revelation. At that time, storing a list of keys offered a substantial performance benefit. The benefit justified the cost for smallish tables that rarely change. These tables often populate dropdowns for data entry forms, where a quick read of all keys is useful.

When reviewing LRDEX.SUB for OpenInsight 10 we realized it needed revision. The sorting depended on the Arev language sets feature and was incompatible with DICT.MFS, causing crashes.  Rebuilding the %RECORDS% rows was not optimized. We wanted to address these and other issues and improve performance.

Changes

We started by optimizing the %RECORDS% rebuild. We needed to perform a rapid table scan and write a sorted list of keys.  OI10’s RList already provides optimized table scans, so we reused that with V119 to sort the list.  Surprisingly, reading %RECORDS% with and without the new rebuild showed a negligible performance difference!  We realized that storing a list of keys no longer offered much benefit.  We could remove the overhead of maintaining the list without sacrificing performance.  Thus, the revised LRDEX.SUB will perform a table scan when you ask for %RECORDS%. It does not lock or write anything. It still checks for %RECORDS% in a READNEXT. It will delete the record if it finds it. Quickdex.mfs remains best suited to smaller tables. However, it will not fail unexpectedly as a table grows, and it will work with any of the BFS’s.

Sharing data with OpenInsight 9

Note that the OI10 version of LRDEX.SUB is not compatible with the OI 9 version. OI9 requires the %RECORDS% row, OI10 deletes it. For that reason we soft-coded LRDEX.SUB so you can specify which version to use with a configuration setting.  If you need share tables between OI9 and OI10 you must configure OI10 to use the same logic as OI9.  Create a record in the SYSENV table named “RTI_CFG_LRDEX” with “RTI_LRDEX_SUB_9” in the first row.

Summary

OpenInsight 10 offers a simplified Quickdex/Rightdex capability with similar benefits and without the shortcomings of the OI9 version. The simplified version cannot be used on tables shared with OI9. You can configure OI10 to use the OI9 version if needed.