Introduction
It is amazing the amount of cycles, hardware and brain ware, go into paging results efficiently. Recently Scott Mitchell authored an article titled Efficiently Paging Through Large Result Sets in SQL Server 2000 that looked at a stored procedure that returned a particular "page" of data from a table. After examining Scott's approach, I saw some potential improvements in his method. (If you haven't yet perused Scott's technique, take a moment to do so before continuing here, as this article builds upon Scott's example.)
Scott's approach made use of a table variable to generate a synthetic ID to act as a row counter. Every time a page is requested, all of the data in the table being paged must be read and inserted into the table variable in order to generate the synthetic ID, at which point a SELECT
ROWCOUNT
to greatly reduce the number of records that must be read and inserted into the table variable. statement returns just those records whose IDs fall within the desired range. While Scott's method is faster than blindly returning all of the records, his approach can be greatly improved by using
In this article we'll look at two ways to improve Scott's method. The first approach uses a table variable (just like Scott's), but utilizes the SET ROWCOUNT
command to reduce the number of records read and inserted into the table variable. The second technique more cleverly uses SET ROWCOUNT
to provide an even more efficient approach than the first. Read on to learn more!
Using ROWCOUNT
to Optimize Paging
The first step we can take to optimize paging is to use SET ROWCOUNT
prior to filling our table variable. SET
options alter the current sessions handling of specific behavior; SET ROWCOUNT
tells SQL Server to stop processing query results after it has processed the specified number of rows. For more background on SET ROWCOUNT
, refer to Retrieving the First N Records from a SQL Query.
This particular stored procedure example was created by 4Guys reader Dave Griffiths.
CREATE PROCEDURE usp_PagedResults_New_Rowcount |
As you can see, this stored procedure uses SET ROWCOUNT
prior to filling the table variable with what is already known will be the last row needed to satisfy the current page. SQL Server stops filling the table once it processes this number of rows, minimizing the amount of data pulled into the table variable. This method rocks for the first several pages and only begins to hit SQL Server resources as we get incrementally deeper into our pages. Last but very important, SET ROWCOUNT 0
. This turns off row limitation and puts the current session in the default behavior mode in case the caller is doing anything else interesting with the same connection that may require more rows returned.
Taking Advantage of the SQL Server Optimizer
An optimizer trick that can also be used in this scenario is when a single variable is set to a potential list of values, it will get assigned the value of the last item in the list. For example, the following SQL script simply creates and fills a table variable with 100 records (1 through 100), then selects the value of the val
column into a local variable from the entire table using two different sorts:
DECLARE @tmp TABLE( |
The results from these selects follow:
Value Sort |
While one might think that SQL Server will need to read every record from the @tmp
table and assign the val
column to @res
for each such record, looking at the query plan for the statement it becomes obvious that the SQL Server optimizer knows that it will only ever need a single row to complete the query and is able to just read in that particular record. Examining this operation in SQL Profiler you'll find that the optimizer is able to get to the end result in only six reads (as opposed to the 100+ reads that would be necessary if it was reading every single record in the table). In short, when presented with such a query SQL Server doesn't actually get all of the records from the table, one at a time, and assign them to the local variable. Rather, it searches just for the last record in the query and assigns that result to the variable.
So, how does this little trick help in the problem of paging large result sets? If this knowledge is combined with SET ROWCOUNT
, large result sets can be efficiently paged without the need for temporary tables or table variables! Here is another, more efficient version of Scott and David's stored procedures:
CREATE PROCEDURE [dbo].[usp_PageResults_NAI] |
Using optimizer knowledge and SET ROWCOUNT
, the first EmployeeID
in the page that is requested is stored in a local variable for a starting point. Next, SET ROWCOUNT
to the maximum number of records that is requested in @maximumRows
. This allows paging the result set in a much more efficient manner. Using this method also takes advantage of pre-existing indexes on the table as it goes directly to the base table and not to a locally created table.
Using an even more highly unscientific comparison method than Scott's, let's see the results:
| Rows in Table | Page # | CPU | Reads | Duration |
---|---|---|---|---|---|
Scott's Approach (Table Variable w/o SET ROWCOUNT ) | 50,000 | 1 | 407 | 51,469 | 438 |
50,000 | 10 | 375 | 51,469 | 422 | |
50,000 | 100 | 406 | 51,469 | 485 | |
50,000 | 1,000 | 359 | 51,469 | 422 | |
50,000 | 10,000 | 422 | 51,469 | 468 | |
100,000 | 50,000 | 734 | 102,829 | 797 | |
150,000 | 100,000 | 1,109 | 154,170 | 1,219 | |
David's Approach Table Variable with SET ROWCOUNT ) | 50,000 | 1 | 0 | 80 | 0 |
50,000 | 10 | 0 | 90 | 0 | |
50,000 | 100 | 0 | 182 | 15 | |
50,000 | 1,000 | 0 | 1,133 | 16 | |
50,000 | 10,000 | 47 | 10,422 | 110 | |
100,000 | 50,000 | 313 | 51,501 | 391 | |
150,000 | 100,000 | 688 | 102,867 | 750 | |
Greg's Approach | 50,000 | 1 | 0 | 24 | 0 |
50,000 | 10 | 0 | 24 | 0 | |
50,000 | 100 | 0 | 26 | 0 | |
50,000 | 1,000 | 0 | 24 | 0 | |
50,000 | 10,000 | 16 | 125 | 15 | |
100,000 | 50,000 | 31 | 525 | 63 | |
150,000 | 100,000 | 31 | 1,023 | 78 |
Impressed? If your application is like 99.99% of application using paging and you know no one will ever make it to the 1000th page, you might not think using knowledge of the optimizer is not very important, but if you talk to just about anyone who has a highly loaded database, they will tell you space and blocking in tempdb
is a problem. (Anytime you write to a temporary table or table variable, you're working with tempdb
.) Anything that can be done to minimize the use of tempdb
should be used – it will speed up your application. Also, the reduction in IO through a 30% reduction in reads over using SET ROWCOUNT
alone for the first page alone is significant.
Conclusion
As you can see, paging can be greatly improved with the use of SET ROWCOUNT
and perhaps a little knowledge of the optimizer. SET ROWCOUNT
can be used with a table variable and allow different sorting parameters easily. Additionally, we could still allow ordering of the pages with different sorting options through the use of dynamic SQL (let us know if you'd like to see how to do these things in another article) and still use the optimizer to our advantage, but this can get very complex in the case of ties.
Happy Programming!
Attachments:
Read the original article here..
http://www.4guysfromrolla.com/webtech/042606-1.shtml
Thank you 4GuysFromRolla... You guys rock...
Please visit my other blogs too: http://edwardanil.blogspot.com for information and http://netsell.blogspot.com for internet marketing. Thanks !!
No comments:
Post a Comment