Database Reference
In-Depth Information
Figure 19-8 shows the result.
Figure 19-8. Execution plan for the query when the local variable is not used
Although these two approaches look identical, on closer examination, interesting differences begin to appear.
Notice the estimated cost of some of the operations. For example, the Merge Join is different between Figure 19-6 and
Figure 19-7 ; it's 29 percent in the first and 25 percent in the second. If you look at STATISTICS IO and TIME for each
query, other differences appear. First, here's the information from the initial query:
Table 'PurchaseOrderDetail'. Scan count 1, logical reads 66
Table 'PurchaseOrderHeader'. Scan count 1, logical reads 44
CPU time = 16 ms, elapsed time = 151 ms.
Then here's the second query, without the local variable:
Table 'PurchaseOrderDetail'. Scan count 1, logical reads 66
Table 'PurchaseOrderHeader'. Scan count 1, logical reads 44
CPU time = 0 ms, elapsed time = 132 ms.
Notice that the scans and reads are the same, as might be expected of queries with near identical plans. The CPU
and elapsed times are different, with the second query (the one without the local variable) consistently being a little
less. Based on these facts, you may assume that the execution plan of the first query will be somewhat more costly
compared to the second query. But the reality is quite different, as shown in the execution plan cost comparison in
Figure 19-9 .
 
Search WWH ::




Custom Search