[转]几种常见SQL分页方式
创建环境:
create table pagetest ( id int identity(1,1) not null, col01 int null, col02 nvarchar(50) null, col03 datetime null ) --100万记录集(大约耗时5min) declare @i int set @i=0 while(@i<1000000) begin insert into pagetest select cast(floor(rand()*10000) as int),left(newid(),10),getdate() set @i=@i+1 end
几种分页方式比较:
--写法1,not in/top --每页50条,取第2015页数据 declare @begin_date datetime declare @end_date datetime select @begin_date = getdate() select top 50 * from pagetest where id not in (select top ((2015-1)*50) id from pagetest order by id) order by id select @end_date = getdate() select datediff(ms,@begin_date,@end_date) as 'ms'
结果:473 ms
--写法2,not exists declare @begin_date datetime declare @end_date datetime select @begin_date = getdate() select top 50 * from pagetest where not exists (select 1 from (select top ((2015-1)*50) id from pagetest order by id)a where a.id=pagetest.id) order by id select @end_date = getdate() select datediff(ms,@begin_date,@end_date) as 'ms'
结果:483 ms
--写法3,max/top declare @begin_date datetime declare @end_date datetime select @begin_date = getdate() select top 50 * from pagetest where id>(select max(id) from (select top ((2015-1)*50) id from pagetest order by id)a) order by id select @end_date = getdate() select datediff(ms,@begin_date,@end_date) as 'ms'
结果:343 ms
--写法4,row_number() 只支持sql2005及以上版本 declare @begin_date datetime declare @end_date datetime select @begin_date = getdate() select top 50 * from (select row_number()over(order by id)rownumber,* from pagetest)a where rownumber>((2015-1)*50) select @end_date = getdate() select datediff(ms,@begin_date,@end_date) as 'ms'
结果:293 ms
以上测试在不同环境在会有略微差别,整体来说都还不错,row_number和max/top方式性能相对更好些,而row_number只支持sql2005版本及以上,所以max/top在大部分环境下会是比较好的选择。
简单将ROWNUMBER,max/top的方式封装到存储过程。
create proc [dbo].[spSqlPageByRownumber] @tbName varchar(255), --表名 @tbFields varchar(1000), --返回字段(可以为*) @PageSize int, --页尺寸 @PageIndex int, --页码 @strWhere varchar(1000), --查询条件(为空则无条件) @StrOrder varchar(255), --排序条件 @Total int output --返回总记录数 as declare @strSql varchar(5000) --主语句 declare @strSqlCount nvarchar(500)--查询记录总数主语句 --------------总记录数--------------- if @strWhere !='' begin set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName + ' where '+ @strWhere end else begin set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName end --------------分页------------ if @PageIndex <= 0 begin set @PageIndex = 1 end set @strSql='Select * from (Select row_number() over('+@strOrder+') rowId,'+ @tbFields +' from ' + @tbName + ' where 1=1 ' + @strWhere+' ) tb where tb.rowId >'+str((@PageIndex-1)*@PageSize) +' and tb.rowId <= ' +str(@PageIndex*@PageSize) exec sp_executesql @strSqlCount,N'@TotalCout int output',@Total output exec(@strSql)
create proc [dbo].[spSqlPageByMaxTop] @tbName varchar(255), --表名 @tbFields varchar(1000), --返回字段(可以为*) @PageSize int, --页尺寸 @PageIndex int, --页码 @strWhere varchar(1000), --查询条件(为空则无条件) @StrOrder varchar(255), --排序条件 @Total int output --返回总记录数 as declare @strSql varchar(5000) --主语句 declare @strSqlCount nvarchar(500)--查询记录总数主语句 --------------总记录数--------------- if @strWhere !='' begin set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName + ' where '+ @strWhere end else begin set @strSqlCount='Select @TotalCout=count(*) from ' + @tbName end --------------分页------------ if @PageIndex <= 0 begin set @PageIndex = 1 end set @strSql='select top '+str(@PageSize)+' * from ' + @tbName + ' where id>(select max(id) from (select top '+str((@PageIndex-1)*@PageSize)+' id from ' + @tbName + ''+@strOrder+')a) '+@strOrder+'' exec sp_executesql @strSqlCount,N'@TotalCout int output',@Total output exec(@strSql)
转载自:http://www.cnblogs.com/iamowen/archive/2011/11/03/2235068.html