Proactive Index Design - Relational Database Index Design and the Optimizers - page 65

scan—much too slow if there are say more than 100,000 rows in the table. Index
screening in itself does not imply that the index has any of the three stars.
Nevertheless, according to our experience, a significant proportion of index
problems found after cutover would have been detected early by considering BQ.
Applying BQ to single-table selects is straightforward. A join on the other
hand must be mentally broken down into several single-table cursors before the
BQ can be applied. This is a much more complex process, one that we shall
consider in great detail in Chapter 8.
QUICK UPPER-BOUND ESTIMATE (QUBE)
In the initial evaluation phase (will this SELECT be fast enough with the cur-
rent indexes?), the QUBE is more time consuming than BQ, but it reveals all
performance problems that relate to index or table design—assuming that the
worst-case filter factors used for each predicate are reasonably close to the
actual worst-case filter factors. By definition the QUBE is pessimistic (upper-
bound); it sometimes makes false alarms, unlike BQ which isn't able to detect
some problems.
The objective of the QUBE is to reveal potentially slow access paths at
a very early stage, as the program is being designed, written, or generated. In
order to be usable in real projects, any statement level prediction formula must
be so simple that the additional effort of the estimation process is at an accept-
able level. A few years ago, a Finnish company used a former version of the
QUBE to evaluate all the transactions and batch programs for a new application.
According to its records, the estimation process added only 5% to the implemen-
tation effort; however, numerous index improvements and indeed a few program
design changes were identified and implemented as a result. Post-cutover tuning
was reduced by 90% compared to similar projects designed without the QUBE.
The version of the QUBE discussed in this topic is also somewhat less time
consuming than that used in this company's evaluation.
The output of the quick estimate is the local response time (LRT), that is,
the elapsed time within the database server. The LRT of a traditional transaction
in a single-tier environment (where the program issuing the SQL calls resides
in the same machine as the database) is the response time for one interaction
between the user and the database server, excluding all network delays. In mul-
titier environments (client/server), the communication time between tiers is also
excluded. The LRT of a batch job means the elapsed time of the job execution.
Any queuing time in the job queue is also excluded.
Figure 5.1 shows the components of the LRT together with the subcompo-
nents of interest in the QUBE process.
Service Time
In simple cases (those for which I/O time and CPU time do not overlap), service
time is the sum of the CPU time and the random read time without drive queuing.
If there is no contention, the local response time will be equal to the service time.