Sorting Custom Paged Results

By Scott Mitchell

Introduction

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...

- continued -

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 AllowSorting property 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 Employees database 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:

ID Last First Department Salary Hire Date
57344 Johnson Dave Sales $30,138.00 5/21/2003
57345 Wilson Aaron IT $36,454.00 5/31/2001
57346 Callister Gordon Sales $32,232.00 7/27/2002
57347 Maher Kim Sales $58,108.00 6/23/2002
57348 Schardt Scott IT $59,962.00 8/28/1997
57349 Richards Linda Executive $50,054.00 7/24/1998
57350 Richards Jen Executive $40,198.00 5/27/2003
57351 Davis Linda Sales $36,648.00 7/11/2002
57352 Moon Simon Sales $38,156.00 7/13/1998
57353 Bryant Tito Sales $48,756.00 10/19/1996

1 2 3 4 5 6 7 8 9 10 ...

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:

ID Last First Department Salary Hire Date
57344 Johnson Dave Sales $30,138.00 5/21/2003
57346 Callister Gordon Sales $32,232.00 7/27/2002
57345 Wilson Aaron IT $36,454.00 5/31/2001
57351 Davis Linda Sales $36,648.00 7/11/2002
57352 Moon Simon Sales $38,156.00 7/13/1998
57350 Richards Jen Executive $40,198.00 5/27/2003
57353 Bryant Tito Sales $48,756.00 10/19/1996
57347 Maher Kim Sales $58,108.00 6/23/2002
57349 Richards Linda Executive $50,054.00 7/24/1998
57348 Schardt Scott IT $59,962.00 8/28/1997

1 2 3 4 5 6 7 8 9 10 ...

This makes it look like Dave Johnson has the lowest salary at $30,180, but in reality he's just the lowest paid employee listed in the first page. (There are actually seven employees who make less, at $30,000.)

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:

SELECT EmployeeID, LastName, FirstName, DepartmentID, Salary, HireDate, DepartmentName FROM (SELECT EmployeeID, LastName, FirstName, e.DepartmentID, Salary, HireDate, d.Name as DepartmentName, ROW_NUMBER() OVER(ORDER BY EmployeeID) as RowNum FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID ) as EmpInfo WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1

This stored procedure associates a sequentially-increasing row number with each employee record ordered by EmployeeID. It then returns those records that fall between the bounds specified by the @startRowIndex and @maximumRows parameters.

If we want to get a certain page of employees ordered by some other column (such as Salary), we need to change the ORDER BY 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 DESC keyword to the ORDER BY clause.

SELECT EmployeeID, LastName, FirstName, DepartmentID, Salary, HireDate, DepartmentName FROM (SELECT EmployeeID, LastName, FirstName, e.DepartmentID, Salary, HireDate, d.Name as DepartmentName, ROW_NUMBER() OVER(ORDER BY Salary) as RowNum FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID ) as EmpInfo WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1

In theory we'd need a stored procedure for each column that could be sorted, or we'd need to add a bunch of IF/ELSE 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 CASE statement in the ORDER BY clause, as discussed in Dynamic ORDER BY Statements in Stored Procedures and The Power of SQL CASE Statements. 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 Dynamic 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.

-- Issue query DECLARE @sql nvarchar(4000) SET @sql = 'SELECT EmployeeID, LastName, FirstName, DepartmentID, Salary, HireDate, DepartmentName FROM (SELECT EmployeeID, LastName, FirstName, e.DepartmentID, Salary, HireDate, d.Name as DepartmentName, ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID ) as EmpInfo WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) + ' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + ' + CONVERT(nvarchar(10), @maximumRows) + ') - 1' -- Execute the SQL query EXEC sp_executesql @sql

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 @startRowIndex and not exceeding @maximumRows rows returned, with the results sorted by the EmployeeID column.
GetEmployeesSubsetSorted(@sortExpression, @startRowIndex, @maximumRows) - returns the subset of records starting at @startRowIndex and not exceeding @maximumRows rows returned, with the results sorted by @sortExpression.

Also note that the Employees table 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

With the GetEmployeesSubsetSorted stored procedure complete, the final step is to configure the ObjectDataSource to pass in the GridView's SortExpression value into this stored proceudre. To accomplish this, add a method to the Typed DataSet's EmployeesTableAdapter class called GetEmployeesSubsetSorted that invokes the GetEmployeesSubsetSorted stored procedure. Next, configure the ObjectDataSource to use this new method. Finally, set the ObjectDataSource's SortParameterName property to the name of the parameter that receives the sort expression (sortExpression).

Your ObjectDataSource's declarative markup should look something like:

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetEmployeesSubsetSorted" TypeName="EmployeesTableAdapters.EmployeesTableAdapter" EnablePaging="True" SelectCountMethod="GetEmployeesRowCount" SortParameterName="sortExpression"> </asp:ObjectDataSource>

The download at the end of this article includes examples that illustrate both custom paging without sorting and custom paging and sorting. The demo at Default.aspx shows custom paging without sorting; refer to the CustomPagingAndSorting.aspx demo to see an example using both custom paging and sorting...

The Performance of Custom Paging and Sorting

Adding sorting capabilities using the dynamic ORDER BY clause 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

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

Custom Paging w/Sorting

(Sorting Without Indexes)

Page Load Duration (sec)

0.207699581930106

0.231496461142408

0.142260259334636

0.139198697041104

0.223796599847187

Avg: 0.18889032

Custom Paging w/Sorting

(Sorting With Indexes)

Page Load Duration (sec)

0.0368784300798006

0.0590625344841314

0.027032765337494

0.0339626963762154

0.0313743785872227

Avg: 0.037662161

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 against the Employees table in your application...)

Conclusion

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 SortParameterName property 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 ORDER BY clause. However, this leads to less than optimal performance.

Happy Programming!

By Scott Mitchell

Attachments

Download the code used in this article