Let's use the CPU time of 16 milliseconds (ms) as a base time and try to improve on it. First, let's use the code that Listing 3 shows to build some indexes on the join columns. When you run the test query again, the statistics output is
Table 'od2'. Scan count 32, logical
reads 144, physical reads 0, read-ahead reads 0.
Table 'o2'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 8 ms, elapsed time = 8 ms.
This output shows that the CPU time decreased by half but the logical reads increased significantly. This result happened because for the original query (with no indexes), SQL Server used a hash-join plan, which results in few I/O operations but more internal-processing time.
Now let's see whether building indexes on the search arguments makes the query run even faster. Use the code that Listing 4 shows to drop the existing indexes and build the new indexes. Then, run the test query again. According to the following I/O output
Table 'od2'. Scan count 1, logical
reads 10, physical reads 0, read-ahead reads 0.
Table 'o2'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 15 ms.
the performance didn't improve. Neither search argument was selective enough to cause SQL Server to use an index, so the plan is the same as when no indexes existed.
Next, let's try adding composite indexes that include both the join column and the search argument column for each table. You could try building these indexes by specifying either the join column first or the search argument column first. Listing 5 shows the code to build composite indexes on the example tables. Listing 5 actually contains two sets of index drops and rebuilds; the first set builds composite indexes giving priority to the JOIN condition, and the second set gives priority to the search argument. I'll let you try them out by running the test query after building each new set of indexes.
If you ran these tests, you probably found no significant performance advantage to building the composite indexes. Neither of these indexes was more useful than the ones we had already created. Finally, let's try building composite indexes that cover the query. Remember that a covered query is supported by an index that contains all the columns the query references. Again, you could give priority to either the join column or the search argument column, as the code in Listing 6 shows.
Just like the indexes we built for Listing 5, the indexes built for Listing 6 don't seem to help at all. But changing the column order in the CREATE INDEX statement, as the code in Listing 7 shows, produces a winning solution: a covering index on each table, in which the search argument column is the leftmost column in the index. This combination of indexes gives outstanding performance, with a CPU time of substantially less than the original 16ms. In fact, my machine reported the CPU time as 0ms, which means less than one millisecond. I had no way to predict this result, but knowing what possible combinations of indexes were worth trying led me to an ideal solution.
Even though no simple algorithm can always come up with the best indexes for any given query or table, you can make some smart guesses and test whether those choices give good performance. For example, you can start with an index on your primary keys (which should already exist if you define your primary keys as a constraint on the table). Your decision is whether to make the index on the primary key column (or columns) clustered or nonclustered. That's a topic I won't discuss in detail now, but try experimenting to find the difference in performance. Sometimes a clustered index performs better, and sometimes a nonclustered index does.
Before you make the final decision about whether the primary key should have a clustered or nonclustered index, test all the possible types of queries that will access the table. You'll probably also want to consider building indexes on your foreign key columns and the columns used for search arguments and for grouping and sorting. When testing possible indexes, make sure to include all your data-modification operations. In my next column, I'll look at how indexes can be both a help and a hindrance for achieving good data-modification performance.
End of Article
Prev. page
1
[2]
next page -->
