大数据量分页存储过程效率测试附代码

最新推荐文章于 2019-07-22 15:10:46 发布

hsapphire

最新推荐文章于 2019-07-22 15:10:46 发布

阅读量69

点赞数

本文链接：https://blog.csdn.net/hsapphire/article/details/83906691

版权

在项目中，我们经常遇到或用到分页，那么在大数据量（百万级以上）下，哪种分页算法效率最优呢？我们不妨用事实说话。

测试环境

硬件：CPU 酷睿双核T5750 内存：2G

软件:Windows server 2003 + Sql server 2005

OK,我们首先创建一数据库：data_Test,并在此数据库中创建一表：tb_TestTable

1

create database data_Test -- 创建数据库data_Test
2

GO
3

use data_Test
4

GO
5

create table tb_TestTable -- 创建表
6

(
7

id int identity ( 1 , 1 ) primary key ,
8

userName nvarchar ( 20 ) not null ,
9

userPWD nvarchar ( 20 ) not null ,
10

userEmail nvarchar ( 40 ) null
11

)
12

然后我们在数据表中插入2000000条数据:

1

-- 插入数据
2

set identity_insert tb_TestTable on
3

declare @count int
4

set @count = 1
5

while @count <= 2000000
6

begin
7

insert into tb_TestTable(id,userName,userPWD,userEmail) values ( @count , ' admin ' , ' admin888 ' , ' lli0077@yahoo.com.cn ' )
8

set @count = @count + 1
9

end
10

set identity_insert tb_TestTable off

我首先写了五个常用存储过程：

1，利用select top 和select not in进行分页，具体代码如下：

1

create procedure proc_paged_with_notin -- 利用select top and select not in
2

(
3

@pageIndex int , -- 页索引
4

@pageSize int -- 每页记录数
5

)
6

as
7

begin
8

set nocount on ;
9

declare @timediff datetime -- 耗时
10

declare @sql nvarchar ( 500 )
11

select @timediff = Getdate ()
12

set @sql = ' select top ' + str ( @pageSize ) + ' * from tb_TestTable where(ID not in(select top ' + str ( @pageSize * @pageIndex ) + ' id from tb_TestTable order by ID ASC)) order by ID '
13

execute ( @sql ) -- 因select top后不支技直接接参数，所以写成了字符串@sql
14

select datediff (ms, @timediff , GetDate ()) as 耗时
15

set nocount off ;
16

end

2,利用select top 和 select max(列键)

1 create procedure proc_paged_with_selectMax -- 利用select top and select max(列)
2

(

@pageIndex int , -- 页索引
4

@pageSize int -- 页记录数
5

)
6

as
7

begin
8

set nocount on ;
9

declare @timediff datetime
10

declare @sql nvarchar ( 500 )
11

select @timediff = Getdate ()
12

set @sql = ' select top ' + str ( @pageSize ) + ' * From tb_TestTable where(ID>(select max(id) From (select top ' + str ( @pageSize * @pageIndex ) + ' id From tb_TestTable order by ID) as TempTable)) order by ID '
13

execute ( @sql )
14

select datediff (ms, @timediff , GetDate ()) as 耗时
15

set nocount off ;
16

end

3,利用select top和中间变量--此方法因网上有人说效果最佳，所以贴出来一同测试

1

create procedure proc_paged_with_Midvar -- 利用ID>最大ID值和中间变量
2

(
3

@pageIndex int ,
4

@pageSize int
5

)
6

as
7

declare @count int
8

declare @ID int
9

declare @timediff datetime
10

declare @sql nvarchar ( 500 )
11

begin
12

set nocount on ;
13

select @count = 0 , @ID = 0 , @timediff = getdate ()
14

select @count = @count + 1 , @ID = case when @count <= @pageSize * @pageIndex then ID else @ID end from tb_testTable order by id
15

set @sql = ' select top ' + str ( @pageSize ) + ' * from tb_testTable where ID> ' + str ( @ID )
16

execute ( @sql )
17

select datediff (ms, @timediff , getdate ()) as 耗时
18

set nocount off ;
19

end
20

4,利用Row_number() 此方法为SQL server 2005中新的方法,利用Row_number()给数据行加上索引

1

create procedure proc_paged_with_Rownumber -- 利用SQL 2005中的Row_number()
2

(
3

@pageIndex int ,
4

@pageSize int
5

)
6

as
7

declare @timediff datetime
8

begin
9

set nocount on ;
10

select @timediff = getdate ()
11

select * from ( select * ,Row_number() over ( order by ID asc ) as IDRank from tb_testTable) as IDWithRowNumber where IDRank > @pageSize * @pageIndex and IDRank < @pageSize * ( @pageIndex + 1 )
12

select datediff (ms, @timediff , getdate ()) as 耗时
13

set nocount off ;
14

end
15

5，利用临时表及Row_number

1

create procedure proc_CTE -- 利用临时表及Row_number
2

(
3

@pageIndex int , -- 页索引
4

@pageSize int -- 页记录数
5

)
6

as
7

set nocount on ;
8

declare @ctestr nvarchar ( 400 )
9

declare @strSql nvarchar ( 400 )
10

declare @datediff datetime
11

begin
12

select @datediff = GetDate ()
13

set @ctestr = ' with Table_CTE as
14

(select ceiling((Row_number() over(order by ID ASC))/ ' + str ( @pageSize ) + ' ) as page_num,* from tb_TestTable) ' ;
15

set @strSql = @ctestr + ' select * From Table_CTE where page_num= ' + str ( @pageIndex )
16

end
17

begin
18

execute sp_executesql @strSql
19

select datediff (ms, @datediff , GetDate ())
20

set nocount off ;
21

end
22

OK,至此，存储过程创建完毕，我们分别在每页10条数据的情况下在第2页，第1000页，第10000页，第100000页，第199999页进行测试，耗时单位：ms 每页测试5次取其平均值

存过	第2页耗时	第1000页耗时	第10000页耗时	第100000页耗时	第199999页耗时	效率排行
1用not in	0ms	16ms	47ms	475ms	953ms	3
2用select max	5ms	16ms	35ms	325ms	623ms	1
3中间变量	966ms	970ms	960ms	945ms	933ms	5
4row_number	0ms	0ms	34ms	365ms	710ms	2
4临时表	780ms	796ms	798ms	780ms	805ms	4