User guide

Improving Performance
Using ENFORM Efficiently
5–8 058058 Tandem Computers Incorporated
Note that the addition or removal of alternate keys affects other transactions that
access the physical file. For example, PATHWAY often uses alternate keys to access a
tiny subset of the data base. Thus, removal of an alternate key might improve the
performance of ENFORM but have a disastrous effect on a PATHWAY application.
Consider the cost of record insertions as a consequence of adding alternate keys. The
cost of record insertions is an important consideration when determining whether to
add an alternate key since it could significantly slow application response time and
transaction rate.
Adding Alternate Keys. To observe the effect of adding an alternate key that is used to
select a small subset of records, consider $mkt.sample.employee, the physical file that
stores the records for employee (described in Appendix C). Assume that no alternate
key exists for employee and that $mkt.sample.employee:
Stores one hundred records.
Has ten data blocks each containing ten records.
Has one index block.
When the following query is issued:
OPEN employee;
LIST empnum, empname, salary,
WHERE empname = "TOM HALL";
the query processor must access the index block and all ten data blocks of
$mkt.sample.employee to retrieve the required record, resulting in a total of eleven
physical file accesses.
Consider the effect of making empname an alternate key for employee, such that the
alternate key file:
Stores one hundred records (one for each data record).
Has ten data blocks each containing ten records.
Has one index block.
If the preceding query is now issued, the query processor must access the alternate key
file index block, one alternate key file data block, one $mkt.sample.employee index block,
and one $mkt.sample.employee data block, resulting in a total of four physical file
accesses.
The reduction in physical file accesses is over 60%. The principles described in this
example apply to files of any size. For very large files, the resulting reduction in
physical file accesses is very significant.