Indexes and System Migrations
Our MultiValue environment has been commercially available for almost 40 years. Serial-based systems, aka "native" systems ruled for the first 25 years with network systems arriving during the last 20 years. The comparisons between these two styles of systems is quite revealing.
The maximum "native" system was somewhere around 50-100 users, 32MB of main memory and perhaps 200-800mb of hard drive. These numbers pale when compared to the incredible resources of a modern system, although the user count may remain similar.
Today's network applications come from two sources: A brand new installation or a migration from a "native" system. If the new installation was written for the network system, or heavily modified towards it, then it may have addressed indexes in several ways.
One of the originally installed features to native applications were XREF files. These were manually maintained by the programmer's application and not the system.
The programmer had to update the XREF file with the appropriate fields to facilitate an easy lookup later using the cross referenced values. The programmer also had to make sure that the XREF file behaved properly if the source field was changed or the record was deleted.
Thus, there was always a manual rebuild program to go through the source file and rebuild the XREF file from scratch. Despite the best intentions, these manual XREF files do go astray, and the rebuild programs are almost mandatory.
Today's MultiValue systems all have system-level INDEX commands to let the system take care of the indexing for us. While there is some consumption of space with either style of indexing, system-level INDEX commands participate much better in both updating the index and using the index.
Manual XREF files do not participate in EDITOR changes to the fields. System Indexes do. Manual XREF files don't normally participate with SELECT statements. System Indexes do.
There are two kinds of fields that are usually cross referenced: simple and complex. The simple fields are dates, product codes, customer numbers etc. as their values are literal and easily policed by the application. These values are easily cross referenced by older XREF files or system indexes.
Text-based fields are more complicated. Instead of being a single literal value, it may often be cross referenced by its separate words. A good example is the Customer Name field.
Being of no obvious structure, it often is also one of the most difficult fields to cross reference as it is usually human data and not validated by the application. Also, one would rarely cross reference the entire value of a text field in either method.
Supplemental to cross referencing Name style fields was a separate table of words to not cross reference. Words like THE, AN, A, COMPANY etc. returned too many choices. With space at a premium, omitting these keywords saved time as well.
Then, logic routines would parse the stored Name and update the XREF file with the words of the Name. Likewise, these routines would parse the inquiry request as well.
The purpose of these XREF files was to not have to run an A-Z SELECT statement through the entire CUSTOMER file. The statement QSELECT CUSTOMER-XREF SMITH was much faster — seconds rather than minutes.
If there were 40 customers having SMITH in their name, they would be returned immediately with the QSELECT request. The SELECT request would use square brackets or the LIKE operator and may accidentally grab GOLDSMITH or BLACKSMITH as well.
Ignoring text fields and theory, I will focus on the replacement of the XREF file style cross reference with the system index command(s) for simple fields..
Using the CREATE-INDEX command on the file/field will build and make available the index for that field (Some platforms require separate Create and Build commands. See your product's documentation for details.). Standard SELECT statements that filter for one value of that field will instantly become much faster as they inherit the effect of the index without additional programming.
The maintenance programs that used to manage the XREF files can then be changed to retire that function. Those programs that use the XREF file will have to be changed slightly. If the number of XREF'd files and fields are small, this is a manageable task.
The QSELECT statement that used the XREF file could be replaced with a standard SELECT statement. In Data/Basic, I use a subroutine called GET.IDS(TCL, IDS) that will process the TCL statement and return the item-ids in IDS. Thus the QSELECT ORDER-XREF 123456 statement (orders for customer 123456) could use SELECT ORDER WITH CUSTNO = "123456". Sidebar: My sub GET.IDS allows me to have one called reference to a program that I modify to suit the local MultiValue flavor it's on.
As I spend time both in ex-native systems as well as contemporary systems, I see a wide variety of XREF files. Many often contain orphaned records that cause me to find or write a rebuild program. Doing this enough has cause me to replaced the XREF files with system indexes and let the system do the work.
In conclusion, there are two kinds of fields (simple and complex) and two kinds of methods (XREF and INDEX). Simple fields can be easily upgraded to INDEX while complex fields may remain with XREF.
Also, the use of square brackets in an English statement or the LIKE statement may not be consistent across MultiValue platforms or utilize the INDEXes. Plus, INDEXes may not work with multiple filters or with multiple values for one filter.
As in all upgrades, proceed cautiously to see if the benefits appear after the change.
