tailieunhanh - Hướng dẫn học Microsoft SQL Server 2008 part 138

Sự khôn ngoan thông thường cho rằng đây là con đường nhanh nhất có thể truy vấn, và nó là linh hoạt khi trở về một hàng đơn, tuy nhiên, từ góc độ hàng, trả lại cho mỗi phần nghìn giây, nó là một trong các đường dẫn truy vấn chậm nhất. Một chuyện hoang đường phổ biến là tìm kiếm chỉ có thể trở về đơn hàng và đó là lý do tại sao tìm kiếm nhiều hàng sẽ rất chậm so với quét. Khi hai đường dẫn tới truy vấn cho biết, đó là không đúng sự thật | Part IX Performance Tuning and Optimization Conventional wisdom holds that this is the fastest possible query path and it is snappy when returning a single row however from a rows-returned-per-millisecond perspective it s one of the slowest query paths. A common myth is that seeks can only return single rows and that s why seeking multiple rows would be very slow compared to scans. As the next two query paths indicate that s not true. Query Path 3 Range Seek Query The third query path selects a narrow range of consecutive values using a between operator in the WHERE clause SELECT FROM WHERE WorkOrderID between 10000 and 10010 The Query Optimizer must first determine whether there s a suitable index to select the range. In this case it s the same key column in the clustered index as in Query path 2. A range seek query has an interesting query execution plan. The seek predicate listed in the index seek properties which defines how the query is navigating the b-tree has both a start and an end as shown in Figure 64-6. This means the operation is seeking the first row and then quickly scanning and returning every row to the end of the range as illustrated in Figure 64-7. To further investigate the range seek query path this next query pushes the range to the limit by selecting every row in the table. Both queries are tested just to prove that between is logically the same as with SELECT FROM WHERE WorkOrderID 1 and WorkOrderID 72591 SELECT FROM WHERE WorkOrderID between 1 and 72591 At first blush it would seem that this query should generate the same query execution plan as the first query path select from table but just like the narrow range query the between operator needs a consecutive range of rows which causes the Query Optimizer to select index seek to return ordered rows. Keep in mind that there s no guarantee that another row might be added after the query plan is generated and before it s executed.