数据库分页产生rownumber的几种方法及性能比较

在sqlsver查询分析器中直接运行即可(提示：运行时间大约30分钟) 如有需要请认真研究代码，会大有收获。

--change tools|options  setting to  discard  query results
set nocount on;
use testdb;
go
if object_id('dbo.RNBenchmark') is not null
drop table dbo.RNBenchmark;
go
if object_id('dbo.SalesBM') is not null
drop table dbo.SalesBM;
go
if object_id('dbo.SalesBMIdentity') is not null
drop  table dbo.SalesBMIdentity;
go
if object_id('dbo.SalesBMCursor')  is not null
drop table  dbo.salesBMCursor;
go
if object_id('dbo.RNTechniques') is not null
drop table dbo.RNTechniques;
create table dbo.RNTechniques
(
  tid int not null primary key,
  technique varchar(25) not null
);

insert into dbo.RNTechniques (tid,technique)values(1,'Subquery')
insert into dbo.RNTechniques (tid,technique)values(2,'Identity')
insert into dbo.RNTechniques (tid,technique)values(3,'Cursor')
insert into dbo.RNTechniques (tid,technique)values(4,'Row_Number')
go

create table  dbo.RNBenchmark
(
  tid         int not null  references  dbo.RNTechniques(tid),
  numrows     int not null,
  runtimemcs  bigint  not null,
  primary key(tid,numrows)
);
go

create table dbo.SalesBM
(
  empid  int not null  identity primary key,
  qty int not null
);
create index idx_qty_empid  on dbo.SalesBM(qty,empid);
go
create table  dbo.SalesBMIdentity(empid  int,qty int,rn int identity);
go

create table dbo.SalesBMCursor(empid int,qty int,rn int);
go

declare
@maxnumrows as int,
@steprows  as int,
@curnumrows as int,
@dt   as datetime;

set @maxnumrows=100000;
set @steprows=10000;
set @curnumrows=10000;

while  @curnumrows<=@maxnumrows
begin
truncate  table dbo.SalesBM;
insert  into   dbo.SalesBM(qty)
select cast(1+999.9999999999*rand(checksum(newid())) as int)
from dbo.Nums
where n<=@curnumrows;

--'subquery'
dbcc freeproccache with No_INFOMSGS;

dbcc DROPCLEANBUFFERS WITH NO_INFOMSGS;

SET @dt=getdate();--use sysdatetime() after to 2008

select  empid,qty,
(
   select count(*)
   from dbo.SalesBM as s2
   where s2.qty<s1.qty
   or
   (
      s2.qty=s1.qty  and s2.empid<=s1.empid
   )
)  as rn
from dbo.SalesBM as s1
order by qty,empid;

insert  into  dbo.RNBenchmark(tid,numrows,runtimemcs)
values
(
  1,@curnumrows,datediff(ms,@dt,getdate())
);

--'identity'
truncate  table dbo.SalesBMIdentity;
dbcc freeproccache with No_INFOMSGS;

dbcc DROPCLEANBUFFERS WITH NO_INFOMSGS;
set @dt=getdate();
insert into dbo.SalesBMIdentity(empid,qty)
select empid,qty  from dbo.salesBM  order by qty,empid;

select empid,qty,rn  from dbo.SalesBMIdentity;
insert into dbo.RNBenchmark(tid,numrows,runtimemcs)
values(2,@curnumrows,datediff(ms,@dt,getdate()));
--cursor
truncate table  dbo.SalesBMCursor;
dbcc freeproccache with No_INFOMSGS;
dbcc DROPCLEANBUFFERS WITH NO_INFOMSGS;
set @dt=getdate();
declare @empid as int,@qty  as int,@rn  as int;
begin tran
declare rncursor  cursor FAST_FORWARD
for
select empid,qty  from dbo.salesBM order by qty,empid;
open rncursor;
set @rn=0;
fetch next from rncursor into  @empid,@qty;
while @@fetch_status=0
begin
set @rn=@rn+1;
insert into dbo.SalesBMCursor(empid,qty,rn)
values
(
  @empid,@qty,@rn
);
fetch next from rncursor into  @empid,@qty;
end
close rncursor;
deallocate rncursor;--delete rncursor;
commit  tran;

select emPid,qty,rn  from dbo.SalesBMCursor;

insert into  dbo.RNBenchmark(tid,numrows,runtimemcs)
values
(3,@curnumrows,datediff(ms,@dt,getdate()));

--row_number
dbcc freeproccache with No_INFOMSGS;
dbcc DROPCLEANBUFFERS WITH NO_INFOMSGS;

set @dt=getdate();
select empid,qty, row_number() over(order by qty,empid)as rn
from dbo.SalesBM;

insert into dbo.RNBenchmark(tid,numrows,runtimemcs)
values (4,@curnumrows,datediff(ms,@dt,getdate()));
set @curnumrows=@curnumrows+@steprows;
end

--pivot table
SELECT  numrows,
[Subquery],[Cursor],[Identity],[Row_Number]
from
(
  select technique,numrows,runtimemcs
  from dbo.RNBenchmark as b
   join dbo.RNTechniques  as t
    on b.tid=t.tid
) as d
pivot(max(runtimemcs) for technique
in([subquery],[identity],[cursor],[row_number])

)  as p
order by numrows,[Subquery],[Cursor],[Identity],[Row_Number];