SQLServer分页存储过程的一点心得

　　 最近参与一个对原有MS SQLSERVER2000+ASP系统升级到.NET的开发项目。其中，针对某一个数据记录较多的表的查询用的比较多，而且该查询的条件组合比较复杂，包括分页，按特定字段排序，按特定条件查询。

　　参考了netkillerbaseSQL Server 存储过程的分页，开始决定采用效率最高的“方案二”，即通过ID标识来比较大小，从而快速检索出所需的记录。

　　为了方便读者，我在这里简单列出前面列出的参考文章中的3种分页查询存储过程的核心T-SQL语句：

方案一：

SELECT   TOP  页大小  *  
FROM  TestTable 
WHERE  (ID  NOT   IN  
( SELECT   TOP  页大小 * 页数 id 
FROM  表 
ORDER   BY  id)) 
ORDER   BY  ID 

方案二：

SELECT   TOP  页大小  *  
FROM  TestTable 
WHERE  (ID  >  
( SELECT   MAX (id) 
FROM  ( SELECT   TOP  页大小 * 页数 id 
FROM  表 
ORDER   BY  id)  AS  T)) 
ORDER   BY  ID 

方案三：(利用SQL的游标存储过程分页)

create   procedure  XiaoZhengGe 
@sqlstr   nvarchar ( 4000 ),  -- 查询字符串 
@currentpage   int ,  -- 第N页 
@pagesize   int   -- 每页行数 
as  
set  nocount  on  
declare   @P1   int ,  -- P1是游标的id 
@rowcount   int  
exec  sp_cursoropen  @P1  output, @sqlstr , @scrollopt = 1 , @ccopt = 1 , @rowcount = @rowcount  output 
select   ceiling ( 1.0 * @rowcount / @pagesize )  as  总页数 -- ,@rowcount as 总行数,@currentpage as 当前页 
set   @currentpage = ( @currentpage - 1 ) * @pagesize + 1  
exec  sp_cursorfetch  @P1 , 16 , @currentpage , @pagesize  
exec  sp_cursorclose  @P1  
set  nocount  off  

上文作者使用查询分析器比较过3种方案，结论如下：
分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句
分页方案一：(利用Not In和SELECT TOP分页) 效率次之，需要拼接SQL语句
分页方案三：(利用SQL的游标存储过程分页) 效率最差，但是最为通用

　　按照“方案二”写好查询存储过程后，测试中发现按照某些字段排序时，会出现记录遗漏的情况。经过分析表中的记录发现，原来是因为该字段内的值有重复，即用来排序的字段不能作为标识来比较大小。

　　找到原因后，对方案二和方案一进行了一个整合。先使用方案二中的排序字段的大小比较，来初步筛选符合条件的记录，然后再使用方案一中的ID标识来验证找到记录是否合法。最后得到的T-SQL语句类似如下：

SELECT   TOP  页大小  *  
FROM  TestTable 
WHERE  (ordercol  >=   SELECT   MAX (ordercol) 
FROM  ( SELECT   TOP  页大小 * 页数 ordercol 
FROM  TestTable 
ORDER   BY  ordercol ))   and  (ID  NOT   IN
( SELECT   TOP  页大小 * 页数 id 
FROM  TestTable 
ORDER   BY  ordercol)) 
ORDER   BY  ordercol 

　　这种方式继承了“方案一”的缺点，即在记录数相当大，而且页码靠后时，(SELECT TOP 页大小*页数 id
FROM TestTable ORDER BY ordercol)所得到的记录集会消耗相当大的内存。但是，于此同时，前面先进行的比较判断(ordercol >= SELECT MAX(ordercol) FROM (SELECT TOP 页大小*页数 ordercol FROM TestTable
ORDER BY ordercol ))所得到的记录数量却并不大，加上比较查询的效率远远高于not in，所以最终的执行效率还是可以让人接受的。

　　实际项目所使用的表中有1万多条记录，采用该分页存储过程查询倒数前5页，查询执行时间低于1秒。而原来的asp程序使用recordset的move方法，查询执行时间超过2秒。