Datasheet

588
✦ 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 capabil-
ity 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. For best results, a
thorough knowledge of the capabilities and the deficiencies of your DBMS is
essential, as is a good grasp of optimization principles.
Two possible culprits in performance problems are nested queries and cor-
related queries. Many optimizers don’t handle these queries well. If a nested
or correlated query isn’t performing up to expectations, recoding it without
nesting or correlation is a good thing to try.
Tuning Transactions
In an environment in which many users are using a database concurrently,
contention for a popular resource can slow performance for everyone. The
problem arises because a user locks a resource before using it and releases
the lock when she is finished with it. As long as the resource is locked, no
one else can access it.
Here are several things you can do to minimize the performance impact of
locking:
✦ Minimize the amount of time that you hold a lock. If you’re perform-
ing a series of operations with a transaction, obtain your locks as late as
possible and release them as soon as possible.
✦ Put indexes on a different disk from the one that holds the data
files. This practice prevents accesses to indexes from interfering with
accesses to data.
✦ Switch to a hash index. If a table is updated frequently, B+ tree indexes
on its columns lose much of their advantage, because the root of the
tree and the pages just below it must be traversed by every update.
They become hot spots, meaning that they’re locked frequently, becom-
ing bottlenecks. Making the switch to a hash index may help.
Tuning Transactions
40_9780470929964-bk07ch01.indd 58840_9780470929964-bk07ch01.indd 588 2/24/11 3:45 PM2/24/11 3:45 PM