Chapter 7 - Database Structure and Performance Tuning

Abstract

A database management system (DBMS) takes care of storing and retrieving data based on a user's commands without human intervention. The strategy used to process a data manipulation request is handled by the DBMS's query optimizer, a portion of the program that determines the most efficient sequence of relational algebra operations to perform a query. From a relational algebra point of view, a join can be implemented using two other operations—product and restrict. This sequence of operations requires the manipulation of a great deal of data and, if used by a DBMS, can result in very slow query performance. The restrict operation retrieves rows from a table by matching each row against logical criteria (a predicate). Those rows that meet the criteria are placed in the result table, and those that do not meet the criteria are omitted. One of the duties of a database administrator (DBA) is to optimize database performance (also known as performance tuning). This includes modifying the design—where possible—to avoid performance bottlenecks, especially involving queries.