sql server 2000 大型结果集的分页
- 下载 PagingTest 解决方案 (C#) - 55.8 Kb
- 下载 Paging_Procedures SQL 脚本 - 2.48 Kb
- 下载 Paging_LargeTable SQL 脚本 - 0.6 Kb
介绍
Web 应用程序中大型数据库结果集的分页是一个众所周知的问题。简而言之，您不希望查询的所有结果都显示在单个网页上，因此某种分页显示更合适。虽然在旧的 ASP 中这不是一件容易的事，DataGrid但 ASP.NET 中的控件将其简化为几行代码。因此，分页在 ASP.NET 中很容易，但DataGrid是您查询的所有结果记录都将从 SQL 服务器获取到 ASP.NET 应用程序。如果您的查询返回一百万条记录，这将导致一些严重的性能问题（如果您需要令人信服，请尝试在您的 Web 应用程序中执行此类查询，并在任务管理器中查看 aspnet_wp.exe 的内存消耗）。这就是为什么需要自定义分页解决方案的原因，其中所需的行为是仅从当前页面获取行。
有很多关于这个问题的文章和帖子以及几个提出的解决方案。我在这里的目标不是向您展示一个惊人的解决所有问题的程序，而是优化所有现有方法并为您提供测试应用程序，以便您可以自己进行评估。这是一篇很好的起点文章，它描述了许多不同的方法并提供了一些性能测试结果：
如何对记录集进行分页？
我对他们中的大多数都不满意。首先，一半的方法使用旧的 ADO，并且明显是为“旧”的 ASP 编写的。其余方法是 SQL Server 存储过程。从页面底部作者的性能结果中可以看出，其中一些产生的响应时间很差，但有几个引起了我的注意。
概括
我决定仔细研究的三种方法是作者调用的方法TempTable，DynamicSQL和Rowcount。我将Asc-Desc在本文的其余部分将第二种方法称为方法。我不认为DynamicSQL是个好名字，因为您也可以将动态 SQL 逻辑应用于其他方法。所有这些存储过程的普遍问题是您必须评估您将允许对哪些列进行排序，而这可能不仅仅是 PK 列。这导致了一系列新问题——对于您希望通过分页显示的每个查询，您必须拥有与不同排序列一样多的不同分页查询。这意味着您将为每个排序列使用不同的存储过程（不管应用的分页方法），或者您将尝试借助动态 SQL 将其推广到仅一个存储过程。这对性能有轻微影响，但如果您需要使用此方法显示许多不同的查询，则会提高可维护性。因此，
允许除 PK 列之外的其他排序列的第二个问题是，如果这些列未以某种方式建立索引，则这些方法都无济于事。在所有这些中，必须首先对分页源进行排序，并且使用非索引列排序的成本对于大型表来说是巨大的。响应时间如此之长，以至于在这种情况下所有过程实际上都无法使用（响应从几秒到几分钟不等，具体取决于表的大小和要获取的起始记录）。其他列的索引会带来更多的性能问题，并且可能是不可取的，例如，在您有大量每日导入的情况下，它可能会显着减慢您的速度。
临时表
我要评论的第一个是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 <BR>WHERE ID > @StartRow AND ID < @EndRow
该方法可以通过将行复制到临时表来进一步优化，直到到达最后的分页行SELECT TOP EndRow...（临时表也是如此。考虑到所有这些并查看了上面文章中的结果，我决定从我的测试中放弃这种方法。
升-降
此方法在子查询中使用默认排序，然后应用反向排序。原理是这样的
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 <BR> 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 <BR>ORDER BY SortColumn /* default order */
完整代码 – Paging_Asc_Desc行数
此方法的基本逻辑依赖于 SQLSET 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方法。最彻底的方法是我在以下文章
使用 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 群组时找到了最后一种方法，您可以在此处找到原始线程。此方法使用服务器端动态游标。很多人倾向于避免使用游标，由于它们的非关系性、顺序性，它们的性能通常很差。问题是分页是一个顺序任务，无论你使用什么方法，你都必须以某种方式到达起始行。在所有先前的方法中，这是通过选择起始行之前的所有行加上所需行然后丢弃所有前面的行来完成的。动态光标具有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 <BR>ORDER BY SortColumn
完整代码 – Paging_Cursor复杂查询的泛化
正如前面所指出的，所有的过程都是用动态 SQL 概括的，因此，理论上它们可以处理任何类型的复杂查询。这是一个与Northwind数据库一起使用的复杂查询示例。
SELECT Customers.ContactName AS Customer,
Customers.Address + ', ' + Customers.City + ', ' + <BR> Customers.Country AS Address,
SUM([Order Details].UnitPrice*[Order Details].Quantity) AS <BR> [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, <BR> 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 <BR> 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, <BR> Customers.City, Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000'
请注意，在原始查询中，ORDER BY子句中使用了别名。您不能在分页过程中这样做，因为所有这些过程中最耗时的任务是跳过起始行之前的行。这可以通过多种方式完成，但原则是首先不获取所有必需的字段，而只获取 PK 列（在RowCount方法的情况下为排序列），这加快了这项任务。仅针对属于所请求页面的行获取所有必填字段。因此，字段别名在最终查询之前不存在，并且必须更早地使用排序列（在行跳过查询中）。
该过程还有另一个问题，它被概括为仅使用子句RowCount中的一列。和方法ORDER BY也是如此，尽管它们可以与多个排序列一起使用，但要求 PK 中只包含一列。我想这可以通过更动态的 SQL 来解决，但我认为这不值得大惊小怪。尽管这些情况极有可能发生，但并不常见。即使是这样，您也始终可以按照上述原则编写单独的分页过程。Asc-DescCursor
性能测试
我在测试中使用了这 4 种方法，如果您有更好的方法，我很高兴知道。尽管如此，我还是想比较这些方法并衡量它们的性能。第一个想法是使用分页 DataGrid 编写一个 ASP.NET 测试应用程序，然后测量页面响应。尽管如此，这并不能反映存储过程的真实响应时间，因此控制台应用程序似乎更合适。我还包含了一个 Web 应用程序，不是用于性能测试，而是作为 DataGrid 自定义分页如何与这些存储过程一起使用的示例。它们都包含在PagingTest 解决方案中。
我使用自动生成的大表进行测试，并在其中插入了大约 500 000 条记录。如果您没有要试验的大表，您可以在此处下载表设计脚本和用于数据生成的存储过程。我不想要我的 PK 的身份列，uniqueidentifier而是使用了。如果您将使用此脚本，则可以考虑在生成表后添加标识。它将添加按 PK 排序的数字，并且当您使用 PK 排序调用分页过程时，您将获得正确页面的指示。
性能测试背后的想法是通过循环多次调用特定的存储过程，然后测量平均响应时间。此外，为了消除缓存偏差并更准确地模拟真实情况——多次调用存储过程并每次获取相同的页面似乎是不合适的。因此，需要具有一组不同页码的相同存储过程的随机序列。当然，一组不同的页码假设有固定数量的页面（10 - 20），其中每个页面将被多次获取，但顺序是随机的。
不难注意到响应时间取决于获取的页面与结果集开头的距离。起始记录越远，需要跳过的记录越多。这就是我没有在随机序列中包含前 20 页的原因。相反，我使用了 2 N页的集合。循环设置为（不同页数）*1000。因此，每个页面都被提取了大约 1000 次（或多或少是因为随机分布）。
结果
这是我得到的结果 - Paging_Results (MS Excell 文件)
结论
方法按以下顺序执行，从最好的一个开始 - RowCount、Cursor和。下面部分的行为特别有趣，因为在许多实际情况下，您很少会浏览超过前五页的内容，因此该方法可能会满足您在这些情况下的需求。这完全取决于您的结果集的大小以及对远程页面的获取频率的预测。您也可以使用这些方法的组合。至于我自己，我决定尽可能使用这种方法。它非常好，即使是第一页。“尽可能”部分代表难以概括此方法的某些情况，然后我将使用（可能与Asc-DescSubquerySubqueryRowCountCursorSubQuery前几页）。
2004-05-05 更新
我写这篇文章的主要原因是来自广大编程社区的反馈。几周后，我将开始一个新项目的工作。初步分析表明，将涉及几个非常大的表。这些表将用于许多复杂的连接查询，它们的结果将显示在 ASP.NET 应用程序中（启用排序和分页）。这就是为什么我花了一些时间研究和追求最好的分页方法。我感兴趣的不仅仅是性能，还有可用性和可维护性。
现在投入的时间已经开始得到回报。您可以在下面找到 C. v. Berkel 的帖子（非常感谢），他在其中发现了该RowCount方法的缺陷。如果排序列不是唯一的，它将无法正常工作。该RowCount方法在我的测试中表现最好，但现在我正在认真考虑根本不使用它。在大多数情况下，排序列（除了 PK）不会是唯一的。这给我留下Cursor了最快且适用于大多数情况的方法。它可以与SubQuery前几页的方法结合使用，也可以与RowCount唯一排序列的方法结合使用。
可能值得一提的另一件事是该Asc-Desc方法也存在一个小缺陷。它总是返回PageSize最后一页的记录数，而不是实际数（可能低于PageSize）。可以计算出正确的数字，但由于我不打算使用此过程（因为它的执行方式），我不想进一步改进它。
执照
本文没有附加明确的许可，但可能在文章文本或下载文件本身中包含使用条款。如有疑问，请通过下面的讨论区联系作者。
浙公网安备 33010602011771号