大数据量分页存储过程效率测试附代码(转http://www.cnblogs.com/lli0077/archive/2008/09/03/1282862.html)

测试环境 硬件:CPU 酷睿双核T5750 内存:2G 软件:Windows server 2003 + sql
server 2005
OK,我们首先创建一数据库:data_Test,并在此数据库中创建一表:tb_TestTable
复制代码 代码如下: create database
data_Test –创建数据库 data_Test GO use data_Test GO create table
tb_TestTable –创建表 (id int identity(1,1) primary key, userName
nvarchar(20) not null, userPWD nvarchar(20) not null, userEmail
nvarchar(40) null) GO 然后我们在数据表中插入2000000条数据: 复制代码 代码如下: –插入数据 set
identity_insert tb_TestTable on declare @count int set @count=1 while
@count=2000000 begin insert into
tb_TestTable(id,userName,userPWD,userEmail)
values(@count,’admin’,’admin888′,’lli0077@yahoo.com.cn’) set
@count=@count+1 end set identity_insert tb_TestTable off
我首先写了五个常用存储过程: 1,利用select top 和select not
in进行分页,具体代码如下: 复制代码
代码如下:create procedure proc_paged_with_notin –利用select top and
select not in ( @pageIndex int, –页索引 @pageSize int –每页记录数 ) as
begin set nocount on; declare @timediff datetime –耗时 declare @sql
nvarchar(500) select @timediff=Getdate() 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’ execute(@sql) –因select
top后不支技直接接参数,所以写成了字符串@sql select
datediff(ms,@timediff,GetDate()) as 耗时 set nocount off; end
2,利用select top 和 select max(列键) 复制代码 代码如下:create procedure
proc_paged_with_selectMax –利用select top and select max(列) (
@pageIndex int, –页索引 @pageSize int –页记录数 ) as begin set nocount
on; declare @timediff datetime declare @sql nvarchar(500) select
@timediff=Getdate() 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’ execute(@sql) select
datediff(ms,@timediff,GetDate()) as 耗时 set nocount off; end
3,利用select
top和中间变量–此方法因网上有人说效果最佳,所以贴出来一同测试 复制代码 代码如下:create procedure
proc_paged_with_Midvar –利用ID最大ID值和中间变量 ( @pageIndex int,
@pageSize int ) as declare @count int declare @ID int declare @timediff
datetime declare @sql nvarchar(500) begin set nocount on; select
@count=0,@ID=0,@timediff=getdate() select @count=@count+1,@ID=case when
@count=@pageSize*@pageIndex then ID else @ID end from tb_testTable
order by id set @sql=’select top ‘+str(@pageSize)+’ * from
tb_testTable where ID’+str(@ID) execute(@sql) select
datediff(ms,@timediff,getdate()) as 耗时 set nocount off; end
4,利用Row_number() 此方法为sql server
2005中新的方法,利用Row_number()给数据行加上索引 复制代码 代码如下:create procedure
proc_paged_with_Rownumber –利用SQL 2005中的Row_number() (
@pageIndex int, @pageSize int ) as declare @timediff datetime begin set
nocount on; select @timediff=getdate() 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) select datediff(ms,@timediff,getdate())
as 耗时 set nocount off; end 5,利用临时表及Row_number 复制代码 代码如下:create procedure proc_CTE
–利用临时表及Row_number ( @pageIndex int, –页索引 @pageSize int
–页记录数 ) as set nocount on; declare @ctestr nvarchar(400) declare
@strSql nvarchar(400) declare @datediff datetime begin select
@datediff=GetDate() set @ctestr=’with Table_CTE as (select
ceiling((Row_number() over(order by ID ASC))/’+str(@pageSize)+’) as
page_num,* from tb_TestTable)’; set @strSql=@ctestr+’ select * From
Table_CTE where page_num=’+str(@pageIndex) end begin execute
sp_executesql @strSql select datediff(ms,@datediff,GetDate()) set
nocount off; end
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 测试结果显示:select max row_numbernot
in临时表中间变量 于是我对效率最高的select
max方法用2分法进行了扩展,代码取自互联网,我修改了ASC排序时取不到值的BUG,测试结果:
2分法 156ms 156ms 180ms 470ms 156ms 1*
从测试结果来看,使用2分法确实可以提高效率并使效率更为稳定,我又增加了第159999页的测试,用时仅296ms,效果相当的不错!
下面是2分法使用select max的代码,已相当完善。 复制代码 代码如下: –/*—–存储过程 分页处理
孙伟 2005-03-28创建 ——-*/ –/*—–存储过程 分页处理 浪尘
2008-9-1修改———-*/ –/*—–
对数据进行了2分处理使查询前半部分数据与查询后半部分数据性能相同
——-*/ alter PROCEDURE proc_paged_2part_selectMax ( @tblName
nvarchar(200), —-要显示的表或多个表的连接 @fldName nvarchar(500) =
‘*’, —-要显示的字段列表 @pageSize int = 10, —-每页显示的记录个数
@page int = 1, —-要显示那一页的记录 @fldSort nvarchar(200) = null,
—-排序字段列表或条件 @Sort bit = 0,
—-排序方法,0为升序,1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)–程序传参如:’
SortA Asc,SortB Desc,SortC ‘) @strCondition nvarchar(1000) = null,
—-查询条件,不需where @ID nvarchar(150), —-主表的主键 @Dist bit = 0,
—-是否添加查询字段的 DISTINCT 默认0不添加/1添加 @pageCount int = 1
output, —-查询结果分页后的总页数 @Counts int = 1 output
—-查询到的记录数 ) AS SET NOCOUNT ON Declare @sqlTmp nvarchar(1000)
—-存放动态生成的SQL语句 Declare @strTmp nvarchar(1000)
—-存放取得查询结果总数的查询语句 Declare @strID nvarchar(1000)
—-存放取得查询开头或结尾ID的查询语句 Declare @strSortType nvarchar(10)
—-数据排序规则A Declare @strFSortType nvarchar(10) —-数据排序规则B
Declare @SqlSelect nvarchar(50) —-对含有DISTINCT的查询进行SQL构造
Declare @SqlCounts nvarchar(50) —-对含有DISTINCT的总数查询进行SQL构造
declare @timediff datetime –耗时测试时间差 select @timediff=getdate()
if @Dist = 0 begin set @SqlSelect = ‘select ‘ set @SqlCounts =
‘Count(*)’ end else begin set @SqlSelect = ‘select distinct ‘ set
@SqlCounts = ‘Count(DISTINCT ‘+@ID+’)’ end if @Sort=0 begin set
@strFSortType=’ ASC ‘ set @strSortType=’ DESC ‘ end else begin set
@strFSortType=’ DESC ‘ set @strSortType=’ ASC ‘ end
——–生成查询语句——– –此处@strTmp为取得查询结果数量的语句 if
@strCondition is null or @strCondition=” –没有设置显示条件 begin set
@sqlTmp = @fldName + ‘ From ‘ + @tblName set @strTmp = @SqlSelect+’
@Counts=’+@SqlCounts+’ FROM ‘+@tblName set @strID = ‘ From ‘ + @tblName
end else begin set @sqlTmp = + @fldName + ‘From ‘ + @tblName + ‘ where
(10) ‘ + @strCondition set @strTmp = @SqlSelect+’ @Counts=’+@SqlCounts+’
FROM ‘+@tblName + ‘ where (10) ‘ + @strCondition set @strID = ‘ From ‘ +
@tblName + ‘ where (10) ‘ + @strCondition end
—-取得查询结果总数量—– exec sp_executesql @strTmp,N’@Counts int
out ‘,@Counts out declare @tmpCounts int if @Counts = 0 set @tmpCounts =
1 else set @tmpCounts = @Counts –取得分页总数 set
@pageCount=(@tmpCounts+@pageSize-1)/@pageSize
/**//**//**//**当前页大于总页数 取最后一页**/ if
@page@pageCount set @page=@pageCount –/*—–数据分页2分处理——-*/
declare @pageIndex int –总数/页大小 declare @lastcount int
–总数%页大小 set @pageIndex = @tmpCounts/@pageSize set @lastcount =
@tmpCounts%@pageSize if @lastcount 0 set @pageIndex = @pageIndex + 1
else set @lastcount = @pagesize –//***显示分页 if @strCondition is
null or @strCondition=” –没有设置显示条件 begin if @pageIndex2 or
@page=@pageIndex / 2 + @pageIndex % 2 –前半部分数据处理 begin if
@page=1 set @strTmp=@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’
‘+ @fldName+’ from ‘+@tblName +’ order by ‘+ @fldSort +’ ‘+
@strFSortType else begin if @Sort=1 begin set @strTmp=@SqlSelect+’ top
‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName +’
where ‘+@ID+’ (select min(‘+ @ID +’) from (‘+ @SqlSelect+’ top ‘+
CAST(@pageSize*(@page-1) as Varchar(20)) +’ ‘+ @ID +’ from ‘+@tblName
+’ order by ‘+ @fldSort +’ ‘+ @strFSortType+’) AS TBMinID)’ +’ order by
‘+ @fldSort +’ ‘+ @strFSortType end else begin set @strTmp=@SqlSelect+’
top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName +’
where ‘+@ID+’ (select max(‘+ @ID +’) from (‘+ @SqlSelect+’ top ‘+
CAST(@pageSize*(@page-1) as Varchar(20)) +’ ‘+ @ID +’ from ‘+@tblName
+’ order by ‘+ @fldSort +’ ‘+ @strFSortType+’) AS TBMinID)’ +’ order by
‘+ @fldSort +’ ‘+ @strFSortType end end end else begin set @page =
@pageIndex-@page+1 –后半部分数据处理 if @page = 1 –最后一页数据显示
set @strTmp=@SqlSelect+’ * from (‘+@SqlSelect+’ top ‘+ CAST(@lastcount
as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName +’ order by ‘+ @fldSort
+’ ‘+ @strSortType+’) AS TempTB’+’ order by ‘+ @fldSort +’ ‘+
@strFSortType else if @Sort=1 begin set @strTmp=@SqlSelect+’ * from
(‘+@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’
from ‘+@tblName +’ where ‘+@ID+’ (select max(‘+ @ID +’) from(‘+
@SqlSelect+’ top ‘+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20))
+’ ‘+ @ID +’ from ‘+@tblName +’ order by ‘+ @fldSort +’ ‘+
@strSortType+’) AS TBMaxID)’ +’ order by ‘+ @fldSort +’ ‘+
@strSortType+’) AS TempTB’+’ order by ‘+ @fldSort +’ ‘+ @strFSortType
end else begin set @strTmp=@SqlSelect+’ * from (‘+@SqlSelect+’ top ‘+
CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName +’ where
‘+@ID+’ (select min(‘+ @ID +’) from(‘+ @SqlSelect+’ top ‘+
CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +’ ‘+ @ID +’ from
‘+@tblName +’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TBMaxID)’ +’
order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TempTB’+’ order by ‘+
@fldSort +’ ‘+ @strFSortType end end end else –有查询条件 begin if
@pageIndex2 or @page=@pageIndex / 2 + @pageIndex % 2 –前半部分数据处理
begin if @page=1 set @strTmp=@SqlSelect+’ top ‘+ CAST(@pageSize as
VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName +’ where 1=1 ‘ +
@strCondition + ‘ order by ‘+ @fldSort +’ ‘+ @strFSortType else
if(@Sort=1) begin set @strTmp=@SqlSelect+’ top ‘+ CAST(@pageSize as
VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName +’ where ‘+@ID+’ (select
min(‘+ @ID +’) from (‘+ @SqlSelect+’ top ‘+ CAST(@pageSize*(@page-1) as
Varchar(20)) +’ ‘+ @ID +’ from ‘+@tblName +’ where (1=1) ‘ +
@strCondition +’ order by ‘+ @fldSort +’ ‘+ @strFSortType+’) AS
TBMinID)’ +’ ‘+ @strCondition +’ order by ‘+ @fldSort +’ ‘+
@strFSortType end else begin set @strTmp=@SqlSelect+’ top ‘+
CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName +’ where
‘+@ID+’ (select max(‘+ @ID +’) from (‘+ @SqlSelect+’ top ‘+
CAST(@pageSize*(@page-1) as Varchar(20)) +’ ‘+ @ID +’ from ‘+@tblName
+’ where (1=1) ‘ + @strCondition +’ order by ‘+ @fldSort +’ ‘+
@strFSortType+’) AS TBMinID)’ +’ ‘+ @strCondition +’ order by ‘+
@fldSort +’ ‘+ @strFSortType end end else begin set @page =
@pageIndex-@page+1 –后半部分数据处理 if @page = 1 –最后一页数据显示
set @strTmp=@SqlSelect+’ * from (‘+@SqlSelect+’ top ‘+ CAST(@lastcount
as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName +’ where (1=1) ‘+
@strCondition +’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TempTB’+’
order by ‘+ @fldSort +’ ‘+ @strFSortType else if(@Sort=1) set
@strTmp=@SqlSelect+’ * from (‘+@SqlSelect+’ top ‘+ CAST(@pageSize as
VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName +’ where ‘+@ID+’ (select
max(‘+ @ID +’) from(‘+ @SqlSelect+’ top ‘+
CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +’ ‘+ @ID +’ from
‘+@tblName +’ where (1=1) ‘+ @strCondition +’ order by ‘+ @fldSort +’ ‘+
@strSortType+’) AS TBMaxID)’ +’ ‘+ @strCondition+’ order by ‘+ @fldSort
+’ ‘+ @strSortType+’) AS TempTB’+’ order by ‘+ @fldSort +’ ‘+
@strFSortType else set @strTmp=@SqlSelect+’ * from (‘+@SqlSelect+’ top
‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName +’
where ‘+@ID+’ (select min(‘+ @ID +’) from(‘+ @SqlSelect+’ top ‘+
CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +’ ‘+ @ID +’ from
‘+@tblName +’ where (1=1) ‘+ @strCondition +’ order by ‘+ @fldSort +’ ‘+
@strSortType+’) AS TBMaxID)’ +’ ‘+ @strCondition+’ order by ‘+ @fldSort
+’ ‘+ @strSortType+’) AS TempTB’+’ order by ‘+ @fldSort +’ ‘+
@strFSortType end end ——返回查询结果—– exec sp_executesql
@strTmp select datediff(ms,@timediff,getdate()) as 耗时 –print @strTmp
SET NOCOUNT OFF GO 执行示例:exec proc_paged_2part_selectMax
‘tb_testTable’,’ID,userName,userPWD,userEmail’,10,100000,’ID’,0,null,’ID’,0
这种测试只在单机进行,并且没有在实际开发WEB项目中分页测试,测试项也比较单一,所以不够全面系统,但从其效率相比上,我们可以在数据库分页算法上进行有效的控制。

 

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

 

测试环境

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

软件:Windows server 2003    +   Sql server 2005

 

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

 

图片 1

 1图片 2create database data_Test  –创建数据库data_Test 
 2图片 3GO 
 3图片 4use data_Test 
 4图片 5GO 
 5图片 6create table tb_TestTable   –创建表 
 6图片 7
 7图片 8    id int identity(1,1) primary key, 
 8图片 9    userName nvarchar(20) not null, 
 9图片 10    userPWD nvarchar(20) not null, 
10图片 11    userEmail nvarchar(40) null 
11图片 12
12图片 13GO

图片 14

 

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

 

图片 15

 1图片 16–插入数据 
 2图片 17set identity_insert tb_TestTable on 
 3图片 18declare @count int 
 4图片 19set @count=1 
 5图片 20while @count<=2000000 
 6图片 21begin  
 7图片 22    insert into tb_TestTable(id,userName,userPWD,userEmail) values(@count,’admin’,’admin888′,’lli0077@yahoo.com.cn’) 
 8图片 23    set @count=@count+1 
 9图片 24end 
10图片 25set identity_insert tb_TestTable off

图片 26

 

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

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

 

图片 27

 1图片 28create procedure proc_paged_with_notin  –利用select top and select not in 
 2图片 29
 3图片 30    @pageIndex int,  –页索引 
 4图片 31    @pageSize int    –每页记录数 
 5图片 32
 6图片 33as 
 7图片 34begin 
 8图片 35    set nocount on; 
 9图片 36    declare @timediff datetime –耗时 
10图片 37    declare @sql nvarchar(500) 
11图片 38    select @timediff=Getdate() 
12图片 39    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图片 40    execute(@sql)  –因select top后不支技直接接参数,所以写成了字符串@sql 
14图片 41    select datediff(ms,@timediff,GetDate()) as 耗时 
15图片 42    set nocount off; 
16图片 43end

图片 44

 

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

 

 

图片 45

 1图片 46create procedure proc_paged_with_selectMax  –利用select top and select max(列) 
 2图片 47
 3图片 48    @pageIndex int,  –页索引 
 4图片 49    @pageSize int    –页记录数 
 5图片 50
 6图片 51as 
 7图片 52begin 
 8图片 53set nocount on; 
 9图片 54    declare @timediff datetime 
10图片 55    declare @sql nvarchar(500) 
11图片 56    select @timediff=Getdate() 
12图片 57    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图片 58    execute(@sql) 
14图片 59    select datediff(ms,@timediff,GetDate()) as 耗时 
15图片 60set nocount off; 
16图片 61end

图片 62

 

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

 

 

图片 63

 1图片 64create procedure proc_paged_with_Midvar  –利用ID>最大ID值和中间变量 
 2图片 65
 3图片 66    @pageIndex int, 
 4图片 67    @pageSize int 
 5图片 68
 6图片 69as 
 7图片 70    declare @count int 
 8图片 71    declare @ID int 
 9图片 72    declare @timediff datetime 
10图片 73    declare @sql nvarchar(500) 
11图片 74begin 
12图片 75set nocount on; 
13图片 76    select @count=0,@ID=0,@timediff=getdate() 
14图片 77    select @count=@count+1,@ID=case when @count<=@pageSize*@pageIndex then ID else @ID end from tb_testTable order by id 
15图片 78    set @sql=’select top ‘+str(@pageSize)+’ * from tb_testTable where ID>’+str(@ID) 
16图片 79    execute(@sql) 
17图片 80    select datediff(ms,@timediff,getdate()) as 耗时 
18图片 81set nocount off; 
19图片 82end
20图片 83

图片 84

 

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

 

 

 

图片 85

 1图片 86create procedure proc_paged_with_Rownumber  –利用SQL 2005中的Row_number() 
 2图片 87
 3图片 88    @pageIndex int, 
 4图片 89    @pageSize int 
 5图片 90
 6图片 91as 
 7图片 92    declare @timediff datetime 
 8图片 93begin 
 9图片 94set nocount on; 
10图片 95    select @timediff=getdate() 
11图片 96    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图片 97    select datediff(ms,@timediff,getdate()) as 耗时 
13图片 98set nocount off; 
14图片 99end
15图片 100

图片 101

5,利用临时表及Row_number

 

 

图片 102

 1图片 103create procedure proc_CTE  –利用临时表及Row_number 
 2图片 104
 3图片 105    @pageIndex int,  –页索引 
 4图片 106    @pageSize int    –页记录数 
 5图片 107
 6图片 108as 
 7图片 109    set nocount on; 
 8图片 110    declare @ctestr nvarchar(400) 
 9图片 111    declare @strSql nvarchar(400) 
10图片 112    declare @datediff datetime 
11图片 113begin 
12图片 114    select @datediff=GetDate() 
13图片 115    set @ctestr=’with Table_CTE as 
14图片 116                (select ceiling((Row_number() over(order by ID ASC))/’+str(@pageSize)+’) as page_num,* from tb_TestTable)’; 
15图片 117    set @strSql=@ctestr+’ select * From Table_CTE where page_num=’+str(@pageIndex) 
16图片 118end 
17图片 119    begin 
18图片 120        execute sp_executesql @strSql 
19图片 121        select datediff(ms,@datediff,GetDate()) 
20图片 122    set nocount off; 
21图片 123    end
22图片 124

图片 125

 

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

 

 

测试结果显示:select max >row_number>not in>临时表>中间变量

 

于是我对效率最高的select
max方法用2分法进行了扩展,代码取自互联网,我修改了ASC排序时取不到值的BUG,测试结果:

 

2分法 156ms 156ms 180ms 470ms 156ms 1*

 

从测试结果来看,使用2分法确实可以提高效率并使效率更为稳定,我又增加了第159999页的测试,用时仅296ms,效果相当的不错!

 

下面是2分法使用select max的代码,已相当完善。

 

 

图片 126

  1图片 127–/*—–存储过程 分页处理 孙伟 2005-03-28创建 ——-*/ 
  2图片 128–/*—–存储过程 分页处理 浪尘 2008-9-1修改———-*/ 
  3图片 129–/*—– 对数据进行了2分处理使查询前半部分数据与查询后半部分数据性能相同 ——-*/ 
  4图片 130
  5图片 131alter PROCEDURE proc_paged_2part_selectMax 
  6图片 132
  7图片 133@tblName     nvarchar(200),        —-要显示的表或多个表的连接 
  8图片 134@fldName     nvarchar(500) = ‘*’,    —-要显示的字段列表 
  9图片 135@pageSize    int = 10,        —-每页显示的记录个数 
 10图片 136@page        int = 1,        —-要显示那一页的记录 
 11图片 137@fldSort    nvarchar(200) = null,    —-排序字段列表或条件 
 12图片 138@Sort        bit = 0,        —-排序方法,0为升序,1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)–程序传参如:’ SortA Asc,SortB Desc,SortC ‘) 
 13图片 139@strCondition    nvarchar(1000) = null,    —-查询条件,不需where 
 14图片 140@ID        nvarchar(150),        —-主表的主键 
 15图片 141@Dist                 bit = 0,           —-是否添加查询字段的 DISTINCT 默认0不添加/1添加 
 16图片 142@pageCount    int = 1 output,            —-查询结果分页后的总页数 
 17图片 143@Counts    int = 1 output                —-查询到的记录数 
 18图片 144
 19图片 145AS 
 20图片 146SET NOCOUNT ON 
 21图片 147Declare @sqlTmp nvarchar(1000)        —-存放动态生成的SQL语句 
 22图片 148Declare @strTmp nvarchar(1000)        —-存放取得查询结果总数的查询语句 
 23图片 149Declare @strID     nvarchar(1000)        —-存放取得查询开头或结尾ID的查询语句 
 24图片 150
 25图片 151Declare @strSortType nvarchar(10)    —-数据排序规则A 
 26图片 152Declare @strFSortType nvarchar(10)    —-数据排序规则B 
 27图片 153
 28图片 154Declare @SqlSelect nvarchar(50)         —-对含有DISTINCT的查询进行SQL构造 
 29图片 155Declare @SqlCounts nvarchar(50)          —-对含有DISTINCT的总数查询进行SQL构造 
 30图片 156
 31图片 157declare @timediff datetime  –耗时测试时间差 
 32图片 158select @timediff=getdate() 
 33图片 159
 34图片 160if @Dist  = 0 
 35图片 161begin 
 36图片 162    set @SqlSelect = ‘select ‘ 
 37图片 163    set @SqlCounts = ‘Count(*)’ 
 38图片 164end 
 39图片 165else 
 40图片 166begin 
 41图片 167    set @SqlSelect = ‘select distinct ‘ 
 42图片 168    set @SqlCounts = ‘Count(DISTINCT ‘+@ID+’)’ 
 43图片 169end 
 44图片 170
 45图片 171
 46图片 172if @Sort=0 
 47图片 173begin 
 48图片 174    set @strFSortType=’ ASC ‘ 
 49图片 175    set @strSortType=’ DESC ‘ 
 50图片 176end 
 51图片 177else 
 52图片 178begin 
 53图片 179    set @strFSortType=’ DESC ‘ 
 54图片 180    set @strSortType=’ ASC ‘ 
 55图片 181end 
 56图片 182
 57图片 183
 58图片 184
 59图片 185——–生成查询语句——– 
 60图片 186–此处@strTmp为取得查询结果数量的语句 
 61图片 187if @strCondition is null or @strCondition=”     –没有设置显示条件 
 62图片 188begin 
 63图片 189    set @sqlTmp =  @fldName + ‘ From ‘ + @tblName 
 64图片 190    set @strTmp = @SqlSelect+’ @Counts=’+@SqlCounts+’ FROM ‘+@tblName 
 65图片 191    set @strID = ‘ From ‘ + @tblName 
 66图片 192end 
 67图片 193else 
 68图片 194begin 
 69图片 195    set @sqlTmp = + @fldName + ‘From ‘ + @tblName + ‘ where (1>0) ‘ + @strCondition 
 70图片 196    set @strTmp = @SqlSelect+’ @Counts=’+@SqlCounts+’ FROM ‘+@tblName + ‘ where (1>0) ‘ + @strCondition 
 71图片 197    set @strID = ‘ From ‘ + @tblName + ‘ where (1>0) ‘ + @strCondition 
 72图片 198end 
 73图片 199
 74图片 200—-取得查询结果总数量—– 
 75图片 201exec sp_executesql @strTmp,N’@Counts int out ‘,@Counts out 
 76图片 202declare @tmpCounts int 
 77图片 203if @Counts = 0 
 78图片 204    set @tmpCounts = 1 
 79图片 205else 
 80图片 206    set @tmpCounts = @Counts 
 81图片 207
 82图片 208    –取得分页总数 
 83图片 209    set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize 
 84图片 210
 85图片 211    /**//**当前页大于总页数 取最后一页**/ 
 86图片 212    if @page>@pageCount 
 87图片 213        set @page=@pageCount 
 88图片 214
 89图片 215    –/*—–数据分页2分处理——-*/ 
 90图片 216    declare @pageIndex int –总数/页大小 
 91图片 217    declare @lastcount int –总数%页大小  
 92图片 218
 93图片 219    set @pageIndex = @tmpCounts/@pageSize 
 94图片 220    set @lastcount = @tmpCounts%@pageSize 
 95图片 221    if @lastcount > 0 
 96图片 222        set @pageIndex = @pageIndex + 1 
 97图片 223    else 
 98图片 224        set @lastcount = @pagesize 
 99图片 225
100图片 226    –//***显示分页 
101图片 227    if @strCondition is null or @strCondition=”     –没有设置显示条件 
102图片 228    begin 
103图片 229        if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2   –前半部分数据处理 
104图片 230            begin  
105图片 231                if @page=1 
106图片 232                    set @strTmp=@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName                         
107图片 233                        +’ order by ‘+ @fldSort +’ ‘+ @strFSortType 
108图片 234                else 
109图片 235                begin 
110图片 236                    if @Sort=1 
111图片 237                    begin                     
112图片 238                    set @strTmp=@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName 
113图片 239                        +’ where ‘+@ID+’ <(select min(‘+ @ID +’) from (‘+ @SqlSelect+’ top ‘+ CAST(@pageSize*(@page-1) as Varchar(20)) +’ ‘+ @ID +’ from ‘+@tblName 
114图片 240                        +’ order by ‘+ @fldSort +’ ‘+ @strFSortType+’) AS TBMinID)’ 
115图片 241                        +’ order by ‘+ @fldSort +’ ‘+ @strFSortType 
116图片 242                    end 
117图片 243                    else 
118图片 244                    begin 
119图片 245                    set @strTmp=@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName 
120图片 246                        +’ where ‘+@ID+’ >(select max(‘+ @ID +’) from (‘+ @SqlSelect+’ top ‘+ CAST(@pageSize*(@page-1) as Varchar(20)) +’ ‘+ @ID +’ from ‘+@tblName 
121图片 247                        +’ order by ‘+ @fldSort +’ ‘+ @strFSortType+’) AS TBMinID)’ 
122图片 248                        +’ order by ‘+ @fldSort +’ ‘+ @strFSortType  
123图片 249                    end 
124图片 250                end     
125图片 251            end 
126图片 252        else 
127图片 253            begin 
128图片 254            set @page = @pageIndex-@page+1 –后半部分数据处理 
129图片 255                if @page <= 1 –最后一页数据显示                 
130图片 256                    set @strTmp=@SqlSelect+’ * from (‘+@SqlSelect+’ top ‘+ CAST(@lastcount as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName 
131图片 257                        +’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TempTB’+’ order by ‘+ @fldSort +’ ‘+ @strFSortType  
132图片 258                else 
133图片 259                    if @Sort=1 
134图片 260                    begin 
135图片 261                    set @strTmp=@SqlSelect+’ * from (‘+@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName 
136图片 262                        +’ where ‘+@ID+’ >(select max(‘+ @ID +’) from(‘+ @SqlSelect+’ top ‘+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +’ ‘+ @ID +’ from ‘+@tblName 
137图片 263                        +’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TBMaxID)’ 
138图片 264                        +’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TempTB’+’ order by ‘+ @fldSort +’ ‘+ @strFSortType 
139图片 265                    end 
140图片 266                    else 
141图片 267                    begin 
142图片 268                    set @strTmp=@SqlSelect+’ * from (‘+@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName 
143图片 269                        +’ where ‘+@ID+’ <(select min(‘+ @ID +’) from(‘+ @SqlSelect+’ top ‘+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +’ ‘+ @ID +’ from ‘+@tblName 
144图片 270                        +’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TBMaxID)’ 
145图片 271                        +’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TempTB’+’ order by ‘+ @fldSort +’ ‘+ @strFSortType  
146图片 272                    end 
147图片 273            end 
148图片 274    end 
149图片 275
150图片 276    else –有查询条件 
151图片 277    begin 
152图片 278        if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2   –前半部分数据处理 
153图片 279        begin 
154图片 280                if @page=1 
155图片 281                    set @strTmp=@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName                         
156图片 282                        +’ where 1=1 ‘ + @strCondition + ‘ order by ‘+ @fldSort +’ ‘+ @strFSortType 
157图片 283                else if(@Sort=1) 
158图片 284                begin                     
159图片 285                    set @strTmp=@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName 
160图片 286                        +’ where ‘+@ID+’ <(select min(‘+ @ID +’) from (‘+ @SqlSelect+’ top ‘+ CAST(@pageSize*(@page-1) as Varchar(20)) +’ ‘+ @ID +’ from ‘+@tblName 
161图片 287                        +’ where (1=1) ‘ + @strCondition +’ order by ‘+ @fldSort +’ ‘+ @strFSortType+’) AS TBMinID)’ 
162图片 288                        +’ ‘+ @strCondition +’ order by ‘+ @fldSort +’ ‘+ @strFSortType 
163图片 289                end 
164图片 290                else 
165图片 291                begin 
166图片 292                    set @strTmp=@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName 
167图片 293                        +’ where ‘+@ID+’ >(select max(‘+ @ID +’) from (‘+ @SqlSelect+’ top ‘+ CAST(@pageSize*(@page-1) as Varchar(20)) +’ ‘+ @ID +’ from ‘+@tblName 
168图片 294                        +’ where (1=1) ‘ + @strCondition +’ order by ‘+ @fldSort +’ ‘+ @strFSortType+’) AS TBMinID)’ 
169图片 295                        +’ ‘+ @strCondition +’ order by ‘+ @fldSort +’ ‘+ @strFSortType  
170图片 296                end            
171图片 297        end 
172图片 298        else 
173图片 299        begin  
174图片 300            set @page = @pageIndex-@page+1 –后半部分数据处理 
175图片 301            if @page <= 1 –最后一页数据显示 
176图片 302                    set @strTmp=@SqlSelect+’ * from (‘+@SqlSelect+’ top ‘+ CAST(@lastcount as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName 
177图片 303                        +’ where (1=1) ‘+ @strCondition +’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TempTB’+’ order by ‘+ @fldSort +’ ‘+ @strFSortType                      
178图片 304            else if(@Sort=1) 
179图片 305                    set @strTmp=@SqlSelect+’ * from (‘+@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName 
180图片 306                        +’ where ‘+@ID+’ >(select max(‘+ @ID +’) from(‘+ @SqlSelect+’ top ‘+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +’ ‘+ @ID +’ from ‘+@tblName 
181图片 307                        +’ where (1=1) ‘+ @strCondition +’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TBMaxID)’ 
182图片 308                        +’ ‘+ @strCondition+’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TempTB’+’ order by ‘+ @fldSort +’ ‘+ @strFSortType     
183图片 309            else 
184图片 310                    set @strTmp=@SqlSelect+’ * from (‘+@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName 
185图片 311                        +’ where ‘+@ID+’ <(select min(‘+ @ID +’) from(‘+ @SqlSelect+’ top ‘+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +’ ‘+ @ID +’ from ‘+@tblName 
186图片 312                        +’ where (1=1) ‘+ @strCondition +’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TBMaxID)’ 
187图片 313                        +’ ‘+ @strCondition+’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TempTB’+’ order by ‘+ @fldSort +’ ‘+ @strFSortType             
188图片 314        end     
189图片 315    end 
190图片 316
191图片 317——返回查询结果—– 
192图片 318exec sp_executesql @strTmp 
193图片 319select datediff(ms,@timediff,getdate()) as 耗时 
194图片 320–print @strTmp 
195图片 321SET NOCOUNT OFF 
196图片 322GO
197图片 323

图片 324

发表评论

电子邮件地址不会被公开。 必填项已用*标注