Database Reference
In-Depth Information
As you can see, in the first case (using
UNION
), the optimizer filtered the records completely differently in order
to eliminate the duplicates while concatenating the result set of the two
SELECT
statements. Since the result sets are
exclusive to each other, you can use
UNION ALL
instead of the
UNION
clause. Using the
UNION ALL
clause avoids the
overhead of detecting duplicates and thereby improves performance.
Use Indexes for Aggregate and Sort Conditions
Generally, aggregate functions such as
MIN
and
MAX
benefit from indexes on the corresponding column. Without
any index on the column, the optimizer has to scan the base table (or the clustered index), retrieve all the rows, and
perform a stream aggregate on the group (containing all rows) to identify the
MIN/MAX
value, as shown in the following
example (see Figure
19-5
):
SELECT MIN(sod.UnitPrice)
FROM Sales.SalesOrderDetail AS sod;
Figure 19-5.
A scan of the entire table filtered to a single row
The
STATISTICS IO
and
TIME
output of the
SELECT
statement using the
MIN
aggregate function is as follows:
Table 'SalesOrderDetail'. Scan count
1,
logical reads 1246 CPU time = 46 ms,
elapsed time = 52 ms.
As shown in the
STATISTICS
output, the query performed more than 1,200 logical reads just to retrieve the row
containing the minimum value for the
UnitPrice
column. You can see this represented in the execution plan in
Figure
19-5
. A huge fat row comes out of the
Clustered Index Scan
only to be filtered to a single row by the
Stream
Aggregate
operation. If you create an index on the
UnitPrice
column, then the
UnitPrice
values will be presorted by
the index in the leaf pages.
CREATE INDEX TestIndex ON Sales.SalesOrderDetail (UnitPrice ASC);
The index on the
UnitPrice
column improves the performance of the
MIN
aggregate function significantly. The
optimizer can retrieve the minimum
UnitPrice
value by seeking to the topmost row in the index. This reduces the
number of logical reads for the query, as shown in the corresponding
STATISTICS
output (see Figure
19-6
).
Table 'SalesOrderDetail'. Scan count
1,
logical reads 3
CPU time = 0 ms, elapsed time = 20 ms.
Figure 19-6.
An index radically improves performance
