Database Reference
In-Depth Information
Figure 19-9.
Relative cost of the query with and without the use of a local variable
From the relative cost of the two execution plans, it appears that the second query isn't cheaper than the first
query. However, from the
STATISTICS
comparison, it appears that the second query should be cheaper than the first
query. Which one should you believe: the comparison of
STATISTICS
or the relative cost of the execution plan? What's
the source of this anomaly?
The execution plan is generated based on the optimizer's estimation of the number of rows affected for each
execution step. If you take a look at the properties for the various operators in the initial execution plan for the query
with the local variable (as shown in Figure
19-7
), you may notice a disparity. Take a look at this in Figure
19-10
.
