ENFORM User's Guide

Improving Performance
Using ENFORM Efficiently
058058 Tandem Computers Incorporated 5–7
Figure 5-4 shows the same key-sequenced file after the block size is increased to
4096 bytes.
Figure 5-4. Key-Sequenced File with Increased Block Size
Alternate Key File
Level 1 Index Block
Alternate Key File
Data Blocks
Data File
Level 1 Index Block
Data File
Level 2 Index Blocks
Data File
Data Blocks
Increasing the block size eliminates one index block for both the alternate key file and
the data file. After the data is reloaded, the query processor must access only five
blocks to retrieve the required record: two blocks in the alternate key file and three
blocks in the data file. If both level 1 index blocks are in cache, the query processor
must access only three blocks: an alternate key data block, a level 2 data file index
block, and a data file data block.
For the file in Figure 5-4 increasing the block size has decreased the number of
physical file accesses from 7 to 5. This reduction in physical file accesses generates a
significant improvement in query performance.
Note that the effect of increasing the block size is not apparent in the ENFORM
statistics. ENFORM reports one logical data access in its statistics no matter how
many physical accesses occur.
Add or Remove Alternate Keys
Adding or removing alternate keys improves performance if a reduction in the
number of physical file accesses results. The conditions under which adding or
removing alternate keys affect performance are:
When the alternate key field is used to select a small subset of records, adding an
alternate key reduces the number of physical file accesses.
When the alternate key field is used to select a large subset of records, removing
the alternate key reduces the number of physical file accesses. It is more efficient
for the query processor to sort the file or simply access it sequentially when the
number of records retrieved is large.