65.9K
CodeProject 正在变化。 阅读更多。
Home

ASP.NET 中大型结果集的 Paging

Jasmin Muharemovic
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.92/5 (157投票s)

2004 年 5 月 4 日

11分钟阅读

viewsIcon

1075349

downloadIcon

26601

本文介绍用于 ASP.NET 中大型结果集分页的 MS SQL Server 2000 存储过程的优化和性能测试。

引言

在 Web 应用程序中分页大型数据库结果集是一个众所周知的问题。简而言之，您不想将查询的所有结果显示在单个 Web 页面上，因此某种分页显示更为合适。虽然在旧版 ASP 中这项任务并不容易，但 ASP.NET 中的 DataGrid 控件仅用几行代码即可简化此操作。因此，在 ASP.NET 中进行分页很容易，但是 DataGrid 的默认行为是从 SQL Server 将查询的所有结果记录获取到 ASP.NET 应用程序中。如果查询返回一百万条记录，这将导致严重的性能问题（如果您需要说服，请尝试在 Web 应用程序中执行此类查询，并在任务管理器中查看 aspnet_wp.exe 的内存消耗）。这就是为什么需要自定义分页解决方案，其期望行为是仅获取当前页的行。

关于这个问题和几种提出的解决方案，有大量的文章和帖子。我的目标不是向您展示一种惊人的、万能的解决方案，而是优化所有现有方法，并为您提供一个测试应用程序，以便您可以自己进行评估。这是一篇很好的入门文章，其中描述了许多不同的方法，并提供了性能测试结果。

我如何分页遍历记录集？

我对其中的大多数都不满意。首先，有一半的方法使用旧的 ADO，并且显然是为“旧”ASP 编写的。其余的方法是 SQL Server 存储过程。其中一些响应时间很差，正如您从页面底部的作者性能结果中可以看到的，但有几个引起了我的注意。

泛化

我决定深入研究的三种方法是作者称之为 TempTable、DynamicSQL 和 Rowcount 的方法。在本文的其余部分，我将把第二种方法称为 Asc-Desc 方法。我认为 DynamicSQL 这个名字并不好，因为您可以将动态 SQL 逻辑应用于其他方法。所有这些存储过程的普遍问题是，您必须评估允许对哪些列进行排序，而这可能不仅仅是主键列。这会导致新的一系列问题——您希望通过分页显示的每个查询都必须有尽可能多的不同的分页查询，具体取决于不同的排序列。这意味着您将为每个排序列拥有一个不同的存储过程（无论应用何种分页方法），或者您将尝试借助动态 SQL 将其泛化为一个存储过程。这会产生轻微的性能影响，但如果需要在此方法中使用许多不同的查询，则可以提高可维护性。因此，我将在本文中尝试使用动态 SQL 泛化所有存储过程，但在某些情况下，只能达到一定程度的泛化，因此您仍然需要为某些复杂查询编写单独的存储过程。

允许除了主键列以外的其他排序列的第二个问题是，如果这些列没有以某种方式索引，那么这些方法都无济于事。在所有这些方法中，必须首先对分页源进行排序，而按非索引列排序的成本对于大型表来说是巨大的。响应时间如此之高，以至于在这种情况下所有过程实际上都无法使用（响应时间从几秒到几分钟不等，具体取决于表的大小和正在获取的起始记录）。对其他列进行索引会带来更多性能问题，并且可能是不希望的，例如，它可能会显著降低您在大量每日导入情况下的速度。

TempTable

我首先要评论的是 TempTable 方法。这实际上是一个广泛提出的解决方案，我遇到过几次。这是另一篇文章，描述了它以及解释和如何使用自定义分页与 DataGrid 的示例。

ASP.NET DataGrid Paging Part 2 - Custom Paging

两篇文章中的方法都可以通过仅将主键数据复制到临时表，然后与主查询进行联接来优化。因此，此方法的核心将是以下内容。

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
通过将行复制到临时表直到达到最后一个分页行（SELECT TOP EndRow...），可以进一步优化该方法，但在最坏的情况下——对于一个拥有一百万条记录的表，您最终也会在临时表中得到一百万条记录。考虑到所有这些，并查看上述文章中的结果，我决定从我的测试中排除此方法。

Asc-Desc

此方法使用子查询中的默认排序，然后应用反向排序。原理如下。

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 */
完整代码 – Paging_Asc_Desc

RowCount

此方法的基本逻辑依赖于 SQL SET ROWCOUNT 表达式来跳过不需要的行并获取所需的行。

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
完整代码 – Paging_RowCount

SubQuery

我考虑了另外两种方法，它们来自不同的资源。第一个是众所周知的三重查询或 SubQuery 方法。最全面的方法是我在以下文章中找到的那种。

SQL Server 的服务器端分页

虽然您需要订阅，但可以获得包含 SubQuery 存储过程变体的 .zip 文件。Listing_04.SELECT_WITH_PAGINGStoredProcedure.txt 文件包含完整的通用动态 SQL。我在本文中对所有其他存储过程使用了类似的泛化逻辑。这是原理，后面是指向整个过程的链接（我稍微缩短了原始代码，因为记录计数部分对我测试目的而言是不必要的）。

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
完整代码 – Paging_SubQuery

光标

我在浏览 Google Groups 时发现了最后一种方法，您可以在 此处 找到原始帖子。此方法使用服务器端动态游标。许多人倾向于避免使用游标，它们通常由于其非关系性和顺序性而性能不佳。关键是分页本身就是一项顺序任务，无论您使用哪种方法，您都必须以某种方式到达起始行。在所有先前的方法中，这是通过选择起始行之前的​​所有行加上所需的行，然后丢弃所有先前的行来完成的。动态游标具有 FETCH RELATIVE 选项，可以执行“魔法”跳转。基本逻辑如下。

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
完整代码 – Paging_Cursor

复杂查询的泛化

如前所述，所有过程都通过动态 SQL 进行泛化，因此，理论上，它们可以处理任何类型的复杂查询。这是一个适用于 Northwind 数据库的复杂查询示例。

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 Customers.Country <> 'USA' AND Customers.Country <> 'Mexico'
GROUP BY Customers.ContactName, Customers.Address, Customers.City, 
         Customers.Country 
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
ORDER BY Customer DESC, Address DESC
返回第二页的分页存储过程调用如下所示。
EXEC ProcedureName
/* Tables */
'Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID',
/* PK */
'Customers.CustomerID',
/* ORDER BY */
'Customers.ContactName DESC, Customers.Address DESC',
/* PageNumber */
2,
/* Page Size */
10,
/* Fields */
'Customers.ContactName AS Customer,
Customers.Address + '', '' + Customers.City + '', '' + Customers.Country 
                                                                  AS Address, 
SUM([Order Details].UnitPrice*[Order Details].Quantity) AS [Total money spent]',
/* Filter */
'Customers.Country <> ''USA'' AND Customers.Country <> ''Mexico''',
/*Group By*/
'Customers.CustomerID, Customers.ContactName, Customers.Address, 
 Customers.City, Customers.Country 
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000'

请注意，在原始查询中，别名在 ORDER BY 子句中使用。您不能在分页过程中这样做，因为所有这些过程中最耗时的任务是跳过起始行之前的​​行。这是通过各种方式完成的，但原则是首先不获取所有必需的字段，而只获取主键列（在 RowCount 方法的情况下是排序列），这可以加快此任务的速度。所有必需的字段仅为属于所请求页面的行获取。因此，字段别名直到最终查询才存在，并且排序列必须更早使用（在行跳过查询中）。

RowCount 过程还有另一个问题，它被泛化为只能在 ORDER BY 子句中使用一列。Asc-Desc 和 Cursor 方法也是如此，尽管它们可以处理多个排序列，但要求主键中仅包含一列。我认为这可以通过更多的动态 SQL 来解决，但我认为不值得费心。尽管这些情况很有可能发生，但并不那么频繁。即使发生，您也可以始终根据上述原理编写单独的分页过程。

性能测试

我在测试中使用了这 4 种方法，如果您有更好的方法，我很乐意知道。尽管如此，我想比较这些方法并测量它们的性能。最初的想法是编写一个带有分页 DataGrid 的 ASP.NET 测试应用程序，然后测量页面响应。然而，这并不能反映存储过程的真实响应时间，因此控制台应用程序似乎更合适。我还包含了一个 Web 应用程序，不是用于性能测试，而是作为一个如何将 DataGrid 自定义分页与这些存储过程结合使用的示例。它们都包含在 PagingTest 解决方案 中。

我使用了自动生成的表用于我的测试，并在其中插入了大约 500,000 条记录。如果您没有大型表可供实验，您可以下载用于表设计和数据生成存储过程的脚本 此处。我不想为我的主键使用身份列，而是使用了 uniqueidentifier。如果您使用此脚本，您可能需要考虑在生成表后添加一个身份列。它将添加按主键排序的数字，并且当您使用主键排序调用分页过程时，您将有一个指示已正确获取页面的提示。

性能测试背后的想法是通过循环多次调用特定的存储过程，然后测量平均响应时间。此外，为了消除缓存偏差并更准确地模拟真实情况——多次调用具有相同页面获取的同一存储过程是不合适的。因此，需要对同一存储过程进行一系列不同的页面号的随机序列调用。当然，一组不同的页面号假定有固定的页面数（10-20 页），其中每个页面将被多次获取，但以随机顺序。

不难发现响应时间取决于所获取页面与结果集开头之间的距离。起始记录越远，需要跳过的记录越多。这就是我未将前 20 页包含在我的随机序列中的原因。相反，我使用了一组 2N 页。循环设置为 (不同页面数)*1000。因此，每个页面都被获取了大约 1000 次（由于随机分布，多或少）。

结果

这是我得到的结果 - Paging_Results (MS Excell 文件)




结论

这些方法的性能顺序如下，从最佳方法开始 - RowCount、Cursor、Asc-Desc 和 Subquery。下半部分的行为尤其有趣，因为在许多实际情况中，您很少会浏览前五页以外的页面，因此 Subquery 方法在这些情况下可能满足您的需求。这完全取决于您的结果集大小以及对远处页面获取频率的预测。您也可以使用方法的组合。就我个人而言，我决定尽可能使用 RowCount 方法。即使对于第一页，它的表现也相当不错。“尽可能”部分适用于某些难以泛化此方法的情况，然后我将使用 Cursor（可能与 SubQuery 结合使用，用于前几页）。

更新 2004-05-05

我写这篇文章的主要原因是我从庞大的编程社区获得了反馈。几周后，我将开始一个新项目。初步分析表明，其中将涉及几个非常大的表。这些表将在许多复杂的联接查询中使用，并且它们的结果将在 ASP.NET 应用程序中显示（启用了排序和分页）。这就是为什么我投入了一些时间进行研究并追求最佳分页方法。我感兴趣的不仅仅是性能，还有可用性和可维护性。

现在，投入的时间已经开始得到回报。您可以在下面找到 C. v. Berkel 的帖子（非常感谢），他在其中发现了 RowCount 方法的一个缺陷。如果排序列不是唯一的，它将无法正确工作。RowCount 方法在我的测试中表现最佳，但现在我正在认真考虑完全不使用它。在大多数情况下，排序列（主键以外）不会是唯一的。这使我以 Cursor 方法作为最快且适用于大多数情况的方法。它可以与 SubQuery 方法结合使用（用于前几页），可能还与 RowCount 方法结合使用（用于唯一排序列）。

另一件值得一提的事情是 Asc-Desc 方法也存在一个微小的缺陷。它总是为最后一页返回 PageSize 条记录，而不是实际数量（可能低于 PageSize）。可以计算出正确的数量，但由于我打算不使用此过程（因为它表现不佳），所以我不想进一步改进它。

© . All rights reserved.