分页存储过程的一点心得

　　 最近参与一个对原有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秒。