Database Reference
In-Depth Information
Chapter 18
Query Design Analysis
A database schema may include a number of performance-enhancement features such as indexes, statistics, and
stored procedures. But none of these features guarantees good performance if your queries are written badly in the
first place. The SQL queries may not be able to use the available indexes effectively. The structure of the SQL queries
may add avoidable overhead to the query cost. Queries may be attempting to deal with data in a row-by-row fashion
(or to quote Jeff Moden, Row By Agonizing Row, which is abbreviated to RBAR and pronounced “reebar”) instead of
in logical sets. To improve the performance of a database application, it is important to understand the cost associated
with varying ways of writing a query.
In this chapter, I cover the following topics:
•
Aspects of query design that affect performance
•
How query designs use indexes effectively
•
The role of optimizer hints on query performance
•
The role of database constraints on query performance
Query Design Recommendations
When you need to run a query, you can often use many different approaches to get the same data. In many cases, the
optimizer generates the same plan, irrespective of the structure of the query. However, in some situations the query
structure won't allow the optimizer to select the best possible processing strategy. It is important that you are aware
that this can happen and, should it occur, what you can do to avoid it.
In general, keep the following recommendations in mind to ensure the best performance:
•
Operate on small result sets.
•
Use indexes effectively.
•
Avoid optimizer hints.
•
Use domain and referential integrity.
•
Avoid resource-intensive queries.
•
Reduce the number of network round-trips.
•
Reduce the transaction cost. (I'll cover the last three in the next chapter.)
Careful testing is essential to identify the query form that provides the best performance in a specific database
environment. You should be conversant with writing and comparing different SQL query forms so you can evaluate
the query form that provides the best performance in a given environment. You'll also want to be able to automate
your testing.
