Design a Custom Paging Solution
Turbocharge paging through large datasets in your Web apps' list screens with ASP.NET's DataGrid control.
by Brett Olges and Doug Safford
October 2002 Issue
For this solution: .NET Framework, SQL Server 7.0 or SQL Server 2000
Web applications are a great way to expose informa tion you hold in databases to both internal and external data consumers. Though complex business, access, and security rules might lie between the Web browser and the database server, at their core most Web applications are front ends providing users with simple and intuitive ways to query and update a database. One criterion users judge any database application by is its ability to deliver information quickly. Many Web apps provide one or several searchable list screens to help users locate records efficiently. A simple example is an online library system that enables users to search for books by author, title, or subject.
The new DataGrid control in ASP.NET makes creating list screens easier then ever for Web developers. In addition to its built-in presentation logic and methods, the control provides out-of-the box logic for breaking a list into pages. Paging provides the user with a more manageable list to look through and can sometimes provide better perceptible performance. DataGrid's built-in paging logic is easy to implement, but its convenience comes at the price of poorer performance when your app returns a large data set. In this article, we'll show you how to design a blazing-fast custom paging solution with the DataGrid that can handle extremely large result sets.
The technique we'll describe is faster and more efficient then the DataGrid's default paging because it doesn't require the database to send the entire result set to the Web server with every page request (see Figure 1 and the sidebar, "Make the DataGrid Control's Built-In Paging Efficient"). Instead, it sends only the rows you need to build each specific page. For example, if a user asks for page 3 of a 100-page result set, you'll send down rows 75 to 100 instead of rows 1 to 2500 (see Figure 2).
The first step in designing a custom paging solution is to build stored procedures on the database that accept parameters representing the first and last row number of the set of rows you need to return. SQL Server 7.0 and above include a "top" extension to Structured Query Language (SQL), allowing you to request only the first given number of rows be returned. Unfortunately, no extension in SQL Server lets you specify that a middle section of rows—for instance, rows 75 through 100—be returned. Oracle includes a rownum() extension in its procedural-language extension to SQL (PL/SQL) that can help you return a middle section of rows by using a statement such as "select * from Authors where Au_LName = 'Anderson' and rownum() >= 75 and rownum <= 100". However, because Oracle assigns the "rownum" value to a row before the sort, a PL/SQL query such as "select * from Authors where rownum <= 25 order by Au_LName" probably won't yield the results you expect. The technique we'll describe was developed for SQL Server, but the concept might be useful for Oracle developers too, especially if the list screen you're designing must return results in a specific order.
Assign Row Numbers
To create a stored procedure that returns only the rows you need for a given page, first you assign row numbers to the result set. Do this with either a temporary table or table variable (table variables are new to SQL Server 2000) in which you've declared an identity column. This article's example uses a table variable. Our performance tests have shown that temporary tables and table variables produce similar performance numbers. One advantage of table variables is that they're never stored to the tempdb database, so they won't expand your tempdb to deal with a particularly large query. Also, table variables are always destroyed when the stored procedure finishes, so you don't have to worry about dropping the object as you would with a temporary table. However, on a busy site using table variables might stress your SQL Server's memory. If you start to bottleneck on memory, consider changing your paging stored procedures to use temporary tables. Because temporary tables use disk storage, they'll free up memory table variables consume.
The general technique we show in pseudocode is valid for many of the top relational database management systems (see Listing 1). The code for a simple SQL Server 2000 paging stored procedure follows this strategy (see Listing 2).
The parameters @StartRow and @StopRow accept integers representing the first and last row of results your application needs to build a specific page. If a user requests page 3 of a set of pages with 25 rows each, the application will send in 51 as the @StartRow and 75 as the @StopRow. The declaration of the @t_table table variable includes an integer column called rownum and specifies it as IDENTITY. This column is particularly important to this technique for paging results. As you insert into the @t_table variable with the SQL statement that follows, the rownum identity column populates automatically with integers in sequential order. Most important, the identity column will honor the ORDER BY statement attached to the insert query. Remember that you can declare only one IDENTITY column for any given table variable or temporary table.
A SET ROWCOUNT command appears after the declaration of the @t_table variable and just before the SQL insert statement. This statement is of particular importance for performance. Most of the time this stored procedure takes to execute involves filling the table variable or temporary table with data. To make sure the stored procedure doesn't work any harder than it has to, you instruct SQL Server to limit the number of rows inserted to the @StopRow parameter. If a given page needs only rows 51 to 75 to display, there's no reason to insert more than 75 rows into the table variable or temporary table.
Select Rows to Return
The final SQL statement selects rows with rownum greater than or equal to the @StartRow parameter from the @t_table table variable. These are the rows that will be returned to the Web server and bound to the DataGrid object. Notice that to get rows 51 through 75 we had to insert 75 rows into the table variable. This means that as users request higher and higher pages, the performance for each page will slow down a bit. For instance, to create page 100 (rows 2451 through 2500) you'd have to fill the table variable or temp table with 2,500 rows before selecting out those with a rownum greater then or equal to 2451. Performance will depend on your own hardware and the size of the rows you're returning, but our testing with the example stored procedure showed that our SQL Server 2000 could return even page 100 in 200-250 milliseconds (compared to 4 milliseconds for page 1). It even returned the 500th page (rows 12,451 through 12,500) in 650 to 750 milliseconds. Users are unlikely to flip though 500 pages of results, but to lessen the stress on your database and network that would result if they did, design search screens so queries are specific enough to return a reasonable number of pages to the user.
Now that you have a stored procedure to do the paging work instead of the Web server, program your DataGrid object to use custom paging logic instead of its default paging. The DataGrid object's AllowPaging, AllowCustomPaging, and PagerStyle properties help you avoid having to write logic to keep track of which page a user is on and which page he or she has just requested. Set AllowCustomPaging to true, or else you might experience problems binding a DataReader or SQLDataReader to your DataGrid. Whenever possible, use SQLDataReaders—not a DataSet—to load your DataGrid. Our performance tests showed the SQLDataReader to be more than twice as fast as DataSets in building list screens. Leave the AllowPaging and PagerStyle properties unset. The problem with these properties is that for them to work correctly you have to maintain the DataGrid in viewstate, but for the best performance possible you'll set the EnableViewState property on your DataGrid to false. Although this approach means writing a little more of the paging logic yourself, performance will improve because the entire contents of your table don't have to be stored in viewstate on each round trip to the Web server.
Once you've shut off the DataGrid's capability to store itself in viewstate, add controls to the Web page that allow the user to navigate from page to page. Without persisting itself to the viewstate, the DataGrid object can no longer keep track of which page will be previous or next, but that's okay—you'll keep track of that yourself. The simplest design entails adding two buttons to the Web page—one to move to the next page and one to move to the previous page. To move forward one page, add code under the Next button click event to request the appropriate rows from your custom-paging stored procedure. For instance, if page 1 consists of rows 1 through 25, to show the user page 2, you send the stored procedure a @StartRow parameter of 26 and a @StopRow parameter of 50. To move back to page 1, send a @StartRow of 1 and a @StopRow of 25.
Here's the VB.NET code for the Next button-click event:
Private Sub ButtonNext_Click (ByVal sender As Object, _
ByVal e As System.EventArgs) Handles ButtonNext.Click
viewstate("StartRow") = viewstate("StartRow") + _
dgrid.PageSize
viewstate("StopRow") = viewstate("StartRow") + _
dgrid.PageSize
''' Run the paging stored procedure
''' and return a SQLDataReader
''' RunSprocReturnDR is our own method
''' (code not shown), not a method
''' native to the .NET Framework
dgrid.DataSource = RunSprocReturnDR (textAu_lname.Text, _
textAu_fname.Text, viewstate("StartRow"), _
viewstate("StopRow"))
dgrid.DataBind()
End Sub
Notice that you store to viewstate only the information necessary to keep track of the appropriate @StartRow and @StopRow values. This is much more efficient than storing the entire DataGrid object in viewstate. The Next and Previous buttons are probably the minimum navigation you have to provide users. More-elaborate navigation, such as page-number links or customizable page size, are still possible, but you should implement that logic yourself instead of using the DataGrid's built-in PagingStyle property. The performance you'll gain by keeping the DataGrid out of the viewstate is significant. Our testing showed we achieved a 54-percent performance gain by keeping the DataGrid object from persisting to the viewstate.
Performance on list screens is especially important for a pleasant user experience. A poorly designed list screen can bog down your application's performance, no matter how fast the back-end database is. The ASP.NET designers have given you a powerful tool in the DataGrid control. With a custom solution, you can avoid the performance pitfalls of depending on the DataGrid's default paging functionality. If searchable list screens play a role in your Web application, the extra work necessary on the database to implement custom paging logic will go a long way toward keeping you users happy and your application fast and scalable.
About the Authors
Brett Olges and Doug Safford are consultants with Empowered Software Solutions, a Chicago-based consulting firm and Microsoft Gold Certified Partner, where they specialize in .NET development and application architecture. Reach them by e-mail at .
|