Improve GridView Performance by Delegating Paging to SQL Server

The ASP.NET GridView control is an extremely powerful tool that gives you just about everything you need to manage the sorting and paging of a web page grid with minimal coding effort. However, this power comes at a high price in terms of performance and scalability. As the number of rows in your data source increases, performance falls off dramatically and you waste serious server memory resources to render each page of grid rows.

If you use SQL Server 2005 or later, I’m going to show you a technique that will make your grids so fast that you’ll wonder if you really clicked the mouse button, regardless of how much data you have. This technique seems to be widely called "SQL Paging" because you use SQL Server to manage the paging and sorting of data for you, rather than letting the GridView do it.

You can download the full source code for the examples in this article by following the instructions in the Download the Code side bar.

Standard GridView Behavior

To understand the value of what SQL Paging does for you, you need to understand how a GridView normally handles the task of paging and sorting.

On the surface, the process is very simple. You use an ObjectDataSource or other method to tell the GridView how to get data, set AllowPaging and AllowSorting to True, and off you go.

Under the hood, the GridView works its magic for you. It dynamically sorts your data as users click on columns and it automatically displays data in pages based on the page size you specify. To work this magic, the GridView has to pull the entire query result set into memory, sort it, and then retrieve the correct rows for the page.

These built-in GridView paging and sorting features do not scale well for several reasons:

• If the database server is not on the same machine as the Web server (which is typical in a production environment), the entire query result set is pulled over the network, even though only a small subset of that data is really needed.
• The entire query result set is loaded into memory on the web server. If the application has many concurrent users or the machine is starved for memory (also common in a production environment), performance degrades rapidly.
• The entire result set is sorted in memory. This process does not take advantage of SQL Server indexes and consumes valuable CPU cycles that would be better spent servicing browser requests.

SQL Server Solution

The built-in GridView features are wonderful from a usability standpoint. You just set a few properties and go. However, if you are willing to do a little more programming, you can dramatically improve the performance of your application. And I really do mean dramatically.

Just think for a moment about the implications of pushing the task of sorting and selecting rows down to SQL Server:

• SQL Server can take advantage of existing indexes for sorting. Even if it can’t use an index, its data sorting mechanism is generally more efficient than any solution you can apply in the application tier.
• If SQL Server selects the rows, then only the data you need to display in the grid goes over the network wire. That not only delivers the data faster, but it lowers the overall load on the network.
• The web server avoids wasting memory and CPU from loading and processing an entire result set, most of which it is just going to throw away.
• SQL Server caches frequently accessed data, so a popular grid page can see additional performance benefits.

Delegating Paging and Sorting to an ObjectDataSource

In order to push sorting and paging down to SQL Server, you need to do some extra programming. One way to approach the problem is to create a data class that you can attach to the grid as an ObjectDataSource. You delegate grid paging and sorting to your data class, which in turn passes those functions down to SQL Server.

Fortunately, the GridView and ObjectDataSource controls are designed to work together in this fashion. You don’t have to do anything particularly tricky to wire them up. You just set a few properties and add a bit of event code to your application.

Here’s an example of a GridView that uses an application data class to retrieve data from the database. The GridView handles the sorting and paging in this example (Style-related GridView tags have been omitted for readability):

<asp:GridView ID="gridDefaultPaging" runat="server"
 AllowPaging="True" AllowSorting="True" CellPadding="4"
 DataSourceID="dataEmpInfo" ForeColor="#333333" GridLines="None">
</asp:GridView>
<asp:ObjectDataSource ID="dataEmpInfo" runat="server"
 TypeName="EmpInfo" SelectMethod="GetFullDataSet">
</asp:ObjectDataSource>

The GetFullDataSet method does nothing more than return a dataset containing the columns needed for the grid. It does not filter or sort the data in any way, because those operations are being handled by the grid.

To delegate paging and sorting to the data class, you modify the ObjectDataSource as follows:

<asp:ObjectDataSource ID="dataEmpInfo" runat="server"
 TypeName="EmpInfo" SelectMethod="GetPagedDataSet"
 EnablePaging="true" SelectCountMethod="GetRowCount"
 StartRowIndexParameterName="startRow"
 MaximumRowsParameterName="pageSize" SortParameterName="sortColumns"
 OnObjectCreated="dataEmpInfo_ObjectCreated">
</asp:ObjectDataSource>

Things to note in the changed code:

• All of the properties are required for delegated paging to work correctly.
• The EnablePaging property tells the GridView to use the referenced data class instead of managing paging for itself.
• The SelectMethod property changes to "GetPagedDataSet." That method returns a DataSet that contains only the rows needed for the current page.
• The SelectRowCount property tells the GridView to use the GetRowCount method of the data class to determine how many total rows are available for paging.
• The OnObjectCreated property identifies the event handler to be called after the data class has been allocated but before it is used to retrieve data.
• The remaining properties tell the GridView how to pass the select method parameters that control paging within the data class. You’ll learn more about that when I cover the data class itself.

If you delegate paging, you must also delegate sorting. It’s not possible to select a page of data if you don’t know how that data is ordered. That’s one reason why you don’t see a separate EnableSorting property on the ObjectDataSource.

The last thing you need to do to wire up your data class is to establish a default sort order. When you first open the web page, the GridView control doesn’t yet know how the user wants to order the data, so it passes an empty string in the sortColumns parameter of the GetPagedDataSet call. However, your data class must know how the data is sorted in order to do paging.

You can deal with this situation in two ways. You could code the default sort order into the data class itself, or you could set an appropriate data class property as soon as the object is created. I take the second approach because I feel that default sorting should be a UI layer decision, not a data layer decision.

Fortunately, the ObjectDataSource raises an event that gives you access to the data class as soon as it is created. That event is the ObjectCreated event.

Here’s an example of how I set the default sort order in my data class using the ObjectCreated event:

protected void dataEmpInfo_ObjectCreated(object sender,
 ObjectDataSourceEventArgs e) {
   EmpInfo info = e.ObjectInstance as EmpInfo;
   if (info != null)
      info.SortColumns = "LastName, FirstName";
}

Things to note about this code:

• The code sets the SortColumns property in order to pass the default sort order to the data class. Later on, if the grid passes a value in the sortColumns parameter when it calls GetPageDataSet, that value overrides the default specified here. Again, you’ll see more about how this works when I go over the data class.
• The SortColumns property must specify column names that exist in the returned DataSet. The syntax is standard "ORDER BY" syntax, which means you can use the "DESC" qualifier (e.g. "LastName DESC") to indicate descending order.

Binding an ObjectDataSource to a Custom Data Class

The data class that you attach to your grid’s ObjectDataSource manages the transfer of data from SQL Server to the grid. It is responsible for telling SQL Server what data to retrieve, how that data should be sorted, and what subset of rows to select for the current page.

As the user pages through the grid and clicks column headings, the grid needs a way to pass this positioning and sorting information to your data class. The ObjectDataSource handles this task using the properties you configure:

• SelectCountMethod specifies what data class method should be called to retrieve a count of all rows in the result set. The GridView uses this information to determine how many total pages of data there are.
• SelectMethod specifies what data class method should be called to retrieve a new page of data.
• StartRowIndexParameterName specifies the name of the SelectMethod parameter that accepts the zero-based starting row index for the current page of data.
• MaximumRowsParameterName specifies the name of the SelectMethod parameter that accepts the number of rows in each page of data (i.e. the page size).
• SortParameterName specifies the name of the SelectMethod parameter that accepts the ORDER BY values to use when sorting data.

Here’s what the GetPagedDataSet method in my data class looks like:

public DataSet GetPagedDataSet(int startRow,
 int pageSize, string sortColumns) {
   if (sortColumns.Length > 0)
      _sortColumns = sortColumns;
   return GetDataSet(AssemblePagedSelectSql(startRow, pageSize));
}

Now you can see how the ObjectDataSource is wired up to my data class: SelectMethod is "GetPagedDataSet," StartRowParameterName is "startRow," MaximumRowsParameterName is "pageSize," and SortParameterName is "sortColumns." You can also see how the sortColumns parameter overrides the SortColumns property value, which the data class stores in the _sortColumns private object variable.

In addition, I created a GetRowCount method, as specified by the ObjectDataSource’s SelectCountMethod property, to retrieve a row count from the database (the example uses the AdventureWorks HumanResources.vEmployee view):

public int GetRowCount() {
   using (SqlConnection conn = new SqlConnection(_connectionString)) {
      conn.Open();
      using (SqlCommand cmd = new
       SqlCommand("SELECT COUNT(*) FROM HumanResources.vEmployee",
       conn)) {
         return (int)cmd.ExecuteScalar();
      }
   }
}

Obviously, your GetRowCount method should take into account any selection criteria that may affect the number of rows processed by the GetPagedDataSet method. The whole point of the GetRowCount method is to tell the grid how many total rows will be paged by GetPagedDataSet method.

Paging Data with SQL Server

Now that you have delegated paging and sorting to your data class, you could perform both operations within the data class itself, but there wouldn’t be much point to doing that. You probably couldn’t do a much better job than the GridView’s built-in paging and sorting mechanism. Instead, your data class should delegate paging and sorting down to SQL Server, where it can be done much more efficiently.

Prior to SQL Server 2005, managing paging and sorting within SQL Server required a stored procedure that implemented cute TRANSACT-SQL tricks. It could be done, but it wasn’t a task for the timid. One technique I’ve seen involved stuffing the query results into a temp table that included an identity field, and then retrieving the subset of rows using the identity. It was messy but effective.

SQL Server 2005 introduced a new built-in function called ROW_COUNT that makes SQL Paging much easier, once you figure out how to use it. When I initially researched the ROW_COUNT function myself, it became obvious that I wasn’t the only one who found the syntax confusing. Hopefully, the example I provide in this article will help clarify how you use it.

If you look back at the GetPagedDataSet method, you’ll see it calls the AssemblePagedSelectSql method. That method is responsible for assembling the SQL passed to SQL Server.

private string AssemblePagedSelectSql(int startRow, int pageSize) {
   StringBuilder sql = new StringBuilder();
   sql.Append("SELECT FROM (");
   sql.Append("SELECT EmployeeId, FirstName, MiddleName, LastName, JobTitle");
   sql.AppendFormat(", ROW_NUMBER() OVER (ORDER BY {0}) AS ResultSetRowNumber",
    _sortColumns);
   sql.Append(" FROM HumanResources.vEmployee) AS PagedResults");
   sql.AppendFormat(" WHERE ResultSetRowNumber > {0} AND ResultSetRowNumber <= {1}",
    startRow.ToString(), (startRow + pageSize).ToString());
   return sql.ToString();
}

It will be easier to see what is going on if I show you the query step-by-step, replacing the parameters with sample values. Assume startRow is 0, pageSize is 10, and _sortColumns is LastName.

The "core" select statement is this:

SELECT EmployeeId, FirstName, MiddleName, LastName, JobTitle
FROM HumanResources.vEmployee

You then use the ROW_NUMBER function to add a row number column to the result set:

SELECT EmployeeId, FirstName, MiddleName, LastName, JobTitle,
 ROW_NUMBER() OVER (ORDER BY LastName) AS ResultSetRowNumber
FROM HumanResources.vEmployee

Now you see why a default sort order is required. The ROW_NUMBER syntax requires the OVER clause with an ORDER BY expression. The syntax is a bit goofy, and it limits what you can do in your SELECT statement, but it does provide a way to dynamically number the rows in a result set.

The next step in assembling the query is to use the row number column (aliased as ResultSetRowNumber) to select only the rows you need from the results:

SELECT FROM (
   SELECT EmployeeId, FirstName, MiddleName, LastName, JobTitle,
      ROW_NUMBER() OVER (ORDER BY LastName) AS ResultSetRowNumber
   FROM HumanResources.vEmployee
) AS PagedResults
WHERE ResultSetRowNumber > 0 AND ResultSetRowNumber <= 10

The wrapping SELECT statement grabs all of the columns from the core SELECT statement, but it selects only the rows that it needs for the current page. If the relational operators in the WHERE clause make you do a double take, they make more sense when you realize that the startRow parameter value is 0-based, but the rows in the result set start at 1.

If you step back from the query a little, you’ll see a reusable design pattern. You could actually build a helper function that wraps any simple SQL statement with the outer "PagedResults" SELECT statement and inserts the ROW_NUMBER function call before the FROM clause of the inner statement.

Performance Enhancement without Drugs

If you have been struggling with a slow grid page because the GridView control is overwhelmed with the amount of data it has to process, you will be astounded at the difference you see once you implement the techniques described in this article.

It is tough to do performance testing on a development machine that has the web site and SQL Server both running locally, but I did do some testing with a 100,000-row table that had only a few columns. I went from about five seconds per page refresh without SQL Paging to a near-instantaneous refresh with it.

If you are running everything locally, the pages in the sample project will perform about the same, but if you modify the examples to work with a larger source of data on a networked SQL server, you’ll quickly see a difference.

The Sample Project

The sample project for this article is a Visual Studio 2005 solution that contains two web pages and a data class.

The Default.aspx page displays a grid based on data selected from an employee view in the SQL Server AdventureWorks sample database. The default page has a link to the SQLPagingGrid.aspx page, which shows exactly the same grid, but with the sorting and paging delegated to SQL Server through the EmpInfo data class.

Use this link to Download the Code for this article.