Supporting Selections
If your application must support selections of ranges of records sharing a value, or a range of values, you can speed up certain selections considerably by providing indexes to support the selections. The selections in question are those in which the data to be output is all within the data needed for the index, or include relatively little data not used in the index.
Consider, for example, a report of customers by country with customer numbers after the customer names. Further assume that each time the report was run, it was only for the customers in one particular country. The required index would be based on, first, country, then customer name. If that index is used, the record for every qualifying customer must be retrieved into the record buffer to get the customer number. However, if the index contains country, customer name, and then customer number, the record data need not be loaded at all—the entire report can be output directly from the index data.
Although this kind of index greatly speeds up selections, the larger and more complex indexes that sometimes result exact their own costs. Specifically:
- Saves of new and edited records take longer if such an index is online (but not if it is batch).
- Individual finds from user input may also take longer if the extended index is used for such finds.
This disadvantage, if it is significant, can be avoided by using the extended index only for selection reports, and the shorter form of the index for individual finds. In this case, it is more important to set the extended index to batch mode, since it constitutes a whole additional index to be updated in every save if it is left online.