葡京网投哪个正规 > 新葡亰-数据 > 大数据量分页存储过程效率测试附代码,大数据量分页存储过程效率测试附测试代码与结果

原标题:大数据量分页存储过程效率测试附代码,大数据量分页存储过程效率测试附测试代码与结果

浏览次数:67 时间:2020-04-30

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

 

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

 

测试环境

硬件: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

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

------插入数据------ 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

几种SQL Server分页的存储过程写法以及性能比较存储过程的5种分页写法,下面的代码是从忘了什么时候从别人那Ctrl+C来的,所以仅仅作为收藏,希望作者看到不要喷我.------创建数据库教程data_Test -----create database 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

---2、利用select top 和 select max(列键)---

---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

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

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

---3、利用select top和中间变量--此方法因网上有人说效果最佳---

---5、利用临时表及Row_numbercreate 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

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

本文由葡京网投哪个正规发布于新葡亰-数据,转载请注明出处:大数据量分页存储过程效率测试附代码,大数据量分页存储过程效率测试附测试代码与结果

关键词:

上一篇:SQL中exists的使用方法,EXISTS的详细介绍

下一篇:按年份统计入职人数sql语句,以学生表为例