Design Issues and Performance

Good query performance requires planning.

Carefully plan the design of the following:

• Storage structures and indexes
• Keys
• Queries

For help in identifying performance issues, see the chapters "Ensuring Data Integrity," "Maintaining Databases," "Maintaining Storage Structures," and "Using the Query Optimizer."

Other important design issues are:

• Database design
• Validation checks and integrities
• Grants and views
• Application design

Hierarchy for Diagnosing Design-based Performance Problems

A thorough performance analysis must include each item in the following list. Areas are listed in the order of greatest gain. For example, if your database design is flawed, perfect server configuration cannot help you avoid query performance problems.

1. Database design
2. Storage structures and index design. See the chapter "Choosing Storage Structures and Secondary Indexes."
3. Key design. See the chapter "Choosing Storage Structures and Secondary Indexes."
4. Constraints. See the chapter "Managing Tables and Views."
5. Validation checks and integrities. See the chapters "Ensuring Access Security" and "Ensuring Data Integrity."
6. Grants and views. See the chapter "Ensuring Access Security."
7. Query design.
8. Application design.
9. Concurrency. See the chapter "Understanding the Locking System" and the System Administrator Guide.
10. DBA utilities and maintenance. See the chapter "Maintaining Databases."
11. Operating system resources and tuning. See the System Administrator Guide.
12. Server configuration. See the System Administrator Guide.

Storage Structures and Index Design and Performance

Choosing the correct table storage structure for your needs can improve concurrency and query performance. Remember that there is no substitute for testing and benchmarking your queries.

For tips on choosing storage structures and advantages and disadvantages of the various storage structures, see the chapter "Choosing Storage Structures and Secondary Indexes." For information on modifying and compressing storage structures and a discussion of overflow, see the chapter "Maintaining Storage Structures."

Key Design and Performance

Key design is a complex subject. For additional information on keys, see the chapter "Choosing Storage Structures and Secondary Indexes."

Characteristics of Good Keys

Good keys have the following features:

• Use columns referenced in the where clauses and joins of your queries
• Are unique

  Always document reasons for maintaining non-unique keys.

All keyed storage structures can enforce unique keys. They are:

• Short
• Static
• Non-nullable

Characteristics of Bad Keys

Bad keys have the following features:

• Wide
  • Use wide keys with caution.
  • You get fewer rows per page.
  • Evaluating the hash function takes more time with wide keys.
  • A wide key deepens the index level of B-tree and ISAM logarithmically, with respect to key width. B-tree is the least affected table structure.
  • Consider using a surrogate key as an alternative.
• Non-static

  Updating the index can slow performance.

• Non-uniform duplication

  A mix of high and low duplication can cause inconsistent query performance.

• Sequential
  • Sequential keys must be used with care.
  • ISAM tables can be lopsided and the overflow chains can cause concurrency problems.
  • Control sequential key problems with a frequent modify schedule.

Multi-Column Keys and Performance

Multi-column keys have special issues. If used improperly in your query, the key cannot be used and the search does a full-table scan.

Keep the following in mind:

• Use the most unique and frequently used columns for the left member of a multi-column key.
• Searches on B-tree and ISAM tables must use at least the leftmost part of a multi-column key in a query, or a full-table scan can result.
• Searches on hash tables must use an exact match for the entire key in the query, or a full-table scan can result.
• Optimizer statistics are approximated by adding the statistics of the columns making up a multi-column key.

Surrogate Keys and Performance

When you use a short surrogate or internal key to replace a bad key, or because there is no good key, consider the performance trade-offs. The set processing of data includes the overhead of deriving the key.

Surrogate key types include:

• Natural

  Universal (a social security number or zip code are examples)

• Environmental

  These are local to an organization, like an employee number.

• Design artificial. These are:
  • Local to an application
  • Hard to remember
  • Hard for users to understand
  • Can be hidden from users

Query Design and Performance

Query design is a complex subject. Following these tips will improve the performance of your queries:

• Conversion joins are joins where two columns of different data types are joined in a query, either explicitly or implicitly. These joins are frequently the result of database design problems and must be avoided.
• Avoid using function joins.
  • Functions in the where clause force a full-table scan.
  • Control uppercase and lowercase, and so on, at input time.
• Some complex OR queries can be rewritten as unions.
• Evaluate QEPs for critical queries:

  Can large table scans be avoided?

  • Is an additional index needed?
  • Are Cartesian products with large tables used?
  • Are function joins used?
• Use repeated queries for queries that are used many times.
• Do not forget to commit.

  Consider using set autocommit on.