Paging of Large Resultsets in ASP.NET

• Download PagingTest Solution (C#) - 55.8 Kb
• Download Paging_Procedures SQL script - 2.48 Kb
• Download Paging_LargeTable SQL script - 0.6 Kb

Introduction

The paging of a large database resultset in Web applications is a well known problem. In short, you don''''t want all the results from your query to be displayed on a single Web page, so some sort of paged display is more appropriate. While it was not an easy task in the old ASP, the DataGrid control in the ASP.NET simplifies this to a few lines of code. So, the paging is easy in ASP.NET, but the default behavior of the DataGrid is that all resulting records from your query will be fetched from SQL server to the ASP.NET application. If your query returns a million records this will cause some serious performance issues (if you need convincing, try executing such a query in your web application and see the memory consumption of the aspnet_wp.exe in the task manager). That''''s why a custom paging solution is required where desired behavior is to fetch only the rows from the current page.

There are numerous articles and posts concerning this problem and several proposed solutions. My goal here is not to present you with an amazing solves-it-all procedure, but to optimize all the existing methods and provide you with a testing application so you can do evaluation on your own. Here is a good starting point article which describes many different approaches and provides some performance test results:

How do I page through a recordset?

I was not satisfied with the most of them. First, half of the methods use old ADO and are clearly written for the "old" ASP. The rest of the methods are SQL server stored procedures. Some of them yield poor response times as you can see from the author’s performance results at the bottom of the page, but several have caught my attention.

Generalization

The three methods I decided to closely look into are the ones the author calls TempTable, DynamicSQL and Rowcount. I''''ll refer to the second method as the Asc-Desc method in the rest of this text. I don''''t think DynamicSQL was a good name, because you can apply dynamic SQL logic to the other methods too. The general problem with all these stored procedures is that you have to assess which columns you''''ll allow sorting for and that won''''t probably be just the PK column(s). This leads to a new set of problems – for each query you want to display via paging you must have as many different paging queries as you have different sorting columns. This means that you will either have a different stored procedure (regardless of the paging method applied) for each sorting column or you''''ll try to generalize this to only one stored procedure with the help of dynamic SQL. This has a slight performance impact, but increases maintainability if you need to display many different queries using this approach. Thus, I’ll try to generalize all of the stored procedures in this text with dynamic SQL, but in some cases it will be possible to achieve only a certain level of generalization, so you’ll still have to write separate stored procedures for some complex queries.

The second problem with allowing other sorting columns beside the PK column(s) is that if those columns are not indexed in some way, none of these methods will help. In all of them a paged source must be sorted first and the cost of using ordering by non-indexed column is immense for large tables. The response times are so high that all the procedures are practically unusable in this case (the response varies from couple of seconds to couple of minutes depending on the size of the tables and the starting record being fetched). The indexing of other columns brings more performance issues and may be undesirable, for example it might significantly slow you down in a situation where you have a lot of daily imports.

TempTable

The first one I would comment on is the TempTable method. This is actually a widely proposed solution and I encountered it several times. Here is another article that describes it along with the explanation and a sample how to use custom paging with the DataGrid:

ASP.NET DataGrid Paging Part 2 - Custom Paging

The methods in both articles could be optimized with just the Primary Key data copied to the temp table and then doing the join with the main query. Therefore, the essence of this method would be the following

CREATE TABLE #Temp (
    ID int IDENTITY PRIMARY KEY,
    PK  /* here goes PK type */
)

INSERT INTO #Temp SELECT PK FROM Table ORDER BY SortColumn

SELECT ... FROM Table JOIN #Temp temp ON Table.PK = temp.PK ORDER BY temp.ID 
WHERE ID > @StartRow AND ID < @EndRow

Asc-Desc

This method uses default ordering in a subquery and then applies the reverse ordering. The principle goes like this

DECLARE @temp TABLE (
    PK  /* PK Type */ NOT NULL PRIMARY 
)

INSERT INTO @temp 
SELECT TOP @PageSize PK FROM (
    SELECT TOP (@StartRow + @PageSize) 
    PK, 
    SortColumn /*If sorting column is defferent from the PK, SortColumn must 
                 be fetched as well, otherwise just the PK is necessary */ 
    ORDER BY SortColumn /* default order – typically ASC */) 
ORDER BY SortColumn /* reversed default order – typically DESC */

SELECT ... FROM Table JOIN @Temp temp ON Table.PK = temp.PK 
ORDER BY SortColumn /* default order */

RowCount

The base logic of this method relies on the SQL SET ROWCOUNT expression to both skip the unwanted rows and fetch the desired ones:

DECLARE @Sort /* the type of the sorting column */
SET ROWCOUNT @StartRow
SELECT @Sort = SortColumn FROM Table ORDER BY SortColumn
SET ROWCOUNT @PageSize
SELECT ... FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn

SubQuery

There are 2 more methods I’ve taken into consideration, and they come from different resources. The first one is well known triple query or the SubQuery method. The most thorough approach is the one I’ve found in the following article

Server-Side Paging with SQL Server

Although you''''ll need to be subscribed, a .zip file with the SubQuery stored procedure variations is available. The Listing_04.SELECT_WITH_PAGINGStoredProcedure.txt file contains the complete generalized dynamic SQL. I used a similar generalization logic with all other stored procedures in this text. Here is the principle followed by the link to the whole procedure (I shortened the original code a bit, because a recordcount portion was unnecessary for my testing purposes).

SELECT ... FROM Table WHERE PK IN 
    (SELECT TOP @PageSize PK FROM Table WHERE PK NOT IN
        (SELECT TOP @StartRow PK FROM Table ORDER BY SortColumn)
    ORDER BY SortColumn)
ORDER BY SortColumn

Cursor

I’ve found the last method while browsing through the Google groups, you can find the original thread here. This method uses a server-side dynamic cursor. A lot of people tend to avoid cursors, they usually have poor performance because of their non-relational, sequential nature. The thing is that paging IS a sequential task and whatever method you use you have to somehow reach the starting row. In all the previous methods this is done by selecting all rows preceding the starting row plus the desired rows and then discarding all the preceding rows. Dynamic cursor has the FETCH RELATIVE option which does the “magic” jump. The base logic goes like this

DECLARE @PK /* PK Type */
DECLARE @tblPK TABLE (
    PK /* PK Type */ NOT NULL PRIMARY KEY
)

DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT @PK FROM Table ORDER BY SortColumn

OPEN PagingCursor
FETCH RELATIVE @StartRow FROM PagingCursor INTO @PK

WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
BEGIN
    INSERT @tblPK(PK) VALUES(@PK)
    FETCH NEXT FROM PagingCursor INTO @PK
    SET @PageSize = @PageSize - 1
END

CLOSE PagingCursor
DEALLOCATE PagingCursor

SELECT ... FROM Table JOIN @tblPK temp ON Table.PK = temp.PK 
ORDER BY SortColumn

Generalization of Complex Queries

As pointed out before, all the procedures are generalized with dynamic SQL, thus, in theory, they can work with any kind of complex query. Here is a complex query sample that works with Northwind database.

SELECT Customers.ContactName AS Customer, 
       Customers.Address + '''', '''' + Customers.City + '''', '''' + 
                                                Customers.Country AS Address, 
       SUM([Order Details].UnitPrice*[Order Details].Quantity) AS 
                                                          [Total money spent] 
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Cu
[1] [2]  下一页

[办公软件]如何实现Office工具栏、菜单以及菜单命令重命名  [办公软件]如何在Office文档(大)括号内输入多行文字
[办公软件]如何在office(PowerPoint,Word,Excel)中制作带圈的…  [办公软件]批量删除Office文档(word,excle,powerpoint)中的超…
[办公软件]Office(Word,Excel)密码破解软件(Office Password…  [办公软件]如何让低版本的Office也能顺利编辑2007文档
[办公软件]设置office艺术字的形状  [办公软件]如何将Office菜单设置、工具设置、宏设置等应用到…
[办公软件]在Office(word,excel)中输入各级钢筋符号的方法  [办公软件]打开Office文档就提示安装的原因及解决方案



 

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18