Hướng dẫn học Microsoft SQL Server 2008 part 139

Kiểm tra việc lấy hiệu suất trong Bảng 64-1, nhiều chỉ số có một truy vấn tối ưu hoá chi phí 0,12 và sử dụng bốn lần đọc logic. Đối với các truy vấn không thường xuyên, đường dẫn Truy vấn 7, với nhiều chỉ số của nó, nhiều hơn là đủ, và tốt hơn nhiều so với chỉ số nào cả. Tuy nhiên, đối với những truy vấn số chạy liên tục, con đường truy vấn tiếp theo là một giải pháp tốt hơn cho nhiều tiêu chí | Part IX Performance Tuning and Optimization 1342 FIGURE 64-13 Filtering by two indexes adds a merge join into the mix. Examining the performance stat in Table 64-1 multiple indexes has a Query Optimizer cost of .12 and uses four logical reads. For infrequent queries Query Path 7 with its multiple indexes is more than adequate and much better than no index at all. However for those few queries that run constantly the next query path is a better solution for multiple criteria. Query Path 8 Filter by Ordered Composite Index For raw performance the fastest solution to the multiple WHERE clause criteria problem is a single composite index as demonstrated in Query Path 8. Creating a composite index with ProductID and StartDate as key columns sets up the test Indexing Strategies 64 DROP INDEX DROP INDEX CREATE INDEX IX_WorkOrder_ProductID ON ProductID StartDate Rerunning the same query SELECT WorkOrderID StartDate FROM WHERE ProductID 757 AND StartDate 2002-01-04 The query execution plan show in Figure 64-14 is a simple single-index seek operation and it performs wonderfully. FIGURE 64-14 Filtering two criteria using a composite index performs like greased lighting. 1343 Part IX Performance Tuning and Optimization Query Path 9 Filter by Unordered Composite Index One common indexing myth is that the order of the index key columns doesn t matter that is SQL Server can use an index so long as the column is anywhere in the index. Like most myths it s a half truth. Searching b-tree indexes requires the data for the leading columns in the order of the columns. Searching for coll col2 and so on works great but searching for the columns out of order . col2 without coll requires scanning all the leaf-level data. Query Path 9 demonstrates the inefficiency of filtering by an unordered composite index. In the .