几种常用存储过程分页方法
我们先给出几种主要的分页方法和核心语句,然后直接给出结论,有兴趣的读者可以看看后面的数据
几种常用存储过程分页方法
TopN方法
select Top(@PageSize) from TableName where ID Not IN
(Select Top ((@PageIndex-1)*@PageSize) ID from Table Name where .... order by ... )
where .... order by ...
临时表
declare @indextable table(id int identity(1,1),nid int,PostUserName nvarchar(50))
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize--下限
set @PageUpperBound=@PageLowerBound+@pagesize--上限
set rowcount @PageUpperBound
insert into @indextable(nid,PostUserName) select ReplyID,PostUserName from TableName order by ......
select * from TableName p,@indextable t where p.ID=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
CTE--2005新语法,类似临时表,但是生命周期稍微不同,这里只是他的一个运用
with cte_temp--定义零时表,PageIndex是一个计算字段,储存了搜索结果的页号
As (ceiling((Row_Number() over(order by .... )-1)/@pagesize as int) as PageIndex,* from TableName where.....)
select * from cte_temp where pageindex=@pageindex-1;
结论:
TopN在小页数下最快,如果在10页以下,可以考虑用它,CTE和临时表时间很稳定,CTE消耗的时间比临时表多,但是不会引起tempdb的暴涨和IO增加
性能比较
试验环境:win2003server,Sqlserver2005,库大小2,567,245行,没有where子句,试验时每页大小50,页码作为变量
取0,3,10,31,100,316,1000,3162...页,也就是10的指数,试验结果如下
| 页数 | TopN | CTE | 临时表 | 临时表 | 老论坛存储过程 | CTE改进 |
| 1 | 3 | 12 | 10 | 101 | 457 | 7302 |
| 3 | 15 | 7 | 79 | 5524 | 464 | 7191 |
| 10 | 127 | 5504 | 88 | 3801 | 464 | 6116 |
| 32 | 588 | 9672 | 122 | 3601 | 976 | 7602 |
| 100 | 4680 | 9738 | 166 | 4235 | 486 | 7151 |
| 316 | 45271 | 9764 | 323 | 3867 | 522 | 7255 |
| 1000 | 无法计算 | 9806 | 869 | 2578 | 635 | 8948 |
| 3162 | 无法计算 | 9822 | 2485 | 4110 | 12460 | 8210 |
| 10000 | 无法计算 | 9754 | 7812 | 11926 | 14250 | 7359 |
| 31623 | 无法计算 | 9775 | 18729 | 33218 | 15249 | 7511 |
| 100000 | 无法计算 | 无法计算 | 31538 | 55569 | 17139 | 6124 |
数据解释和分析
临时表分为有没有缓存两种时间,CTE就是上面的方法,CTE改进只是把选入CTE临时表的列数减少了,只选取了页号和主键,Null表示时间无法计算(时间太长),数据单位是毫秒.
从上面的数据可以看到,TopN在前32页都是有优势的,但是页数增大后,性能降低很快,CTE改进比CTE有所进步,平均进步两秒左右,但是还是比临时表慢,但是考虑临时表会增大日志文件的大小,引起大量IO,CTE也就有他自己的优势,公司现在正在使用的存储过程效率不错,但是在页码靠后的情况下性能会降低
一直以来, 大家热衷于存储过程分页谁分得快, 但是我们搜索一下"通用分页存储过程", 就会发现这个有问题的方法流传得颇广
对于通用查询的解决方案, 两层TOP
SELECT * FROM
(
SELECT TOP(PageSize) * FROM
(
SELECT TOP (PageSize * (PageIndex + 1)) *
FROM table
ORDER BY field DESC
)
ORDER BY field ASC
)
ORDER BY field DESC
这个号称"掐头去尾"的分页方法, 其实有很多问题
首先就是, 在最后一页时, 如果符合此页的数据不足PageSize时, 会取到前一页的部分数据, 因为你总是取到了PageSize条记录呢, 解决办法呢当初是先计算ItemCount, 再决定外面这层Top的数量.
其次, 当orderby field不是unique的时候(这种情况很常见, 因为web上很多grid的都有点击列头排序的功能), 第二次的Top未必就是子查询里的最后那些数据.
为什么会是这样呢, 我们知道, 对于任何两次查询, sql server返回的数据顺序是一样的, 但是对一个子查询做倒排序, 得到的结果集未必就是原始结果集的倒置
举个例子:
create table test (a varchar(10), b varchar(10))
insert into test values ('a','1')
insert into test values ('b','1')
insert into test values ('c','3')
insert into test values ('d','4')
insert into test values ('e','4')
insert into test values ('f','4')
select top 6 * from test order by b asc
a b
--------------------
b 1
a 1
c 3
f 4
e 4
d 4
我们想着倒着再来一次top 2应该是e d两条吧, 那么来吧
select top 2 * from
(select top 6 * from test
order by b asc)
as a order by b desc
a b
--------------------
f 4
e 4
很不幸, 实际结果是f, e
也就是每次它排序的结果是确定的, 但倒排序的时候未必是按正排序的反序来排列数据的, 而上述分页方法恰恰是建立在这个不成立的假设上的, 所以对于order by field不唯一的查询, 这种分页方法一定不对
那么有没有解决方案呢? 答案也是肯定的, 把主键或其它唯一性字段加到order by中去
select * from test
select top 2 * from
(select top 6 * from test
order by b asc,a desc)
as a order by b desc,a asc
这样就暂时OK了, 但是, 我们知道有时候查询结果确实会因为join的存在, 而出现根本无法给出唯一性字段(当然这种情况非常少), 这时这个分页方案就彻底没戏了
再来看一个查询方案, not in
SELECT TOP (PageSize) *
FROM table
WHERE (ID NOT IN
(SELECT TOP (PageSize * PageIndex) id
FROM table
ORDER BY field))
ORDER BY field
这个没问题了, 因为始终依据的是同一种排序方法, 对于最后一页的查询, 也同样能够给出正确的记录数, 但是我们要看到这种分页方法的前提是需要给出唯一性字段ID来做not in操作的, 这是它的局限性
不妨多看一个类似的, > max
SELECT TOP (PageSize) *
FROM table
WHERE (ID >
(SELECT MAX(ID)
FROM (SELECT TOP (PageSize * PageIndex) id
FROM table
ORDER BY field) AS T))
ORDER BY field
同样, 需要ID的存在, 如果只是用order by field来代替ID, 那么结果就错得很离谱了, 因为可能大量的field = max(field)的值还没有被显示, 就被无情的>掉了
顺便看一下sql server2005的CTE(common table expression)的方法, 其它说是CTE方法不准确, 应该说row_number函数好一些
with t as(
select row_number() over(order by field) as rownum, * from table
) select * from t where rownum .....
CTE改进的方法同样需要有ID这样的唯一字段的存在
with t as(
select row_number() over(order by field) as rownum, id from table
) select a.* from table a inner join t on a.id = b.id where t.rownum .....
我们这里不研究效率, 因为效率这里有人研究:分页实现方法的性能比较 , 这种方法就不会出现分不准的问题了, 对于无唯一字段的查询也可以解决, 为什么我说这种不能叫CTE分页法呢, 因为这样写也是可以的:
select *from( select row_number() over(order by field) as rownum, * from table ) as a where a.rownum ....
顺便我比对了一下, 和CTE的执行计划是一样的
那么在sql server2000中, 没有唯一性字段下如何要分得准? 临时表, identity字段, 这种方法网上一搜也是遍地都是
