Database Reference
In-Depth Information
Operating on Small Result Sets
To improve the performance of a query, limit the amount of data it operates on, including both columns and rows.
Operating on a small result set reduces the amount of resources consumed by a query and increases the effectiveness
of indexes. Two of the rules you should follow to limit the data set's size are as follows:
•
Limit the number of columns in the select list.
WHERE
clauses to limit the rows returned.
•
Use highly selective
It's important to note that you will be asked to return tens of thousands of rows to an OLTP system. Just because
someone tells you those are the business requirements doesn't mean they are right. Human beings don't process tens
of thousands of rows. Few human beings are capable of processing thousands of rows. Be prepared to push back on
these requests, and be able to justify your reasons.
Limit the Number of Columns in select_list
Use a minimum set of columns in the select list of a
SELECT
statement. Don't use columns that are not required in the
output result set. For instance, don't use
SELECT *
to return all columns.
SELECT *
statements render covered indexes
ineffective, since it is usually impractical to include all columns in an index. For example, consider the following query:
SELECT Name,
TerritoryID
FROM Sales.SalesTerritory AS st
WHERE st.Name = 'Australia' ;
A covering index on the
Name
column (and through the clustered key,
ProductID
) serves the query quickly
through the index itself, without accessing the clustered index. When you have
STATISTICS 10
and
STATISTICS TIME
switched on, you get the following number of logical reads and execution time, as well as the corresponding execution
plan (shown in Figure
18-1
):
Table 'SalesTerritory'. Scan count 0, logical reads 2
CPU time = 0 ms, elapsed time = 6 ms.
Figure 18-1.
Execution plan showing the benefit of referring to a limited number of columns
If this query is modified to include all columns in the select list as follows, then the previous covering index
becomes ineffective because all the columns required by this query are not included in that index:
SELECT *
FROM Sales.SalesTerritory AS st
WHERE st.[Name] = 'Australia';
