![](https://csdnimg.cn/release/download_crawler_static/4256820/bg11.jpg)
A common table might list the customer’s address and account numbers;
however, several other tables must be joined to process and integrate all
of the necessary account transaction information.
v Statements that have a poor access path, such as one that is not
supported by an available index
v Statements that have a long execution time
v Statements that are executed only at application initialization time, but
that have disproportionately large resource requirements. For example,
statements in an application that generates a list of account work that
must be processed during the day. When the application starts, the first
major SQL statement causes a seven-way join, which creates a very large
list of all the accounts for which this application user is responsible. This
statement might only run a few times each day, but it takes several
minutes to run if it has not been tuned properly.
Benchmark test creation
You will need to consider a variety of factors when designing and implementing a
benchmark testing program.
Because the main purpose of the testing program is to simulate a user application,
the overall structure of the program will vary. You might use the entire application
as the benchmark and simply introduce a means for timing the SQL statements
that are to be analyzed. For large or complex applications, it might be more
practical to include only blocks that contain the important statements. To test the
performance of specific SQL statements, you can include only those statements in
the benchmark testing program, along with the necessary CONNECT, PREPARE,
OPEN, and other statements, as well as a timing mechanism.
Another factor to consider is the type of benchmark to use. One option is to run a
set of SQL statements repeatedly over a certain time interval. The number of
statements executed over this time interval is a measure of the throughput for the
application. Another option is to simply determine the time required to execute
individual SQL statements.
For all benchmark testing, you need a reliable and appropriate way to measure
elapsed time. To simulate an application in which individual SQL statements
execute in isolation, measuring the time to PREPARE, EXECUTE, or OPEN,
FETCH, or CLOSE for each statement might be best. For other applications,
measuring the transaction time from the first SQL statement to the COMMIT
statement might be more appropriate.
Although the elapsed time for each query is an important factor in performance
analysis, it might not necessarily reveal bottlenecks. For example, information on
CPU usage, locking, and buffer pool I/O might show that the application is I/O
bound and not using the CPU at full capacity. A benchmark testing program
should enable you to obtain this kind of data for a more detailed analysis, if
needed.
Not all applications send the entire set of rows retrieved from a query to some
output device. For example, the result set might be input for another application.
Formatting data for screen output usually has a high CPU cost and might not
reflect user needs. To provide an accurate simulation, a benchmark testing program
should reflect the specific row handling activities of the application. If rows are
sent to an output device, inefficient formatting could consume the majority of CPU
time and misrepresent the actual performance of the SQL statement itself.
Chapter 1. Performance tuning tools and methodology 7