SQLServer 2012の効率的なページング

SQLSERVER 2012新ページング机能が出ました!!この2日間、私は自分のワークマシンのPC(同時、単一スレッドなし)でSqlServer 2000/(2005/2008)/2012の3つのバージョンでのページング性能の比較をしました.1、テーブルデータ量200 W以内:SQLServer 2012のoffset/fetchページング性能とSQLServer 2005 Row_numberのページング性能(結果速度のみを考慮)はほとんど区別されず(難点高下)、SQL 2000のTOPページング性能(約10%)をやや上回った.2、テーブルデータ量2000 W程度:SQLServer 2012のoffset/fetchページング性能はSQLServer 2005 Rowよりやや高いnumberのページング性能は、主にIOに現れるが、両者の性能は(約25%)SQL 2000よりはるかに高いTOPページング性能と言える.3、実行計画2012は2005より簡単で、2005は2000より簡単で、学習が簡単で、2012が最も実現しやすい.Microsoft SQL Server 2014-12.0.2000.8(X 64)Feb 20 2014 20:04:26 Copyright(c)Microsoft CorporationEnterprise Edition(64-bit)on Windows NT 6.1(Build 7601:Service Pack 1)
sql code

/*
     ： .
*/
 
create table Test_paging(
    id int identity(1,1) not null primary key,
    TestNumber int not null,
    TestName varchar(20) not null,
    TestDept varchar(10) not null,
    TestDate datetime not null
)
go
 
with tep(Number,Name,Dept,Date) as
(
    select 1,cast('0_testname' as varchar(20)),cast('0_DBA' as varchar(10)),getdate()
    union all
    select Number+1,cast(cast(Number as varchar(20))+'_testname' as varchar(20)),cast(cast(Number/500 as varchar(10))+'_DBA' as varchar(10)) ,getdate()
    from tep
    where Number<=20000000
)
insert into Test_paging(TestNumber,TestName,TestDept,TestDate)
select Number,Name,Dept,Date from tep option(maxrecursion 0)
 
-- （ ，2012 ， ， ）
create nonclustered index IX_TestDept on Test_paging(
    TestDept
) include
(
    TestName,TestDate
) 
go

　
 
　SQL code

/*
     ： 2012 offset/fetch .
*/
 
dbcc dropcleanbuffers
dbcc freeproccache
 
set statistics io on
set statistics time on
set statistics profile on
 
declare   
    @page int,    -- @page 
    @size int,    -- @size 
    @total int    -- 
 
select @page=20,@size=10,@total=count(1) from Test_paging where TestDept = '1000_DBA' 
 
select 
    TestName,TestDept,TestDate,@total
from 
    Test_paging
where 
    TestDept = '1000_DBA' 
order by id offset (@page-1)*@size rows fetch next @size rows only
 
set statistics io off
set statistics time off
set statistics profile off

　　
 
SQL code

/*
     ： 2005/2008 row_number .
*/
 
dbcc dropcleanbuffers
dbcc freeproccache
 
set statistics io on
set statistics time on
set statistics profile on
 
declare   
    @page int,    -- @page 
    @size int,    -- @size 
    @total int
 
select @page=20,@size=10,@total=count(1) from Test_paging where TestDept = '1000_DBA'
 
select TestName,TestDept,TestDate,@total from
(
    select 
        TestName,TestDept,TestDate,row_number() over(order by ID) as num 
    from 
        Test_paging
    where 
        TestDept = '1000_DBA'
) test where num between (@page-1)*@size+1 and @page*@size order by num 
 
set statistics io off
set statistics time off
set statistics profile off

　　
SQL code

/*
     ： 2000 top .
*/
 
dbcc dropcleanbuffers
dbcc freeproccache
 
set statistics io on
set statistics time on
set statistics profile on
 
declare   
    @page int,    -- @page 
    @size int,    -- @size 
    @total int    -- 
 
select @page=20,@size=10,@total=count(1) from Test_paging where TestDept = '1000_DBA' 
 
 
select TestName,TestDept,TestDate,@total from
(
    select top(@size) id,TestName,TestDept,TestDate from 
    (
        select top(@page*@size) id,TestName,TestDept,TestDate
        from Test_paging 
        where TestDept = '1000_DBA'
        order by id
    )temp1 order by id desc
)temp2 order by id 
 
set statistics io off
set statistics time off
set statistics profile off

原文:http://bbs.csdn.net/topics/390941777