Batch vs Online Indexes (Embedded Only)
Batch indexes are only supported with the embedded database. If you are using an SQL database, a more flexible alternative, Temporary Indexes, is available.
Each time you create an index, you will decide whether the index should be "online" or "batch." The distinction between these two types of indexes has to do with when they are updated, and that factor, in turn, has to do with the way each index is used and the performance of your applications when records are being added, edited, or deleted.
An online index is updated whenever a change is made to any column (in any record) of which it is composed. Changes occur to an index when:
- New records are created
- Existing records are deleted
- One or more of the columns making up the index is changed in an existing record
In complex applications using dozens of related tables, each containing millions of records and each having many online indexes with many segments and long aggregate key lengths, changes to the database can impose waiting periods of perceptible length on users while records are saved or deleted.
This overhead can be reduced when certain indexes are used only at isolated and predictable times by changing them from online to batch. Batch indexes are updated only when the database is explicitly sorted through the sort command or the Database Builder.
A situation where a batch index might be appropriate would be where, once a month, a list of customers' names, balances, and phone numbers is output by the number of days by which payment is overdue. It might not be necessary to access customer records by that index on a daily basis, but for the report, the index is required once a month.
If the index of the number of days overdue were defined in the table as a batch index, the program that outputs the report each month could contain, at or near its top, a sort command addressing the table(s) whose batch indexes were to be updated. The program would continue after the command to output the report, probably also blocking changes to the database until the program was finished running.
If a batch index is used without needed updating, however, records will be output out of order, recent records will be omitted or unfindable, and attempts to find records deleted since the last update will generate errors. Suitable measures must be taken to prevent such occurrences where batch indexes are used.