sql 几种分页方法的性能对比

 

create database DBTest


use DBTest--创建测试表

create table pagetest
(id int identity(1,1) not null,
col01 int null,
col02 nvarchar(50) null,
col03 datetime null)

--1000万记录集 

delete from pagetest

declare @i int
set @i=0
while(@i<10000000)
begin
insert into pagetest 
select cast(floor(rand()*10000) as int),left(newid(),10),getdate()    
set @i=@i+1
end 

--数据库没插入那么多只插入了4005805 条 测试结果 






--设定开始和结束的时间,页大小 和 页

declare @begin_date datetime,@end_date datetime
declare @pagesize int ,@curPage int
declare @strSql varchar(800)
set @pagesize=50
set @curPage=78000  --第万页的数据
select @begin_date = getdate()

--用top not in 
--set @strSql='select top '+ cast(@pagesize as varchar(50))+' * from  pagetest where 
--id not in (select top '+Cast(@pagesize*(@curPage-1) as varchar(50)) +' id from pagetest)'
--exec(@strSql)
 

--用not exists

--select top  50 * from pagetest where 
--not exists (select 1 from (select top (50*78000) id from pagetest) as a where a.id =pagetest.id )
--order by id 
 

--用max、top
--select top 50 * from pagetest 
--where id >(select max(id) from (select top (50*78000) id  from pagetest) as a)
--order by id 
 

-- 用row_number 
--select top 50 * from
--(select row_number()over(order by id) rid,* from pagetest) as tbTemp
--where rid>(50*78000)
 

--select * from (select row_number()over(order by id) rid,* from pagetest) as tbTemp
--where rid>50*78000 and rid<=(50*(78000+1))
 

--select * from (select row_number()over(order by id) rid,* from pagetest) as tbTemp
--where  rid between 50*78000 and 50*(78000+1)

 
--csdn 的另外的一种方法
select * from (
select row_number()over(order by rowcol) rid,*
                 from (select top (50*(78000+1)) rowcol=0,* from dbo.pagetest order by id ) 
                 as a
            ) as b 
where b.rid >50*78000

 

select @end_date = getdate()
select datediff(ms,@begin_date,@end_date) as '毫秒'






在同样400万条数据中，用max .top  这种方法是最快的