通用分页存储过程

cjer0o0 楼主

分页存储过程共有四种方式可以实现，行计数、游标、升序-降序、子查询

我记得曾经有人测试过这四种方式的效率分别是 从性能最好到最差的顺序进行的——行计数、游标、升序-降序、子查询

以下是我收集的一些资料供大家参考

原文地址：http://www.codeproject.com/aspnet/PagingLarge.asp
作者：Jasmin Muharemovic
译者：Tony Qu
下载：

介绍
在Web应用程序中，对一个大数据库结果集进行分页已经是一个家喻户晓的问题了。简单的说，你不希望所有的查询数据显示在一个单独的页面中，所以带有分页的显示才是更合适的。虽然在传统的asp里这并不是一个简单的任务，但在asp.net中，DataGrid控件把这一过程简化为只有几行代码。因此，在 asp.net中，分页很简单，但是默认的DataGrid分页事件会从数据库中把所有的记录全部读出来放到asp.net web应用程序中。当你的数据在一百万以上的时候，这将引起严重的性能问题（如果你不相信，你可以在你的应用程序中执行一个查询，然后在任务管理器中查看 aspnet_wp.exe的内存消耗情况）这也就是为什么需要自定义分页行为，这样可以保证仅获得当前页需要的数据记录。

在网上有很多关于这个问题的文章和帖子，还有一些成熟的解决方案。我写这篇文章的目的不是向你展示一个可以解决一切问题的存储过程，而是出于优化已有方法，同时为你提供一个可供测试的应用程序，这样你就可以根据自己的需要进行开发。下文是一个很好的开始，它包含了很多不同的方法，并且给出了一些性能测试结果

《如何通过Recordset进行分页？》

但是我对上文的大部分内容不是很满意。第一，半数的方法是用了传统的ADO，很明显它们是为“古老”的asp而写的。剩下的一些方法就是SQL Server存储过程，并且其中的一些由于相应时间过慢而无法使用，正如你在文章最后所看到的性能结果一样，但是还是有一些引起了我的注意。

通用化
我决定对其中的三个方法进行仔细的分析，它们是临时表(TempTable)，动态SQL（DynamicSQL）和行计数 (Rowcount)。在下文中，我更愿意把第二个方法称为（升序-降序）Asc-Desc方法。我不认为动态SQL是一个好名字，因为你也可以把动态 SQL逻辑应用于另一个方法中。所有这些存储过程的通病在于，你不得不估计哪些列是你即将要排序的，而不仅仅是估计主键列（PK Columns）而已，这可能导致一系列的问题——对于每个查询来说，你需要通过分页显示，也就是说对于每不同的排序列你必须有许多不同的分页查询，这意味着你要么给每个排序列做不同的存储过程（无论使用哪种分页方法），也么你必须借助动态SQL的帮助把这个功能放在一个存储过程中。这两个方法对于性能有微小的影响，但是它增加了可维护性，特别是当你需要使用这个方法显示不同的查询。因此，在本文中我会尝试使用动态SQL对所有的存储过程进行归纳，但是由于一些原因，我们只能对实现部分的通用性，因此你还是得为复杂查询写独立的存储过程。

允许包括主键列在内的所有排序字段的第二个问题在于，如果那些列没有作适当的索引，那么这些方法一个也帮不上忙。在所有这些方法中，对于一个分页源必须先做排序，对于大数据表来说，使用非索引列排序的成本是可以忽略不计的。在这种情况下，由于相应时间过长，所有的存储过程都是无法在实际情况下使用的。（相应的时间各有不同，从几秒钟到几分钟不等，这要根据表的大小和所要获得的第一个记录而定）。其他列的索引会带来额外的不希望出现的性能问题，例如如果你每天的导入数据很多，它有可能变得很慢。

[ 本帖最后由 cjer0o0 于 2007-2-8 15:35 编辑 ]

cjer0o0 楼主

首先，我准备先来说一下临时表方法，这是一个广泛被建议使用的解决方案，我在项目中遇到过好几次了，这里有另一篇解释它如何工作的文章，还有一个如何在DataGrid中是用定制化分页（Custom Paging）的例子：

ASP.NET DataGrid分页 第二部分 – 定制化分页

这两篇文章中的方法都是通过把主键数据拷贝到临时表中，然后对主查询做join实现查询优化。下面让我们来看看这个方法的实质：

1. CREATE TABLE #Temp (
   
2.     ID int IDENTITY PRIMARY KEY,
   
3.     PK  /* here goes PK type */
   
4. )
   
5. 
   
6. INSERT INTO #Temp SELECT PK FROM Table ORDER BY SortColumn
   
7. 
   
8. SELECT   FROM Table JOIN #Temp temp ON Table.PK = temp.PK ORDER BY temp.ID
   
9. WHERE ID > @StartRow AND ID < @EndRow

复制代码

通过把所有的行拷贝到临时表中，我们可以对查询进一步的优化(SELECT TOP EndRow …)，但是关键在于最坏情况——一个包含100万记录的表就会产生一个100万条记录的临时表。考虑到这样的情况，再看看上面文章的结果，我决定在我的测试中放弃该方法

cjer0o0 楼主

这个方法在子查询中使用默认排序，在主查询中使用反向排序，原理是这样的：

1. DECLARE @temp TABLE (
   
2.     PK  /* PK Type */ NOT NULL PRIMARY
   
3. )
   
4. 
   
5. INSERT INTO @temp
   
6. SELECT TOP @PageSize PK FROM (
   
7.     SELECT TOP (@StartRow + @PageSize)
   
8.     PK,
   
9.     SortColumn /*If sorting column is defferent from the PK, SortColumn must
   
10.                  be fetched as well, otherwise just the PK is necessary */
    
11.     ORDER BY SortColumn /* default order – typically ASC */)
    
12. ORDER BY SortColumn /* reversed default order – typically DESC */
    
13. 
    
14. SELECT   FROM Table JOIN @Temp temp ON Table.PK = temp.PK
    
15. ORDER BY SortColumn /* default order */

复制代码

cjer0o0 楼主

这个方法的基本逻辑依赖于SQL中的SET ROWCOUNT表达式，这样可以跳过不必要的行并且获得需要的行记录

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

复制代码

cjer0o0 楼主

还有两个方法也是我考虑过的，他们的来源不同。第一个是众所周知的三角查询(Triple Query)或者说自查询方法，我找的一个比较透彻的方法在下面的文章中有描述

SQL Server服务器端分页

虽然你需要订阅，但是可以下载一个包含子查询存储过程定义的zip文件。列表4 SELECT_WITH_PAGINGStoredProcedure.txt文件包含一个完整的通用的动态SQL。在本文中，我也用一个类似的包含所有其他存储过程的通用逻辑。这里的原理是连接到整个过程中，我对原始代码做了一些缩减，因为recordcount在我的测试中不需要）

1. SELECT   FROM Table WHERE PK IN
   
2.     (SELECT TOP @PageSize PK FROM Table WHERE PK NOT IN
   
3.         (SELECT TOP @StartRow PK FROM Table ORDER BY SortColumn)
   
4.     ORDER BY SortColumn)
   
5. ORDER BY SortColumn

复制代码

cjer0o0 楼主

在看google讨论组的时候，我找到了最后一个方法，你可以点这里查看原始帖子。该方法是用了一个服务器端动态游标。许多人试图避免使用游标，因为游标没有关系可言，以及有序性导致其效率不高，但回过头来看，分页其实是一个有序的任务，无论你使用哪种方法，你都必须回到开始行记录。在之前的方法中，先选择所有在开始记录之前的所有行，加上需要的行记录，然后删除所有之前的行。动态游标有一个FETCH RELATIVE选项可以完成魔法般的跳转。基本的逻辑如下：

1. DECLARE @PK /* PK Type */
   
2. DECLARE @tblPK TABLE (
   
3.     PK /* PK Type */ NOT NULL PRIMARY KEY
   
4. )
   
5. 
   
6. DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
   
7. SELECT @PK FROM Table ORDER BY SortColumn
   
8. 
   
9. OPEN PagingCursor
   
10. FETCH RELATIVE @StartRow FROM PagingCursor INTO @PK
    
11. 
    
12. WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
    
13. BEGIN
    
14.     INSERT @tblPK(PK) VALUES(@PK)
    
15.     FETCH NEXT FROM PagingCursor INTO @PK
    
16.     SET @PageSize = @PageSize - 1
    
17. END
    
18. 
    
19. CLOSE PagingCursor
    
20. DEALLOCATE PagingCursor
    
21. 
    
22. SELECT   FROM Table JOIN @tblPK temp ON Table.PK = temp.PK
    
23. ORDER BY SortColumn

复制代码

cjer0o0 楼主

我在之前指出，所有的存储过程都是用动态SQL实现通用性的，因此，理论上它们可以用任何种类的复杂查询。下面有一个基于Northwind数据库的复杂查询例子。

1. SELECT Customers.ContactName AS Customer,
   
2.        Customers.Address + ', ' + Customers.City + ', ' +
   
3.                                                 Customers.Country AS Address,
   
4.        SUM([Order Details].UnitPrice*[Order Details].Quantity) AS
   
5.                                                           [Total money spent]
   
6. FROM Customers
   
7. INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
   
8. INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
   
9. WHERE Customers.Country <> 'USA' AND Customers.Country <> 'Mexico'
   
10. GROUP BY Customers.ContactName, Customers.Address, Customers.City,
    
11.          Customers.Country
    
12. HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
    
13. ORDER BY Customer DESC, Address DESC
    
14. 返回第二个页面的分页存储调用如下：
    
15. EXEC ProcedureName
    
16. /* Tables */
    
17. 'Customers
    
18. INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
    
19. INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID',
    
20. /* PK */
    
21. 'Customers.CustomerID',
    
22. /* ORDER BY */
    
23. 'Customers.ContactName DESC, Customers.Address DESC',
    
24. /* PageNumber */
    
25. 2,
    
26. /* Page Size */
    
27. 10,
    
28. /* Fields */
    
29. 'Customers.ContactName AS Customer,
    
30. Customers.Address + '', '' + Customers.City + '', '' + Customers.Country
    
31.                                                                   AS Address,
    
32. SUM([Order Details].UnitPrice*[Order Details].Quantity) AS [Total money spent]',
    
33. /* Filter */
    
34. 'Customers.Country <> ''USA'' AND Customers.Country <> ''Mexico''',
    
35. /*Group By*/
    
36. 'Customers.CustomerID, Customers.ContactName, Customers.Address,
    
37. Customers.City, Customers.Country
    
38. HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000'

复制代码

值得注意的是，在原始查询中在ORDER BY语句中使用了别名，但你最好不要在分页存储过程中这么做，因为这样的话跳过开始记录之前的行是很消耗时间的。其实有很多种方法可以用于实现，但原则是不要在一开始把所有的字段包括进去，而仅仅是包括主键列（等同于RowCount方法中的排序列），这样可以加快任务完成速度。只有在请求页中，才获得所有需要的字段。并且，在最终查询中不存在字段别名，在跳行查询中，必须提前使用索引列。

行计数(RowCount)存储过程有一个另外的问题，要实现通用化，在ORDER BY语句中只允许有一个列，这也是升序-降序方法和游标方法的问题，虽然他们可以对几个列进行排序，但是必须保证主键中只有一个字段。我猜如果用更多的动态SQL是可以解决这个问题的，但是在我看来这不是很值得。虽然这样的情况很有可能发生，但他们发生的频率不是很高。通常你可以用上面的原理也独立的分页存储过程。

cjer0o0 楼主

在测试中，我使用了四种方法，如果你有更好的方法的话，我很有兴趣知道。不管如何，我需要对这些方法进行比较，并且评估它们的性能。首先我的第一个想法就是写一个asp.net包含分页DataGrid的测试应用程序，然后测试页面结果。当然，这无法反映存储过程的真实响应时间，所以控制台应用程序显得更加适合。我还加入了一个Web应用程序，但不是为了性能测试，而是一个关于DataGrid自定义分页和存储过程一起工作的例子。这两个应用程序都可以在 Paging Test Solution中找到。

在测试中，我使用了一个自动生成得大数据表，大概插入了500000条数据。如果你没有一张这样的表来做实验，你可以点击这里下载一段用于生成数据的表设计和存储过程脚本。我没有使用一个自增的主键列，而是用一个唯一识别码来识别记录的。如果我使用上面提到的脚本，你可能会考虑在生成表之后添加一个自增列，这些自增数据会根据主键进行数字排序，这也意味着你打算用一个带有主键排序的分页存储过程来获得当前页的数据。

为了实现性能测试，我是通过一个循环多次调用一个特定的存储过程，然后计算平均相应时间来实现的。考虑到缓存的原因，为了更准确地建模实际情况——同一页面对于一个存储过程的多次调用获得数据的时间通常是不适合用来做评估的，因此，我们在调用同一个存储过程时，每一次调用所请求的页码应该是随机的。当然，我们必须假设页的数量是固定的，10-20页，不同页码的数据可能被获取很多次，但是是随机获取的。

有一点我们很容易注意到，相应时间是由要获取的页数据相对于结果集开始的位置的距离决定的，越是远离结果集的开始位置，就有越多的记录要跳过，这也是我为什么不把前20也包括进我的随机序列的原因。作为替换，我会使用2的n次方个页面，循环的大小是需要的不同页的数量*1000，所以，每个页面几乎都被获取了1000次（由于随机原因，肯定会有所偏差）

cjer0o0 楼主

这里有我的测试结果：








结论
测试是按照从性能最好到最差的顺序进行的——行计数、游标、升序-降序、子查询。有一件事很有趣，通常人们很少会访问前五页之后的页面，因此子查询方法可能在这种情况下满足你的需要，这得看你的结果集的大小和对于远距离（distant）页面的发生频率预测，你也很有可能使用这些方法的组合模式。如果是我，在任何情况下，我都更喜欢用行计数方法，它运行起来十分不错，即使对于第一页也是如此，这里的“任何情况”代表了一些很难实现通用化的情况，在这种情况下，我会使用游标。（对于前两页我可能使用子查询方法，之后再用游标方法）

cjer0o0 楼主

该方法也就是上头所提到的行计数的方式，在系统中使用发现过很多次问题，这是最终版本应该不会再有什么算法之类的问题出现了

这个存储过程的原理就是“譬如现在要第5页数据，每页是16行，那么现在要的数据就是在前80行，并且不在原来的64行内的”

Create  Proc proc_PageRecordset
@queryStr nvarchar(2000),  --查询语句，用来获取要显示的数据必须满足的要求@keyField nvarchar (72), --主键
@pageSize int,  --每页的行数@pageNumber int, --第几页
@filter varChar(2000)='', --过滤，where后头的语句
@order varChar(200)='' --排序方式AS
BEGIN
DECLARE @sqlText AS nvarchar(4000)
DECLARE @sqlTable AS nvarchar(4000)
DECLARE @sqlText_PageCount  AS nvarchar(4000)

set @filter=replace(@filter,'#','''')

--这是最后一次所发现的bug，因为在排序中必须确定一个排出来的位置一样的table，不能让a在这次排第2，那次又排第5
if CharIndex(@keyField,@order)=0
Begin
Set @order=@order+','+@keyField
End

if (Rtrim(@filter)='')
begin
SET @sqlTable = 'SELECT TOP ' + CAST((@pageNumber + 1) * @pageSize AS varchar(30)) + ' ' + @queryStr + ' ' + @order
SET @sqlText_PageCount = 'Select Count(*) from (select ' +@queryStr+ ' ) as Table_temp'
end
else
begin
SET @sqlTable = 'SELECT TOP ' + CAST((@pageNumber + 1) * @pageSize AS varchar(30)) + ' ' + @queryStr + ' and ' + @filter + @order
SET @sqlText_PageCount = 'Select Count(*) AS MyCount from (select ' +@queryStr+ ' and ' + @filter +' ) as Table_temp'
end
--譬如现在要第5页数据，每页是16行，那么现在要的数据就是在前80行，并且不在原来的64行内的
SET @sqlText =
'SELECT TOP ' + CAST(@pageSize AS varchar(30)) + ' * ' +
'FROM (' + @sqlTable + ') AS tableA ' +
'WHERE ' + @keyField + ' NOT IN(SELECT TOP ' +
CAST(@pageNumber * @pageSize AS varchar(30)) + ' ' + @keyField +
' FROM (' + @sqlTable + ') AS tableB)'

EXEC (@sqlText)

exec (@sqlText_PageCount)

END

GO

farOasis

嗯，不错

听说2005增加了rownum，分页应该会很方便

cjer0o0 楼主

嗯，２００５的分页可以直接用ROW_NUMBER() ，传入页数和每页的行数（或者第几行到第几行）就可以了

ｅｇ：
--按Freight从小到大排序，求２页，每页１５行  
select * from(  
    select OrderId, Freight, ROW_NUMBER() OVER(order by Freight) as row from Orders  
) a  
where row between 1*15+1  and 2*15

曾经的我

MM好厉害，正在学习这些东西~领教了！

帅青蛙

我总觉得select top xx .... from xxxx where xx not in(select top xx*rows xx from xxxx ....)的效率也不高。
如果有多对多的情况下，这个瓶颈就出来了。

PS: 我个人也是用这种方法。

yjmyzz

原帖由 farOasis 于 2007-2-8 19:18 发表
嗯，不错

听说2005增加了rownum，分页应该会很方便

专用于Sql2005的分页存储过程(利用RowNum，2005环境下优于其它任何方法)
http://blog.sqlsky.com/article.asp?id=332

原理及测试如下：
http://blog.sqlsky.com/article.asp?id=331

[ 本帖最后由 yjmyzz 于 2007-2-9 10:57 编辑 ]