Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 301

Table 9-25: Selective Correlated Subquery Performance Comparison

Method % Cost CPU Reads Duration

Join and DISTINCT 34 50 1241 71ms

Derived Table 33 40 1241 40ms

Correlated Subquery 33 40 1241 39ms

Remember that this example uses a very selective WHERE predicate. Decrease the selectivity removing
the WHERE predicate. This run without the WHERE predicate will require more sort operations then the
previous runs and can demonstrate the scaling capabilities of each method. Table 9-26 shows how the
results change.

Table 9-26: Non Selective Correlated Subquery Comparison

Method % Cost CPU Reads Duration

Join and DISTINCT 34 270 1941 1690ms

Derived Table 33 91 1941 2073ms

Correlated Subquery 33 150 1941 2056ms

You'll notice a more drastic and maybe even surprising change in the total duration, even though the
relative execution plan costs have remained essentially the same percentages. The slowest method is now
the fastest and the two fastest are now the slowest.

If you look at the query execution plans, you'll notice that the query plan in Figure 9-20 for the Join
method has changed to use a Merge Join Operator instead of a Nested Loop Operator.

Figure 9-20