[转]几种常见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

posted @ 2015-03-11 11:15  xiepeixing  阅读(773)  评论(0编辑  收藏  举报