IBM i General Design Standards › Design Standards for Database Files › Considerations for Database File Design › Categories of Database File › Access Path Performance Considerations

Access Path Performance Considerations

An excessive number of access paths can have a serious impact on performance. Note the following points:

• Access paths that are required only for occasional batch jobs (for example, for reports), should be specified with delayed access path rebuild. A value of MAINT(*REBLD) on the OS/400 ‘Create file’ commands (CRTPF or CRTLF).
• Keep the number of immediate maintenance access paths on a given physical file within reason, for example, minimize the number of operational indices.
• If a large number (IBM cites a value of more than 10 percent) of databases add are taking place in a batch procedure, it is quicker to remove all non-essential access paths and add them back afterwards.
• An access path determines:
  • The order that records are presented
  • The criteria that are to be applied to select or omit records
  • Which fields from the records are to be included. A particular use of this is to restrict access to particular fields on a database file

For a given file, the number of useful ways of selecting or omitting the data is usually far greater than the number of useful ways of ordering the data. For this reason, it is often a good idea to leave the selection to the programs that read the file, or to use a ‘dynamic’ access path—rather than building it into the access path permanently (‘static’ selection). This is particularly true when the ‘cardinality’ of each key set (for example, number of records with the same key, or partial key, that have to be read), is small.

• OS/400 will automatically share the access paths of files which have the same keys. If you are specifying select or omit criteria using the database facilities, consider using ‘dynamic’ rather than ‘static’ selection, so as to allow sharing of access paths.