http://www.4guysfromrolla.com/webtech/041206-1.shtml
Introduction
When displaying large amounts of data to a user, it’s important that the information be presented in digestible chunks rather than slamming it all down their throats at once. For example, searching for “Microsoft” on Google returns approximately 2,190,000,000 results, but thankfully Google only shows me ten of those at a time. When providing paging access to particularly large result sets – tables with tens of thousands, hundreds of thousands, or millions of records – care must be taken in querying the data such that only the particular page of data requested is returned.
Both the DataGrid in ASP.NET 1.x and GridView in ASP.NET 2.0 providing two paging flavors:
- Default paging – easy to implement but naively retrieves all records from the database and then trims the results to show only those for the requested page
- Custom paging – requires the developer to correctly retrieve only the precise subset of records to show for the current page; requires more effort to implement
While default paging is quick and easy to implement, for sufficiently-sized result sets custom paging is a must. In March 2006 I authored a couple of articles here on 4Guys on how to efficiently page through large result sets using custom paging in the GridView with the ObjectDataSource and SQL Server 2005’s new ROW_NUMBER() keyword. This new keyword simplifies efficiently retrieving a particular subset of data ordered by row number. After authoring these articles, I received many requests from readers for a look at how to implement this type of efficient custom paging using SQL Server 2000 (which lacks the ROW_NUMBER() keyword).
A previous article on 4Guys, Paging through Records using a Stored Procedure by Daniel Anderson, provides one approach that far outperforms the default paging implementation. However, it has a few areas that can be updated to improve the performance. This article looks at updating Daniel’s stored procedure to provide an even-more efficient approach. The stored procedure presented at the end of this article can be used for classic ASP applications, custom paging with the DataGrid in ASP.NET 1.x, or used by the ObjectDataSource to provide custom paging for the GridView in ASP.NET 2.0 applications. Read on to learn more!