朱燚

我们先给出几种主要的分页方法和核心语句,然后直接给出结论,有兴趣的读者可以看看后面的数据

几种常用存储过程分页方法

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的指数,试验结果如下

数据解释和分析

临时表分为有没有缓存两种时间,CTE就是上面的方法,CTE改进只是把选入CTE临时表的列数减少了,只选取了页号和主键,Null表示时间无法计算(时间太长),数据单位是毫秒.

从上面的数据可以看到,TopN在前32页都是有优势的,但是页数增大后,性能降低很快,CTE改进比CTE有所进步,平均进步两秒左右,但是还是比临时表慢,但是考虑临时表会增大日志文件的大小,引起大量IO,CTE也就有他自己的优势,公司现在正在使用的存储过程效率不错,但是在页码靠后的情况下性能会降低