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

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。