澳门金沙vip运气据量分页存款和储蓄进度效用测量检验附测量试验代码与结果

代码修改集中在类似复制代码 代码如下:if
@Sort=0set @strTmp = @strTmp + ‘(select min(‘elseset @strTmp = @strTmp +
‘(select max(‘

 

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

 

测试环境

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

软件:Windows server 2003    +   Sql server 2005

 

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

 

澳门金沙vip 1

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

澳门金沙vip 14

 

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

 

澳门金沙vip 15

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

澳门金沙vip 26

 

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

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

 

澳门金沙vip 27

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

澳门金沙vip 44

 

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

 

 

澳门金沙vip 45

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

澳门金沙vip 62

 

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

 

 

澳门金沙vip 63

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

澳门金沙vip 84

 

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

 

 

 

澳门金沙vip 85

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

澳门金沙vip 101

5,利用临时表及Row_number

 

 

澳门金沙vip 102

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

澳门金沙vip 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的代码,已相当完善。

 

 

澳门金沙vip 126

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

澳门金沙vip 324

测试环境 硬件: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项目中分页测试,测试项也比较单一,所以不够全面系统,但从其效率相比上,我们可以在数据库分页算法上进行有效的控制。

需要说明的是:这个存储过程参数比较多,我再实际使用中又在外面单独写了一个类,页面调用直接调用封装的类,方法有很多,主要是思路,大家可以参考下。