Refining the Database Design › Estimating I/Os for Transactions

Estimating I/Os for Transactions

After you have assigned data location and access modes to the entities in a database, you need to estimate the number of input/output operations that each business transaction will perform. You estimate the I/O count for a transaction by tracing the flow of processing from one entity to another in the database. As you trace the flow of processing, you determine the number of I/Os required to access all necessary entities.

The I/O estimate for a business transaction depends on several factors, including:

• The order in which entities are accessed
• The location mode of each entity accessed
• The types of indexes (if any) used to access the data
• How the entities are clustered in the database

General guidelines

Assuming that an entire cluster of database entities can fit on a single database page, you can use the following general guidelines for estimating I/Os:

• Zero I/Os are required to access an entity that is clustered around a previously accessed entity.
• One I/O is required to access an entity stored CALC.
• Three I/Os are required to access an entity through an index.

To calculate the time required to perform all I/O operations in a particular transaction, perform the following computations:

• Total number of I/Os for all entity types—Compute the total number of I/O operations by adding the number of I/Os required to retrieve and update occurrences of all entity types.
• I/O reserve factor—Multiply the total number of I/Os by 1.5 to account for possible overflow conditions and large index structures.
• Amount of time to perform I/Os—Multiply the total number of I/Os for all entity types by the access time for the device being used. The result is a rough estimate of the time required to perform all I/O operations in the transaction.

Once you have determined how much time will be required to execute a particular transaction, you need to compare this time figure with the performance goal you established earlier in the design process. If the required time does not meet your expectations, you need to modify the physical database model until it does. Sometimes you have to change your expectations.

For further information on establishing performance goals for business transactions, see Chapter 10, "Identifying Application Performance Requirements".

Two sample exercises in estimating I/Os are presented below. Each exercise uses the EMPLOYEE, EXPERTISE, and SKILL entities: