Datasheet

ManualsBrandsFor Dummies ManualsSoftware978-0-470-92996-4

Chapter 1: Tuning the Database

In This Chapter

✓ Examining the work to be done

✓ Contemplating physical design considerations

✓ Choosing and clustering indexes

✓ Co-clustering two relations

✓ Indexing on multiple attributes

✓ Tuning indexes, queries, and transactions

✓ Query tuning in a high-concurrency environment

✓ Benchmarking

✓ Keeping user interactions separate from transactions

✓ Holding down traffic between application and server

✓ Creating a precompiled list of frequently used queries

he word tuning is generally taken to mean optimizing an existing system

that isn’t operating at top capacity. Tuning doesn’t do you much good,

however, if your initial design isn’t at least close to optimal in the first place.

Tuning can take you only so far from your starting point. It’s a lot easier to

tune a slightly off-pitch B string on your guitar to a perfect B than it is to

tune a G string up to a perfect B. (Also, you’re a lot less likely to break the

string.) Tuning for optimal performance should start in the initial design

stage of a database, not at some later time when design decisions have been

cast in concrete.

The performance of a database management system (DBMS) is generally

judged by how fast it executes queries. Two types of operations are impor-

tant: the retrieval of data from a database and the updating of records in a

database. The speed at which records can be accessed is key to both types

of operations, because you must locate a record before you can retrieve or

update the data in it. The users’ data model on which you’ll base your data-

base design is almost certainly structured in a way that isn’t the best from a

performance standpoint. The users are primarily concerned with function-

ality and may have little or no idea of how the design of a database affects

how well it performs. You must transform the users’ data into a conceptual

schema that you actualize in the form of an Entity-Relationship (ER) model

diagram. Recall that the Entity-Relationship data model and its associated

diagrams are extensively covered in Book II.

40_9780470929964-bk07ch01.indd 57940_9780470929964-bk07ch01.indd 579 2/24/11 3:45 PM2/24/11 3:45 PM

COPYRIGHTED MATERIAL

Summary of content (12 pages)

PAGE 1
Chapter 1: Tuning the Database In This Chapter ✓ Examining the work to be done AL ✓ Contemplating physical design considerations ✓ Co-clustering two relations TE ✓ Indexing on multiple attributes RI ✓ Choosing and clustering indexes ✓ Tuning indexes, queries, and transactions MA ✓ Query tuning in a high-concurrency environment ✓ Benchmarking ✓ Keeping user interactions separate from transactions D ✓ Holding down traffic between application and server TE ✓ Creating a precompiled list of frequen
PAGE 2
580 Analyzing the Workload Analyzing the Workload Optimal design of a database depends largely on how the database will be used. What kinds of queries will it be subjected to? How often will updates be made, compared with how often queries are posed? These kinds of questions try to get at what the workload will be. The answers to such questions have great bearing on how the database should be structured. In effect, the design of the database is tuned based on how it will typically be used.
PAGE 3
Considering the Physical Design 581 through the table until you come upon the record you want. This feature can be a tremendous time-saver for a query. On the minus side, every time an insertion update or a deletion update is made to a table, the indexes on that table must be updated too, costing time. When chosen properly, indexes can be a great help. When chosen poorly, indexes can waste resources and slow processing substantially.
PAGE 4
582 Choosing the Right Indexes Choosing the Right Indexes Indexes can improve the performance of database retrievals dramatically, for several reasons. One reason is that an index tends to be small compared with the table that it’s indexing. This fact means that the index is likely to be in the cache, which is accessible at semiconductor-memory speed rather than on disk — a million-to-one performance advantage right there.
PAGE 5
Choosing the Right Indexes 583 on EMPLOYEE.DepartmentID usually performs best. The number of rows in the EMPLOYEE table is sure to be larger than the number of rows in the DEPARTMENT table, so the index is of more use applied to EMPLOYEE than it is applied to DEPARTMENT. A hash index stores pairs of keys and values based on a pseudo-randomizing function called a hash function. If the WHERE clause contains a range selection, such as EMPLOYEE.Age BETWEEN 55 AND 65, a B+ tree index on EMPLOYEE.
PAGE 6
584 Choosing the Right Indexes Consider the following example: SELECT DeptNo FROM EMPLOYEE WHERE EMPLOYEE.Age > 29 ; You can use a B+ tree index on Age to retrieve only the rows in which employee age is greater than 29. Whether this method is worthwhile depends on the age distribution of the employees. If most employees are 30 or older, the indexed retrieval won’t do much better than a sequential scan. Suppose that only 10 percent of the employees are more than 29 years old.
PAGE 7
Choosing the Right Indexes 585 Figure 1-1 reveals a few things about the cost of indexes: ✦ A clustered index always performs better than an unclustered index. ✦ A clustered index performs better than a sequential scan unless practically all the rows are retrieved. ✦ When one record is being retrieved, or a very few records are being retrieved, a clustered index performs much better than a sequential scan.
PAGE 8
586 Tuning Indexes If you suspect that an index might be doing you more harm than good, run some test queries with the index both present and absent. Use the results to guide your decision. Using composite indexes Composite indexes are indexes on more than one column. They can give superior performance to queries that have more than one condition in the WHERE clause.
PAGE 9
Tuning Queries 587 For best results, tuning indexes must be an ongoing activity. As time goes on, the nature of the workload is bound to evolve. As it does, the best indexes to support the current workload need to evolve, too. The database administrator must keep track of performance and respond when it starts to trend downward. Another problem, which appears after a database has been in operation for an extended period of time, might be called the tired index.
PAGE 10
588 Tuning Transactions ✦ An OR connective in the WHERE clause could cause a problem. ✦ If you expect the optimizer to select a fast but sophisticated, plan, you could be disappointed. Sometimes, the best plan is beyond the capability of even high-end optimizers to find. Some DBMSes give you some help in overcoming optimizer deficiencies. They enable you to force the optimizer to use an index that you know will be helpful or to join tables in the order that you know is best.
PAGE 11
Precompiling Frequently Used Queries 589 Separating User Interactions from Transactions Because computer instructions operate in the nanosecond realm and humans operate in the second or even minute realm, one thing that can really slow a database transaction is any interaction with a human. If that transaction happens to hold a lock on a critical resource, the application with which the user is interacting isn’t the only one to suffer a delay.
PAGE 12
590 Book VII: Database Tuning Overview