Database Reference
In-Depth Information
Subsequently, the base table (or the clustered index) containing all the columns has to be accessed, as shown
next. The number of logical reads and the execution time have both increased.
Table 'SalesTerritory'. Scan count 0, logical reads 4
CPU time = 0 ms, elapsed time = 20 ms
As shown in Figure
18-2
, the fewer the columns in the select list, the better the query performance. And
remember, the query we've been looking at is a simple query returning a single, small row of data, and it has doubled
the number of reads and tripled the execution time. Selecting too many columns also increases data transfer across
the network, further degrading performance.
Figure 18-2.
Execution plan showing the added cost of referring to too many columns
Use Highly Selective WHERE Clauses
As explained in Chapter 8, the selectivity of a column referred to in the
WHERE
and
HAVING
clauses governs the use
of an index on the column. A request for a large number of rows from a table may not benefit from using an index,
either because it can't use an index at all or, in the case of a nonclustered index, because of the overhead cost of the
bookmark lookup. To ensure the use of indexes, the columns referred to in the
WHERE
clause should be highly selective.
Most of the time, an end user concentrates on a limited number of rows at a time. Therefore, you should design
database applications to request data incrementally as the user navigates through the data. For applications that rely
on a large amount of data for data analysis or reporting, consider using data analysis solutions such as Analysis Services
or PowerPivot. Remember, returning huge result sets is costly, and this data is unlikely to be used in its entirety.
Using Indexes Effectively
It is extremely important to have effective indexes on database tables to improve performance. However, it is equally
important to ensure that the queries are designed properly to use these indexes effectively. These are some of the
query design rules you should follow to improve the use of indexes:
•
Avoid nonsargable search conditions.
•
Avoid arithmetic operators on the
WHERE
clause column.
WHERE
clause column.
I cover each of these rules in detail in the following sections.
•
Avoid functions on the
