MSSQL第一篇：SQL2000-2012三种分页方式测试比较

近两天在自己工作机的PC（没有并发，单一线程）上做了SqlServer  2000/ (2005/2008)/2012三个版本下的分页性能比较。

大致可得出以下结果：
1、表数据量200W以内：SQLServer2012 的offset/fetch分页性能和SQLServer2005 Row_number的分页性能（仅考虑出结果速度）基本没区别（难分高下），略高于（大约10%）SQL2000的TOP分页性能。
2、表数据量2000W左右：SQLServer2012 的offset/fetch分页性能略高于SQLServer2005 Row_number的分页性能，主要体现在IO上，但是两者性能可算是远高于（大约25%）SQL2000的TOP分页性能。
3、执行计划2012比2005简单，2005比2000简单，学习简易程度，2012最容易实现。
特此分享一下，下面是我的测试脚本，有兴趣可以自己也试试
测试环境：
Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 
Feb 20 2014 20:04:26 
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

/*** 功能：生成测试数据.   */

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

/***   功能：测试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

 

/*** 功能：测试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

 

/*** 功能：测试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

 

转载于:https://www.cnblogs.com/szDBA/p/6044257.html