分页实现方法的性能比较

dn001 2009-06-03 09:56:55

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

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

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	Null	9806	869	2578	635	8948
3162	Null	9822	2485	4110	12460	8210
10000	Null	9754	7812	11926	14250	7359
31623	Null	9775	18729	33218	15249	7511
100000	Null	Null	31538	55569	17139	6124