Here's a query-tuning trick you can use to reduce the initial wait time for queries that sort data on a nonclustered-index key. Generally, you shouldn't use index hints in production applications if you can avoid them. In fact, in most cases, the need to use a hint might be considered a bug in the SQL Server query optimizer. However, for the case of sorting data on a nonclustered index key, you can safely use an index hint. Using the hint doesn't mean that the optimizer isn't working right, and you don't have to be concerned that the hint might no longer be necessary or beneficial after a service pack "fixes" the optimizer. One form of index hint is called FASTFIRSTROW; you can use it as a table hint in your FROM clause. This hint tells the optimizer to use a plan involving a nonclustered index on the sort column. To use the nonclustered index, SQL Server traverses the index's leaf level, retrieving each index key in sorted order; then it follows the bookmark for every index key to find the corresponding data row. (If no such index exists, SQL Server ignores the hint.) In the Order Details table, this process entails 2155 bookmark-lookup operations. The first few rows return very quickly, so SQL Server can begin to fill in your user's drop-down list. However, the time to complete the sort is much longer than if you didn't use the hint.
You can use the SET STATISTICS TIME ON option to see the difference between using and not using FASTFIRSTROW. The code in Listing 1 turns on this SET option, then executes Query 6 both with and without the FASTFIRSTROW hint. On my Toshiba Tecra 8000 laptop computer (with 256MB of RAM, running SQL Server 2000 Enterprise Edition on Windows 2000 Server), the first query (without the hint) used 30 milliseconds (ms) of CPU time. With the hint, the second query used 70ms of CPU time—more than twice as long as the first query. This dataset is so small that I didn't notice the delay in returning the first row when I didn't use the hint. However, when I've run similar queries on tables with just 20,000 rows, the delay before any data is returned was noticeable when I didn't use the FASTFIRSTROW hint; I got no delay with the hint.
When you have a nonclustered index on the sort column, you can choose whether to use the hint. You decide what's more important to you: Do you want the sort to take the least amount of total processing time, or do you want the sorted data to start coming back to the client as quickly as possible? In other words, do you want to minimize initial response time, or maximize throughput?
I mentioned that using the FASTFIRSTROW hint was just one form of the hint to control SQL Server's use of a nonclustered index on a sort column. Another form of this hint, using the OPTION clause with the FAST N hint, lets you specify how many rows (n) SQL Server should return as quickly as possible. For example, using the hint OPTION (FAST 10) tells SQL Server to return the first 10 rows as quickly as possible, then access the rest of the rows in a way that maximizes throughput.
Both clustered and nonclustered indexes can be useful for sorting more efficiently. If sorting is an important part of your applications' data-management operations, you need to consider those operations carefully when deciding the optimal indexes to build on your tables. Next time, I look at indexing for other organizational operations, such as TOP, GROUP BY, and DISTINCT.
End of Article
Prev. page
1
2
[3]
next page -->
