page111
93
Figure 3-17.
Optimal result set pattern
Needless to say, most query profiles do not conform to the pattern shown in
Figure 3-17, but it is helpful to understand what “good” looks like and then iterate toward
the optimal query profile as you tune your queries.
You might also experience join order issues when missing attributes from natural key
joins or missing primary key/foreign key relationships. Both of these scenarios result in
join explosions.
Detection of join order issues is largely through examination of individual queries
and visual identification and then testing data volumes for each referenced table. You
may want to consider using a clone of your production environment to determine
representative results.
Poor Join Order Costs
Poor join criteria and ordering can result in the following:
•
Inefficient joins where the build side hash tables are larger than
optimal resulting in higher probe execution times.
ChapTer 3
The Query profiler