Custom Paging in ASP.NET 2.0 with SQL Server 2005By Scott Mitchell
|Custom Paging with SQL Server 2000|
|This article highlights features new to SQL Server 2005 that make retrieving a particular paged subset of data incredibly easy and efficient. For those who've yet to switch to SQL Server 2005, check out A More Efficient Method for Paging Through Large Result Sets. The stored procedure presented in that article can be plugged in for the stored procedure examined in this article if needed.|
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
AllowPagingproperty to True and add a few lines of code in the
PageIndexChangedevent 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
ROW_NUMBER() feature. (For more information on SQL Server's new ranking features, including
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 |
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:
@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
with 5,000 records (business is good!). The following query:
Would return results like:
Notice that even though the
EmployeeID fields may have gaps and may not start at 1, the
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
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:
- 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.
- 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.)
SelectMethodto the method that accepts the starting index and maximum number of rows input parameters
- Set the
StartRowIndexParameterNameto the name of the integer input parameter in your
SelectMethodthat accepts the starting index; if you do not provide this value it defaults to
- Set the
MaximumRowsParameterNameto the name of the integer input parameter in your
SelectMethodthat accepts the maximum number of rows to return; if you do not provide this value it defaults to
SelectCountMethodto the method that returns the total number of records being paged through
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
@maximumRowsrecords from the
Employeestable starting at
@startRowIndexwhen ordered by
GetEmployeesRowCount- returns the total number of records in the
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. (
GetEmployeesSubsetreturns records always ordered by
EmployeeID.) This flexibility is needed if you want to create a sortable GridView that employs custom paging.
Once these stored procedures are created, I created the underlying object by adding a Typed DataSet to my project (
I then added three methods, one against each of the stored procedures listed above. I ended up with an
GetEmployeesSubset(startRowIndex, maximumRows) and
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|
|ASP.NET Trace Results|
As you can see, the custom paging is roughly two order of magnitudes faster than the default paging. At the database level,
GetEmployeesSubset(@startRowIndex int, @maximumRows int) stored procedure is about 470 times faster than
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.
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
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.