存储过程分页探究
首先新建一个数据表并插入数据,为存储过程分页研究提供数据支持。本文批量插入2000000条数据插入脚本如下:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 declare @Count int 2 set @Count=0 3 while @Count<2000000 4 begin 5 insert into Teacher (Name,Sex,City,BirthDay) values('张三',1,'陕西西安',getdate()) 6 set @Count=@Count+1 7 end
主键采用自动生成的方式,不在上述脚本里面。由于采用数据库分页的方式只需要加载需要显示的数据而不需要一次加载所有的数据,从而达到提高页面加载速度的目的,所以下面来研究、比较和总结几种存储过程分页的方法,以供借鉴和查找。
1.利用select top 和select not in
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 create procedure PagerWithNotIN 2 @PageIndex int, --页索引 3 @PageSize int --页大小 4 as 5 begin 6 set nocount on 7 declare @TimeDiff datetime 8 declare @sql varchar(1000) 9 select @TimeDiff=getdate() 10 set @sql='select top '+str(@PageSize)+' * from Teacher where ID not in 11 (select top '+str(@PageSize*@PageIndex)+' ID from Teacher order by ID asc) order by ID' 12 execute(@sql) 13 select datediff(ms,@TimeDiff,getdate()) as 时间 14 set nocount off 15 end
2.利用select top 和 select max(列键),注意降序使用min(列键)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 create procedure PagerWithSelectMax 2 @PageIndex int, --页索引 3 @PageSize int --页大小 4 as 5 begin 6 set nocount on 7 declare @TimeDiff datetime 8 declare @sql varchar(1000) 9 select @TimeDiff=getdate() 10 set @sql='select top '+str(@PageSize)+' * from Teacher where ID>(select max(ID) from 11 (select top '+str(@PageSize*@PageIndex)+' ID from Teacher order by ID asc) temp) order by ID' 12 execute(@sql) 13 select datediff(ms,@TimeDiff,getdate()) as 时间 14 set nocount off 15 end
3.利用row_number()方法,注意SQL Server 2005之后均有该方法
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 CREATE procedure [dbo].[PagerWithRowNumber] 2 @PageIndex int, --页索引 3 @PageSize int --页大小 4 as 5 begin 6 set nocount on 7 declare @TimeDiff datetime 8 select @TimeDiff=getdate() 9 select * from 10 (select *,row_number() over(order by ID asc) as RowNumber from Teacher) 11 as temp where RowNumber>@PageSize*@PageIndex and RowNumber<=@PageSize*(@PageIndex+1) 12 select datediff(ms,@TimeDiff,getdate()) as 时间 13 set nocount off 14 end
此外,还有利用中间变量、临时表等方法均可以实现存储过程分页。个人认为效率不及上述三种,在此不再罗列。以后如有发现更好的方法,继续补充进来。闲话不说,下面列出上面三种存储过程分页的测试结果(以每页20条为准,每页测试3次取平均值):
存储过程分页 | 第2页耗时 | 第100页耗时 | 第1000页耗时 | 第10000页耗时 | 第50000页耗时 | 第99999页耗时 | 效率排行 |
利用select top 和select not in | |||||||
利用select top 和 select max(列键) | |||||||
利用row_number()方法 |
举首望孤星, 低头抚古琴, 高山流水吟不尽,空谷觅知音, 秋风秋水此登临, 秋月秋霜一寸心, 世间只有真情在, 除却巫山不是云, 为有知己万里行, 山水苍茫洗胸襟, 一曲情思随风去,归来化做断弦琴。