Custom Paging in ASP.NET 2.0 with SQL Server 2005

From: http://aspnet.4guysfromrolla.com/articles/031506-1.aspx

 

Introduction
A common pattern in web development is providing paged access to data. Rather than displaying the entire contents of a report or database table to an end user, developers often show only a subset of records per web page, with controls for moving from page to page. With ASP.NET 1.x, the DataGrid made paging incredibly simple - just set the AllowPaging property to True and add a few lines of code in the PageIndexChanged event handler and you were done! ASP.NET 2.0's GridView makes the process even simpler - just check the Enable Paging option from the GridView's smart tag - no code needed.

Of course nothing is free in life, and the tradeoff you make with the ease of checking a checkbox to enable paging (or, in the DataGrid's case, writing a couple lines of code) is performance. Out of the box, the DataGrid and GridView use default paging, which is a simple paging model that returns all of the records for each every page of data shown. When paging through small amounts of data (dozens to a hundred or so records), this inefficiency is likely outweighed by the ease of adding the feature. However, if you want to page through thousands, tens of thousands, or hundreds of thousands of records the default paging model is not viable.

The alternative to default paging is custom paging, in which you are tasked with writing code that intelligently grabs the correct subset of data. It requires a bit more work, but is essential when dealing with sufficiently-sized data. I discuss how to implement custom paging in ASP.NET 1.x in my book ASP.NET Data Web Controls Kick Start. In this article we'll look at how to implement custom paging in ASP.NET 2.0 using SQL Server 2005's new ROW_NUMBER() feature. (For more information on SQL Server's new ranking features, including ROW_NUMBER(), see Returning Ranked Results with Microsoft SQL Server 2005.)

Read on to learn more!

 

Default Paging vs. Custom Paging
The GridView in 2.0 (and the DataGrid in 1.x) offers two paging models: default paging and custom paging. The two models provide a tradeoff between performance and ease of setting up/configuring/using. The SqlDataSource control uses default paging (although you can wrestle it into using custom paging); the ObjectDataSource uses default paging by default, but has an easy mechanism to indicate that it should use custom paging. Keep in mind that the GridView merely displays data; it's the GridView's data source control that is actually retrieving data from the database.

With default paging, each time a new page of data in displayed, all of the data is requeried from the GridView's data source. Once all of the data has been returned, the GridView selectively displays part of the entire set of data, based on the page of data the user is viewing and how many records per page are displayed. The key thing to understand here is that every single time a page of data is loaded - be it on the first page visit when viewing the first page of data or when the user postsbacks after requesting to view a different page of data - the entire data result is retrieved.

For example, imagine that you work at an eCommerce company and you want to allow the user to page through a list of the 150 products your company sells. Specifically, you want to display 10 records per page. Now, when a user visits the web page, all 150 records will be returned by the data source control, but the GridView will display the first 10 products (products 1 to 10). Next, imagine that the user navigates to the next page of data. This will cause a postback, at which point the GridView will rerequest all 150 records from the data source control, but this time only display the second set of 10 (products 11 to 20).

Caching and the SqlDataSource

The SqlDataSource allows for the DataSet it returns to be cached by simply setting the EnableCaching property. With a cached DataSet, stepping to another page does not require the database be requiried since the data being paged through is cached in memory. However, on the initial page load the same problem arises - all of the data must be loaded into the cached DataSet. Furthermore, you must worry about stale data with this approach (although if you use SQL cache dependencies, then this point is moot). Even with caching the DataSet, my unscientific tests found custom paging to be twice as fast... When we examine the performance metrics later, though, you'll see that this cached approach far outshines the non-cached approach. (But it still doesn't beat the custom paging approach!) For more on caching the DataSet returned by the SqlDataSource see Caching Data With the SqlDataSource.

With custom paging, you, the developer, have to do a bit more work. Rather than just being able to blindly bind the GridView to a data source control and check the "Enable Paging" checkbox, you have to configure the data source control to selectively retrieve only those records that should be shown for the particular page. The benefit of this is that when displaying the first page of data, you can use a SQL statement that only retrieves products 1 through 10, rather than all 150 records. However, your SQL statement has to be "clever" enough to be able to know how to just snip out the right subset of records from the 150.

The Performance Edge of Custom Paging

Realize that custom paging provides better performance than default paging because only those database records that need to be displayed are retrieved. In our products example, we assumed there were 150 products, showing 10 per page. With custom paging, if the user stepped through all 15 pages of data, precisely 150 records would have been queried from the database. With default paging, however, for each page of data, 150 records would have been accessed, leading to a total number of retrieved records of 15 times 150, or 2,250! While custom paging exhibits better performance, default paging is much easier to use. Therefore, I would encourage you to use default paging if the data you are paging through is relatively small and/or the database server is not heavily trafficked. If you have several hundred, thousands, or tens of thousands of records you are paging through, by all means use custom paging. However, for paging through something like the ASPFAQs.com database, which only has, currently, ~200 FAQs, default paging is sufficient. (Of course, if you use default paging on a small table with, say, 75 records, you are assuming that over time the table's row count will stay low. There will be some unhappy customers if you use default paging on that small table which later grows to be a table with 7,500 records!)

Efficiently Getting Back a Page of Data with SQL Server 2005
As discussed in an earlier 4Guys article, Returning Ranked Results with Microsoft SQL Server 2005, SQL Server 2005 introduces a number of new keywords for returning ranked results. In particular, the ROW_NUMBER() keyword enables us to associate a sequentially-increasing row number for the results returned. We can use ROW_NUMBER(), then, to get a particular page of data using a query like the following:

SELECT ... FROM (SELECT ... ROW_NUMBER() OVER(ORDER BY ColumnName) as RowNum FROM Employees e ) as DerivedTableName WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1

Here @startRowIndex is the index of the row to start from and @maximumRows is the maximum number of records to show per page. This query returns the subset of records whose ROW_NUMBER() is between the starting index and the starting index plus the page size.

To help concretize this concept, let's look at the following example. Imagine that we have an Employees table with 5,000 records (business is good!). The following query:

SELECT RowNum, EmployeeID, LastName, FirstName FROM (SELECT EmployeeID, LastName, FirstName ROW_NUMBER() OVER(ORDER BY EmployeeID) as RowNum FROM Employees e ) as EmployeeInfo

Would return results like:

RowNum	EmployeeID	LastName	FirstName
1	1000	Smith	Frank
2	1001	Jackson	Lucy
3	1011	Lee	Sam
4	1012	Mitchell	Jisun
5	1013	Yates	Scott
6	1016	Props	Kathryn
...
5000	6141	Jordan	DJ

Notice that even though the EmployeeID fields may have gaps and may not start at 1, the ROW_NUMBER() value starts at 1 for the first record and steadily increases. Therefore, if we want to view 10 records per page, and we want to see the third page, we know that we want records 31-40, and can accomplish that in a simple WHERE clause.

Configuring the ObjectDataSource for Custom Paging
As aforementioned, the SqlDataSource isn't designed to provide custom sorting capabilities. The ObjectDataSource, on the other hand, was designed to support this scenario. The ObjectDataSource is a data source control that's designed to access data from an object. The object can retrieve its data however it likes, be it from a Web Service, a database, the file system, an XML file... whatever. The ObjectDataSource doesn't care, it simply acts as a proxy between the data Web control that wants to consume the data (such as a GridView control) and the underlying data that the object provides. (For more information on the ObjectDataSource see the ObjectDataSource Control Overview.)

When binding a data Web control to an ObjectDataSource the "Enable Paging" option is available. If you've not specifically set up the ObjectDataSource to support custom paging, the paging provided will be of the default paging flavor. To setup custom paging with the ObjectDataSource you need to be using an object that provides the following functionality:

1. A method that takes in as its final two input parameters two integer values. The first integer value specifies the starting index from which to retrieve the data (it's zero-based), while the second integer value indicates the maximum number of records to retrieve per page. This method needs to return the precise subset of data being requested, namely the data starting at the specified index and not exceeding the total number of records indicated.
2. A method that returns an integer value specifying the total number of records that are being paged through. (This information is used by the data Web control when rendering the paging controls, since it needs to know how many total pages of data there are when showing page numbers or when deciding whether to enable the Next link.)

If you are using an underlying object that provides these features, configuring the ObjectDataSource to support custom paging is a breeze. Just set the following ObjectDataSource properties:

• Set EnablePaging to True
• Set SelectMethod to the method that accepts the starting index and maximum number of rows input parameters
• Set the StartRowIndexParameterName to the name of the integer input parameter in your SelectMethod that accepts the starting index; if you do not provide this value it defaults to startRowIndex
• Set the MaximumRowsParameterName to the name of the integer input parameter in your SelectMethod that accepts the maximum number of rows to return; if you do not provide this value it defaults to maximumRows
• Set SelectCountMethod to the method that returns the total number of records being paged through

That's it. Once you've done the above, the ObjectDataSource will be using the custom paging functionality. Of course, the hard part of this all is creating the underlying object that can intelligently grab the right subset of data. But once you have that object, configuring the ObjectDataSource to utilize custom paging is just a matter of setting a few properties.

Creating an Object That Supports Custom Paging
In order to bind an ObjectDataSource to a GridView we need to first have an underlying object that the ObjectDataSource will use, and this object must have methods for accessing a particular subset of the data and returning the number of rows to be paged through. As discussed in Joseph Chancellor's article, Using Strongly-Typed Data Access in Visual Studio 2005 and ASP.NET 2.0 and Brian Noyes's article Build a Data Access Layer with the Visual Studio 2005 DataSet Designer, creating objects that can be bound to the ObjectDataSource is a breeze in Visual Studio 2005. The first step is to define the stored procedures (or SQL queries) that will be used to populate the strongly-typed DataSets returned by these object's methods.

The download, available at the end of this article, has a sample database with 50,000 employee records (plus an easy way to add additional records in bulk). The database includes three stored procedures that are used by the two custom paging demos:

• GetEmployeesSubset(@startRowIndex int, @maximumRows int) - returns at most @maximumRows records from the Employees table starting at @startRowIndex when ordered by EmployeeID.
• GetEmployeesRowCount - returns the total number of records in the Employees table.
• GetEmployeesSubsetSorted(@sortExpression nvarchar(50), @startRowIndex int, @maximumRows int) - this sproc returns a page of data sorted by a specified sort expression. This allows the a page of data ordered by, say, Salary, to be returned. (GetEmployeesSubset returns records always ordered by EmployeeID.) This flexibility is needed if you want to create a sortable GridView that employs custom paging.

We won't be discussing implementing the sortable, custom pageable GridView in this article, although examples are included in this article's download; see Sorting Custom Paged Results for a look at how to create a custom paging and bi-directional sortable UI...

Once these stored procedures are created, I created the underlying object by adding a Typed DataSet to my project (Employees.xsd). I then added three methods, one against each of the stored procedures listed above. I ended up with an EmployeesTableAdapter object with methods GetEmployeesSubset(startRowIndex, maximumRows) and GetEmployeesRowCount() that can then be plugged into the ObjectDataSource's properties. (For step-by-step instructions on creating the Typed DataSet, see Using Strongly-Typed Data Access in Visual Studio 2005 and ASP.NET 2.0 and Scott Guthrie's blog entry Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0.)

Comparing the Performance of Default Paging and Custom Paging
To compare the performance between default and custom paging against the database included in this article's download (which has a table with 50,000 records), I used both SQL Profile and ASP.NET tracing to ascertain relative performance differences. (These techniques were done very unscientifically on my computer, which had other processes running in the background and such. While the results can hardly be called conclusive, I think the performance differences between the two methods clearly highlights custom paging's advantages.)

SQL Profiler Results
Default Paging (Selecting All Records from Employees) Duration (sec) Reads 1.455 383 1.405 383 1.434 383 1.394 383 1.365 383 Avg: 1.411 Avg: 383	Custom Paging (Selecting a Page of Records from Employees) Duration (sec) Reads 0.003 29 0.000 29 0.000 29 0.003 29 0.003 29 Avg: 0.002 Avg: 29

ASP.NET Trace Results
Default Paging (Selecting All Records from Employees) Page Load Duration (sec) 2.34136852588807 2.35772228034569 2.43368277253115 2.43237562315881 2.33167064529151 Avg: 2.379363969	Custom Paging (Selecting a Page of Records from Employees) Page Load Duration (sec) 0.0259611207569677 0.0280046765720224 0.0359054013848129 0.0295534767686955 0.0300096800012292 Avg: 0.029886871	Cached SqlDataSource (Selecting All Records, But Caching Them) Page Load Duration (sec) 2.39666633608461 0.0431529705591074 0.0443528437273452 0.0442313199023898 0.0491523364002967 Avg: 0.515511161

As you can see, the custom paging is roughly two order of magnitudes faster than the default paging. At the database level, the GetEmployeesSubset(@startRowIndex int, @maximumRows int) stored procedure is about 470 times faster than the simple SELECT statement that returns all records from the Employees table. Custom paging is about 120 times faster than default paging at the ASP.NET level. The reduction is performance gain is probably due to expensive workloads common to both approaches, namely setting up the database connection and issuing the command. Regardless, two orders of magnitude is a very big difference in the world of performance. And this disparity would be more pronounced with larger data sets or a server that was experiencing any kind of load.

The cached SqlDataSource has a high cost when the cache is empty, as it must go to the database and get all of the records. The frequency that the cache needs to be reloaded depends upon free resources on the web server (if you have low resources available, the cached DataSet may get evicted) and your cache expiration policy. After the data has been cached, though, it greatly improves in performance and is comparable to the custom paging approach. The 0.516 second average time would be amortized to closer to 0.05 seconds as more requests were served with the cached data.

Conclusion
As with the DataGrid in ASP.NET 1.x, the GridView in 2.0 offers two flavors of paging: default and custom. Default paging is easier to setup, but involves requerying the database when viewing each and every page of data. Custom paging, however, more intelligently just grabs those records needing to be displayed and therefore affords much higher degree of performance. SQL Server 2005 simplifies obtaining the precise subset of records for an arbitrary page due to its ability to rank results, which includes the ROW_NUMBER() feature.

If you are building web applications that need to scale or either now or in the future will allow users to page through potentially large data sets, it behooves you to implement custom paging.

Happy Programming!

• By Scott Mitchell