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