How To: Page Records in .NET Applications
Retired Content |
---|
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. |
J.D. Meier, Srinath Vasireddy, Ashish Babbar, and Alex Mackman
Microsoft Corporation
May 2004
Related Links
Home Page for Improving .NET Application Performance and Scalability
Chapter 12, Improving ADO.NET Performance
Checklist: ASP.NET Performance
Send feedback to Scale@microsoft.com
Summary: This How To presents a number of different paging solutions that enable you to efficiently browse through large result sets. Each solution exhibits different performance and scalability characteristics. The solution you choose depends on the nature of your application and other factors, such as database server capacity, load, network bandwidth, and the amount of data to be paged through.
- Microsoft® .NET Framework version 1.1
- Microsoft SQL Server™ 2000
Overview
What You Must Know
SELECT TOP
User-Specific Records
Application-Wide Records
Sample ASP.NET Solution Code
Additional Considerations
Additional Resources
Many Web and Windows Forms applications need to work with large result sets. For ease of use and efficiency, you may need to process and display the data in distinct chunks or pages and allow the user to page backward and forward through the results. A Web application's search results page is a good example of where this type of functionality is required.
There are a number of ways to tackle this problem. This How To describes the main options, along with the relative pros and cons of each approach.
When choosing a paging solution, you need to try to achieve a balance of the following:
- Server-side processing. The number of rows that the database server must process, together with any additional processing that the server needs to perform, such as creating temporary tables or sorting data, affects database server resource utilization. The server resource utilization affects the scalability of the solution.
- Bandwidth consumption. The number of rows returned to the client is an important consideration, particularly in scenarios where client bandwidth is limited.
- Client-side processing. The way in which the client handles retrieved records is significant. This is particularly important when the client is an ASP.NET application, which needs to scale and support multiple concurrent users.
Make sure that you avoid inefficient paging solutions like the following ones:
- Returning all rows for every request. With this approach, all the records are retrieved from the database and are returned across the network. The client then displays the required records. When the user moves to the next page, the server is accessed again and all of the rows are once again retrieved. This approach places unnecessary strain on the database server, consumes vast amounts of client memory, and passes large amounts of data over the network.
- Caching all of the records in memory. With this approach, the client ASP.NET application retrieves all of the rows and caches them. While this approach might be appropriate for small amounts of application wide data, be aware of the potential scalability problems that this approach can create especially with larger amounts of cached data. For example, when a user leaves the site after five minutes, the memory resident records are left on the Web server, consuming valuable memory until the session times out.
Regardless on the type of paging solution that you choose, you need to consider the following guidelines:
If you allow the user to specify the number of rows to be displayed, restrict the range of options by providing choices (for example, 5, 15, 25, and 50) in a drop - down list. Do not let the user specify an arbitrary page size that is out of your control.
Optimize your search query and use a WHERE clause to reduce the number of rows returned.
If you use stored procedures, optimize your query to minimize resource utilization on the server. Use such tools as SQL Query Analyzer, SQL Profiler, and the Index Tuning Wizard to analyze query and index performance.
Consider caching where possible. For example, consider caching the resultant DataSet (which usually contains static, nonvolatile data) on the data access client, for example in the ASP.NET cache, and bind controls to it.
If you use Oracle, use the OracleDataAdapter to load the requested page. The OracleDataReader supports the same functionality as the SqlDataReader with the same method names.
Several of the solutions presented in this How To use the SQL Server SELECT TOP construct. With Oracle databases, you must replace the TOP keyword with ROWNUM.
Also note that you create Oracle temporary tables by using the following syntax.
DECLARE GLOBAL TEMPORARY TABLE SESSION."#TEMP2" AS (SELECT t.seq_nbr, t.id, k.amt, k.qty)
A simple and effective paging approach is to use the TOP keyword on your SELECT query to restrict the size of the result set.
This approach relies on tables having a unique key column (such as an IDENTITY column, or a unique product ID or customer ID). The following pseudocode shows this technique.
SELECT TOP <pageSize> ProductID, ProductName, ProductPrice
FROM Products
WHERE [standard search criteria]
AND ProductID > <lastProductID>
ORDER BY [Criteria that leaves ProductID monotonically increasing]
GO
The client needs to maintain the lastProductID value and increment or decrement it by the chosen page size between successive calls.
This solution exhibits the following features:
- It only works for result sets sorted by a monotonically (consistently) increasing key column, which in practice limits the use of this approach.
- It does not cache data but pulls only the required records across the network.
- It supports simple navigation that enables the user to move to the next and previous pages. In the above example, the client application just needs to maintain the lastProductID value.
- It does not support advanced navigation that enables the user to move to a specific page number.
For tables that do not have a unique key column, you can also use SELECT TOP in conjunction with a nested query. This approach is recommended for handling user - specific queries and is described in the next section.
To provide paging through user-specific data, you can use SELECT TOP with nested queries. The main advantages of this approach is that it does not require a unique key column of any sort and it also supports advanced navigation, where the user is able to move to the next, previous, first, and last pages, and is also able to move to a specific page.
The following pseudocode illustrates this technique.
SELECT TOP <pageSize> CustomerID,CompanyName,ContactName,ContactTitle
FROM
(SELECT TOP <currentPageNumber * pageSize>
CustomerID,CompanyName,ContactName,ContactTitle
FROM
Customers AS T1 ORDER BY ContactName DESC)
AS T2 ORDER BY ContactName ASC
The inner SELECT statement selects a set of rows in descending order based (in this example) on the ContactName column. The number of rows selected is equal to the current page number times the page size. The outer SELECT statement then selects the top n rows, where n is the page size, and presents the data in ascending order based on the ContactName column.
The following code shows the above pseudocode implemented as a stored procedure that can be used to page through the Customers table in the Northwind database.
CREATE PROCEDURE UserPaging
(
@currentPage int = 1, @pageSize int =10
)
AS
DECLARE @Out int, @rowsToRetrieve int, @SQLSTRING nvarchar(1000)
SET @rowsToRetrieve = (@pageSize * @currentPage)
SET NOCOUNT ON
SET @SQLSTRING = N'select CustomerID,CompanyName,ContactName,ContactTitle from ( SELECT TOP '+ CAST(@pageSize as varchar(10)) + 'CustomerId,CompanyName,ContactName,ContactTitle from (SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +
'CustomerID,CompanyName,ContactName,ContactTitle
FROM (SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +
'CustomerID,CompanyName,ContactName,ContactTitle FROM Customers as T1
ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3) As T4 ORDER BY contactname ASC'
EXEC(@SQLSTRING)
RETURN
GO
This solution exhibits the following features:
- It does not cache data but pulls only the required records across the network.
- It does not require a unique key column.
- It supports advanced navigation where the user is able to move to the next, previous, first, and last pages, and is also able to move to a specific page. The client passes in the required current page number.
Performance can be significantly faster when you store records in the sort order in which they will be retrieved, although it is not always practical to do so. A clustered index physically stores the records sorted by the fields in the index. The drawback to clustered indexes is that they can slow down write operations because each insert needs to be sorted in sequence.
To page through application-wide data (records that apply to all users), you can use a global temporary table, which you can share across users. For example, this approach would work well for paging through a product catalog.
The solution uses a global temporary table for the following reasons:
- It pulls together the query data.
- By including an IDENTITY column in the temporary table, you can assign unique IDs to each row.
- You can use the unique IDs to provide advanced record navigation, including the ability to move to the next, previous, first, and last pages, together with the ability to move to a specific page.
You should base the lifetime of the temporary table on the volatility of your data and how frequently you need to refresh the cached data.
The following stored procedure code shows this approach. The procedure starts by calculating the offset using the current page number and page size, which are supplied as parameters. As rows are selected and inserted, the IDENTITY column ensures that each row has an incrementing, numeric key value. This column is used to limit the range of rows returned to the client.
CREATE PROCEDURE dbo.app_temp
(
@PageNum int = 1,
@PageSize int =10,
@ShipCity nvarchar(15),
@GreaterThanOrderDate datetime,
@TotalRows int OUTPUT
)
AS
DECLARE @iStart int, @iEnd int, @err int, @SQLSTRING nvarchar(3000)
SET NOCOUNT ON
SELECT @iStart = (((@PageNum - 1) * @PageSize) + 1)
SELECT @iEnd = @PageNum*@PageSize
--Check if the temporary table already exists. If so there is no
--need to create it again.
IF OBJECT_ID('tempdb..##TempTable1') IS NOT NULL
BEGIN
SELECT * from ##TempTable1 where I_ID between @iStart and @iEnd
SELECT @TotalRows=COUNT(*) FROM ##TempTable1
RETURN
END
-- creating table with as few columns as possible
CREATE TABLE ##TempTable1
(I_ID int identity(1,1) primary key, CustomerID nchar(5),
OrderDate datetime,RequiredDate datetime, ShipName nvarchar(40))
--inserting records
SET @SQLSTRING = N'insert into ##TempTable1 '+
' SELECT '+'CustomerID,OrderDate,RequiredDate,
ShipName FROM Orders' + ' Where
ShipCity like '+''''+@ShipCity+''''+ ' AND OrderDate> '+''''+
CAST(@GreaterThanOrderDate AS nvarchar(50))+''''+ ' Order by OrderDate'
EXEC(@SQLSTRING)
SELECT @TotalRows=COUNT(*) FROM ##TempTable1
SELECT * from ##TempTable1 where I_ID between @iStart and @iEnd
RETURN
GO
This solution exhibits the following features:
- It returns only the required records across the network.
- It works for tables that do not contain a unique primary key column. An IDENTITY column is used on the temporary table to provide row uniqueness.
- It supports advanced navigation where the user is able to move to the next, previous, first, and last pages, and is also able to move to a specific page.
This section presents a sample ASP.NET application that uses the user-specific paging solution and the application wide-paging solution described earlier.
This solution uses nested SELECT TOP statements to implement the user specific paging solution outlined earlier. The test harness used in this solution consists of an ASP.NET page containing a table that in turn contains a DataGrid and navigation controls. The page navigation controls enable you to move to the first, last, next, and previous pages, and also to a specific page. The Web page also displays the current page number and total page count.
To create the ASP.NET page
Create a blank Web page named Userpaging.aspx and add the following code.
<%@ Page Language="C#" Debug="true" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <script > string _storedProc = "app_temp"; int _currentPageNumber = 1; // current selected page int _totalRecords = 0; // total records in table int _pageSize = 10; // number of rows per page int _totalPages = 1; // total pages string _connStr = "server=(local);database= northwind;Trusted_Connection=yes"; private void Page_Load(object sender, System.EventArgs e) { if ( !Page.IsPostBack ) { BindData(); } CreateLinks(); } // The BindData method constructs a SQL query that uses nested SELECT TOP // statements (as described earlier) to retrieve a specified page of data. // public void BindData() { SqlConnection myConnection = new SqlConnection(_connStr); String strCmd = ""; StringBuilder sb = new StringBuilder(); sb.Append("select top {0} CustomerID,CompanyName,ContactName,ContactTitle from (select top {1} CustomerID,CompanyName,ContactName,ContactTitle from Customers "); sb.Append("as t1 order by contactname desc) "); sb.Append("as t2 order by contactname asc"); strCmd = sb.ToString(); sb = null; // Set pseudoparameters: TableName, KeyField and RowIndex strCmd = String.Format(strCmd, _pageSize, _currentPageNumber * _pageSize); // Prepare the command SqlCommand myCommand = new SqlCommand(strCmd,myConnection); SqlDataAdapter sa = new SqlDataAdapter(myCommand); DataSet searchData = new DataSet("SearchData"); try { myConnection.Open(); sa.Fill(searchData); MyDataGrid.DataSource = searchData; MyDataGrid.DataBind(); } finally { myConnection.Close(); } CurrentPage.Text = _currentPageNumber.ToString(); if ( !Page.IsPostBack ) { using (SqlConnection conn = new SqlConnection(_connStr)) { SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT Count(*) FROM Customers"; conn.Open(); _totalRecords = (int)cmd.ExecuteScalar(); _totalPages = _totalRecords / MyDataGrid.PageSize; TotalPages.Text = _totalPages.ToString(); } } else { _totalPages = int.Parse ( TotalPages.Text ); } if (_currentPageNumber == 1) { PreviousPage.Enabled = false; if (_totalPages > 1) NextPage.Enabled = true; else NextPage.Enabled = false; } else { PreviousPage.Enabled = true; if (_currentPageNumber == _totalPages) NextPage.Enabled = false; else NextPage.Enabled = true; } } // The CreateLinks method creates a link button for each page in the target table. // Users of the Web form can use these to directly move to a specific page number. // private void CreateLinks() { Table tbl = new Table(); TableRow tr = new TableRow(); int j=0; for(int i=1;i<=int.Parse(TotalPages.Text);i++,j++) { if(j==20) { tbl.Rows.Add(tr); j=0; tr = new TableRow(); } LinkButton link = new LinkButton(); link.Text = i.ToString(); link.CommandName = i.ToString(); link.Command += new CommandEventHandler(NavigationLink_Click); link.CssClass="pageLinks"; link.EnableViewState=true; TableCell cell = new TableCell(); cell.Controls.Add(link); tr.Cells.Add(cell); } tbl.Rows.Add(tr); SpecificPage.Controls.Add(tbl); } // This method handles the click event for the Next, Previous, First and Last // link buttons. // protected void NavigationLink_Click ( Object sender, CommandEventArgs e ) { switch ( e.CommandName ) { case "First": _currentPageNumber =1; break; case "Next": _currentPageNumber = int.Parse( CurrentPage.Text ) + 1; break; case "Prev": _currentPageNumber = int.Parse( CurrentPage.Text ) - 1; break; case "Last": _currentPageNumber =int.Parse(TotalPages.Text); break; default: _currentPageNumber=int.Parse(e.CommandName.ToString()); break; } BindData(); } </script> <html> <body> <form runat=server> <table> <tr><td><!-- DataGrid goes here --></td></tr> <asp:DataGrid AllowPaging="True" AllowCustomPaging="True" ID="MyDataGrid"> <PagerStyle Visible="False"></PagerStyle> </asp:DataGrid> <tr><td><!-- navigation goes here --></td></tr> <asp:LinkButton id="FirstPage" CommandName="First" OnCommand="NavigationLink_Click" Text="[First Page]"> </asp:LinkButton> <asp:LinkButton id="PreviousPage" CommandName="Prev" OnCommand="NavigationLink_Click" Text="[Previous Page]"> </asp:LinkButton> <asp:LinkButton id="NextPage" CommandName="Next" OnCommand="NavigationLink_Click" Text="[Next Page]"> </asp:LinkButton> <asp:LinkButton id="LastPage" CommandName="Last" OnCommand="NavigationLink_Click" Text="[Last Page]"> </asp:LinkButton> <asp:PlaceHolder ID="SpecificPage"></asp:PlaceHolder> Page <asp:Label id="CurrentPage" ></asp:Label> of <asp:Label id="TotalPages" ></asp:Label> </table> </form> </body> </html>
Save the Userpaging.aspx file.
To configure IIS and the ASPNET user
Create a virtual directory called DataPaging in Internet Information Services (IIS).
Copy Userpaging.aspx to the IIS virtual directory.
Ensure that a Windows login exists for the local ASPNET account in your SQL Server database.
To grant login and database access for the ASPNET account, use SQL Query Analyzer to execute the following commands against the Northwind database. Replace LocalMachine with your local computer name.
exec sp_grantlogin [LocalMachine\ASPNET] exec sp_grantdbaccess [LocalMachine\ASPNET]
To test the Web page and paging functionality
- Use Internet Explorer and browse to https://localhost/DataPaging/Userpaging.aspx.
- Test the paging functionality and the various types of navigation.
This solution uses a global temporary table to implement the application-wide paging solution outlined earlier from an ASP.NET application.
To create the required stored procedure
In SQL Query Analyzer, execute the following SQL script against the Northwind database to create the stored procedure.
app_temp.sql
CREATE PROCEDURE dbo.app_temp ( @PageNum int = 1, @PageSize int =10, @ShipCity nvarchar(15), @GreaterThanOrderDate datetime, @TotalRows int OUTPUT ) AS DECLARE @iStart int, @iEnd int, @err int, @SQLSTRING nvarchar(3000) SET NOCOUNT ON SELECT @iStart = (((@PageNum - 1) * @PageSize) + 1) SELECT @iEnd = @PageNum*@PageSize --Check if the temporary table already exists. If so there is no --need to create it again. IF OBJECT_ID('tempdb..##TempTable1') IS NOT NULL BEGIN SELECT * from ##TempTable1 where I_ID between @iStart and @iEnd SELECT @TotalRows=COUNT(*) FROM ##TempTable1 RETURN END -- creating table with as few columns as possible CREATE TABLE ##TempTable1 (I_ID int identity(1,1) primary key, CustomerID nchar(5), OrderDate datetime,RequiredDate datetime, ShipName nvarchar(40)) --inserting records SET @SQLSTRING = N'insert into ##TempTable1 '+ ' SELECT '+'CustomerID,OrderDate,RequiredDate, ShipName FROM Orders' + ' Where ShipCity like '+''''+@ShipCity+''''+ ' AND OrderDate> '+''''+ CAST(@GreaterThanOrderDate AS nvarchar(50))+''''+ ' Order by OrderDate' EXEC(@SQLSTRING) SELECT @TotalRows=COUNT(*) FROM ##TempTable1 SELECT * from ##TempTable1 where I_ID between @iStart and @iEnd RETURN GO
Grant execute permissions to the ASPNET account. In SQL Query Analyzer, execute the following commands against the Northwind database. Replace LocalMachine with your local computer name.
grant execute on app_temp to [LocalMachine\ASPNET]
To create Appwidepaging.aspx
Create a blank Web page named Appwidepaging.aspx in the virtual directory called DataPaging and add the following code.
<%@ Page Language="C#" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.SqlClient" %> <script > string _storedProc = "app_temp"; int _currentPageNumber = 1; // current selected page int _totalRecords = 0; // total records in table int _pageSize = 10; // number of rows per page int _totalPages = 1; // total pages private void Page_Load(object sender, System.EventArgs e) { if ( !Page.IsPostBack ) { BindData(); } CreateLinks(); } public void BindData() { SqlConnection myConnection = new SqlConnection("server=(local);database= northwind;Trusted_Connection=yes"); SqlCommand myCommand = new SqlCommand(_storedProc, myConnection); myCommand.CommandType = CommandType.StoredProcedure; myCommand.Parameters.Add("@PageNum", SqlDbType.Int).Value = _currentPageNumber ; myCommand.Parameters.Add("@PageSize",SqlDbType.Int).Value = _pageSize; myCommand.Parameters.Add("@ShipCity",SqlDbType.NVarChar,15).Value = "%"; ; myCommand.Parameters.Add("@GreaterThanOrderDate",SqlDbType.DateTime).Value = DateTime.Parse("7/4/1996"); myCommand.Parameters.Add("@TotalRows", SqlDbType.Int).Direction = ParameterDirection.Output; SqlDataAdapter sa = new SqlDataAdapter(myCommand); DataSet searchData = new DataSet("SearchData"); try { myConnection.Open(); sa.Fill(searchData); MyDataGrid.DataSource = searchData; MyDataGrid.DataBind(); } finally { myConnection.Close(); } CurrentPage.Text = _currentPageNumber.ToString(); if ( !Page.IsPostBack ) { _totalRecords = (int)(myCommand.Parameters["@TotalRows"].Value); _totalPages = _totalRecords / MyDataGrid.PageSize; TotalPages.Text = _totalPages.ToString(); } else { _totalPages = int.Parse ( TotalPages.Text ); } if (_currentPageNumber == 1) { PreviousPage.Enabled = false; if (_totalPages > 1) NextPage.Enabled = true; else NextPage.Enabled = false; } else { PreviousPage.Enabled = true; if (_currentPageNumber == _totalPages) NextPage.Enabled = false; else NextPage.Enabled = true; } } private void CreateLinks() { Table tbl = new Table(); TableRow tr = new TableRow(); int j=0; for(int i=1;i<=int.Parse(TotalPages.Text);i++,j++) { if(j==20) { tbl.Rows.Add(tr); j=0; tr = new TableRow(); } LinkButton link = new LinkButton(); link.Text = i.ToString(); link.CommandName = i.ToString(); link.Command += new CommandEventHandler(NavigationLink_Click); link.CssClass="pageLinks"; link.EnableViewState=true; TableCell cell = new TableCell(); cell.Controls.Add(link); tr.Cells.Add(cell); } tbl.Rows.Add(tr); SpecificPage.Controls.Add(tbl); } protected void NavigationLink_Click ( Object sender, CommandEventArgs e ) { switch ( e.CommandName ) { case "First": _currentPageNumber =1; break; case "Next": _currentPageNumber = int.Parse( CurrentPage.Text ) + 1; break; case "Prev": _currentPageNumber = int.Parse( CurrentPage.Text ) - 1; break; case "Last": _currentPageNumber =int.Parse(TotalPages.Text); break; default: _currentPageNumber=int.Parse(e.CommandName.ToString()); break; } BindData(); } </script> <html> <head> </head> <body> <form > <table> <tbody> <tr> <td> <asp:DataGrid id="MyDataGrid" AllowPaging="True" AllowCustomPaging="True"> <PagerStyle visible="False"></PagerStyle> </asp:DataGrid> </td> </tr> <tr> <td> <asp:LinkButton id="FirstPage" CommandName="First" OnCommand="NavigationLink_Click" Text="[First Page]"></asp:LinkButton> <asp:LinkButton id="PreviousPage" CommandName="Prev" OnCommand="NavigationLink_Click" Text="[Previous Page]"></asp:LinkButton> <asp:LinkButton id="NextPage" CommandName="Next" OnCommand="NavigationLink_Click" Text="[Next Page]"></asp:LinkButton> <asp:LinkButton id="LastPage" CommandName="Last" OnCommand="NavigationLink_Click" Text="[Last Page]"></asp:LinkButton> <asp:PlaceHolder id="SpecificPage" ></asp:PlaceHolder> Page <asp:Label id="CurrentPage" ></asp:Label>of <asp:Label id="TotalPages" ></asp:Label></td> </tr> </tbody> </table> </form> </body> </html>
Save Appwidepaging.aspx.
To test the Web page and paging functionality
- Use Internet Explorer and browse to https://localhost/DataPaging/Appwidepaging.aspx.
- Test the paging functionality and the various types of navigation.
In addition to the approaches described earlier in this How To, there are a number of other paging approaches, although the alternatives tend to offer limited performance and scalability characteristics. The additional approaches explained in this section are:
- DataAdapter's overloaded Fill method.
- DataGrid's default paging feature.
You can use the following overloaded Fill method of the DataAdapter to page through rows.
public int Fill(
DataSet dataSet,
int startRecord,
int maxRecords,
string srcTable
);
The startRecord parameter indicates the zero-based index of the first record to retrieve. The maxRecords parameter indicates the number of records, starting from startRecord, to copy into the new DataSet.
The DataAdapter copies all of the results into a newly generated DataSet and discards any unnecessary results. This means that a lot of unnecessary data could be pulled across the network to the data access client, which is the primary drawback to this approach. For example, if you have 1,000 records and want to retrieve records 900 through 950, the first 899 records are still pulled across the network and discarded on the client side. This overhead is likely to be minimal for small result sets, but could be significant when you page through larger sets of data. Therefore, this approach is not a good choice for paging through large query result sets.
To display a single page, the DataGrid object's default paging behavior retrieves all of the records each time the user navigates to a new page. This approach is not a good choice for paging through large query result sets.
For more information, see the following resources:
- Chapter 4, "Architecture and Design Review of a .NET Application for Performance and Scalability"
- Chapter 12, "Improving ADO.NET Performance"
- Chapter 13, "Code Review: .NET Application Performance"
- "Checklist: ADO.NET Performance" in the "Checklists" section of this guide
- Microsoft Knowledge Base article 318131, "HOW TO: Page Through a Query Result for Better Performance," at https://support.microsoft.com/default.aspx?scid=kb;en-us;318131.
Retired Content |
---|
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. |