When Designing a Database for Performance, It Should Include the Total Performance and Not Just Local Performance

Performance should be viewed holistically. The performance of your data tier should be considered in conjunction with the capabilities of other tiers, such as network and application. Sometimes, performance of the data tier can be increased by employing caching strategies at other tiers, use of compression techniques at the network level, and so on.

However, when tuning the data tier for performance, consider the following whenever possible:

• Limit the number of indexes in a database.

• Limit ad hoc data access.

• Reduce the number of rows in a result set.

• Limit the number of joins.

• Avoid sorting.

For relational databases, an index is updated each time a record is updated. Logically, if a record has one corresponding index, the update will occur faster than if the record had ten corresponding indexes. By limiting ad hoc data access to a database, one can make data access patterns more deterministic and predictable. Ad hoc access can have an impact on the performance of a database.

By reducing the number of rows in a result set and/or at least potentially limiting the amount returning to the calling tier, performance can also be increased. In an OLTP system, users typically work with a single row displayed on the screen or with a couple of rows displayed within a grid or list box. Returning only the rows and columns that are needed at that instant will reduce the amount of network traffic, transformation, and other forms of processing associated with the request.

Limiting the number of joins in a result set also will have a profound effect on the performance of the data tier. By reducing the number of tables, the optimizer algorithms that many relational databases use can look at a smaller set of indexes and create a better data access plan. Sorts should be avoided on large amounts of data whenever possible. Additionally, sorting should occur only on indexed fields.

For additional information on mapping, refer to www.agiledata.org/essays/mappingObjects.html.