Sorting Custom Paged ResultsBy Scott Mitchell
Last week I wrote an article on implementing custom paging in ASP.NET 2.0 aptly named Custom Paging in ASP.NET 2.0 with SQL Server 2005. In that article I covered how to efficiently page through large amounts of data using Typed DataSets, an ObjectDataSource control, and SQL Server 2005's new
ROW_NUMBER()keyword. Custom paging intelligently grabs just the subset of records needed to display per page, whereas default paging, while easier to implement, suffers when paging through sufficiently large data because all records are retrieved with each page of data. As my unscientific tests showed in Custom Paging in ASP.NET 2.0 with SQL Server 2005, paging through a 50,000 record database took over two seconds to display each page of data with default paging, while custom paging was able to chew through each page in under 0.03 seconds!
Nothing is free in life, and such is the case with custom paging. While custom paging offers astoundingly better performance when paging through large results, it is more difficult to implement. Furthermore, once custom paging has been added, even more work must be done to allow the user to sort the results. In this article we'll see how to augment custom paging to include support for bi-directional sorting of the results. Read on to learn more!
Before tackling this article make sure you have read and understand Custom Paging in ASP.NET 2.0 with SQL Server 2005...
Why Sorting With Custom Paging Doesn't Work Out of the Box
Displaying and working with data in ASP.NET 2.0 is much simpler than in ASP.NET 1.x thanks in large part to the GridView, 2.0's upgrade of the ASP.NET 1.x DataGrid. When binding a SqlDataSource to the GridView or an ObjectDataSource that returns an object that supports sorting (such as a strongly-typed DataTable), enabling bi-directional sorting in the GridView is as simple as checking the "Enable Sorting" checkbox in the GridView's smart tag (or, alternatively, by manually setting its
AllowSortingproperty to True). No code is needed!
This no code, simply check a checkbox approach works great when binding results to an ObjectDataSource if the data is not pageable or default paging is used. If custom paging is used, however, a bit more effort is required to get sorting to work as expected. If you use custom paging you can still configure the GridView to support sorting, and the GridView will dutifully turn its header columns into LinkButtons. Clicking one of these header columns, however, will only sort the currently displayed data by the column header clicked.
To clarify this, consider the following: Imagine that we are display 50,000 records from the
table using custom paging, just like we did in Custom Paging in ASP.NET 2.0 with SQL Server 2005. Now, imagine that a
user comes to the page that displays this information, showing the first page of data. Recall that, by default, the grid is
sorted initially by
EmployeeID, so the first 10 (of 50,000) employees are displayed:
Now, imagine that our user wants to see how makes the least amount of money in this company, so she clicks on the Salary header LinkButton.
This would cause a postback and requery the data from the ObjectDataSource. After retrieving the data from the underlying
object, but before returning it to the data Web control that requested it, the ObjectDataSource would sort the strongly-typed
DataTable returned based on the
SortExpression value of the sorted row ("Salary", in this case).
Do you see the problem? Since custom paging is only returning the precise 10 employees to display, the ObjectDataSource gets back the exact same 10 employees on Page 1. That means that the displayed results are the 10 employees from Page 1, sorted by their salaries:
Returning the Correct Page of Sorted Data
In order to correctly implement custom paging and sorting, we first must have a way that we can grab the correct subset of employee records sorted by a particular column. Recall from Custom Paging in ASP.NET 2.0 with SQL Server 2005 that the stored procedure used to return the proper subset of employees looked like:
This stored procedure associates a sequentially-increasing row number with each employee record ordered by
It then returns those records that fall between the bounds specified by the
If we want to get a certain page of employees ordered by some other column (such as Salary), we need to change the
clause. The following example sorts the results by Salary in ascending order (from least to most paid);
if we want to sort the results by salary in descending order, we'd need to add the
ORDER BY clause.
In theory we'd need a stored procedure for each column that could be sorted, or we'd need to add a bunch of
T-SQL statements in a single stored procedure to handle these cases. Ick.
Thankfully there are a few options for dynamic SQL in a stored procedure. One option would to be to use a
statement in the
ORDER BY clause, as discussed in Dynamic
ORDER BY Statements
in Stored Procedures and The Power of SQL
Unfortunately this hampers the performance when sorting non-string columns because of conversion requirements and so forth.
A better performing option is to build and execute a dynamic SQL statement, a technique which is also discussed in
ORDER BY Statements in Stored Procedures. This approach, with indexes on the columns that may be
sorted, offers performance time akin to the custom paging without sorting.
The database that's part of the download available at the end of this article includes the following two stored procedures:
GetEmployeesSubset(@startRowIndex, @maximumRows)- returns the subset of records starting at
@startRowIndexand not exceeding
@maximumRowsrows returned, with the results sorted by the
GetEmployeesSubsetSorted(@sortExpression, @startRowIndex, @maximumRows)- returns the subset of records starting at
@startRowIndexand not exceeding
@maximumRowsrows returned, with the results sorted by
Employeestable has an index with a fill factor of 90 for each of the columns that can be sorted. Without the index, the ordering of the results takes longer and the end-to-end execution time may be up to an order of magnitude worse. As the very unscientific testing results shown later in this article indicate, without indexes the ordering the sorting takes on average 0.189 seconds; with the indexes added the average time drops to 0.038 seconds!
Configuring the ObjectDataSource for Sorting with Custom Paging
GetEmployeesSubsetSortedstored procedure complete, the final step is to configure the ObjectDataSource to pass in the GridView's
SortExpressionvalue into this stored proceudre. To accomplish this, add a method to the Typed DataSet's
GetEmployeesSubsetSortedthat invokes the
GetEmployeesSubsetSortedstored procedure. Next, configure the ObjectDataSource to use this new method. Finally, set the ObjectDataSource's
SortParameterNameproperty to the name of the parameter that receives the sort expression (
Your ObjectDataSource's declarative markup should look something like:
Default.aspxshows custom paging without sorting; refer to the
CustomPagingAndSorting.aspxdemo to see an example using both custom paging and sorting...
The Performance of Custom Paging and Sorting
Adding sorting capabilities using the dynamic
ORDER BYclause impacts the performance of the custom paging, but still offers significantly better execution than with default paging for sufficiently large data sets. The following tables show some very unscientific results for default paging, custom paging, and custom paging with sorting.
|ASP.NET Trace Results|
When sorting prior to indexing the columns, the average end-to-end time from the ASP.NET page accessing the data is 0.189
seconds. After adding non-clustered indexes with fill factor 90, the average time drops to 0.038 seconds, close to an entire
order of magnitude improvement. This difference would be more pronounced for even larger data sets. (For ideal performance
considerations, the actual fill factor and padding settings for the indexes would depend on the ratio of reads to writes
Employees table in your application...)
While custom paging can greatly improve the performance of your application when paging through sufficiently large resultsets, as we discussed in Custom Paging in ASP.NET 2.0 with SQL Server 2005 it's not nearly as trivial to implement as default paging. Once custom paging is implemented, it still lacks the out of the box bi-directional sorting capabilities inherent with default paging. To utilize both custom paging and sorting, we need to augment our stored procedure in order to return the correct page of data in the specified sorted order. Next, the data access layer (in this example, a Typed DataSet) needs to be updated to include a method that uses the stored procedure for custom paging over sorted results that takes in an input parameter that indicates the column to sort by. Finally, the ObjectDataSource's
SortParameterNameproperty needs to be set to the name of the underlying method's sort-related input parameter.
As we saw in this article, a single stored procedure can be created to allow for sorting using a dynamic
clause. However, this leads to less than optimal performance.