我们首先创建一数据库,Test --创建数据库 data

  • 栏目:数据 时间:2020-04-17 21:40
<返回列表

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

 

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

 

测试环境

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

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

上一篇:(如果只给表中的部分字段插入值时 下一篇:没有了

更多阅读

我们首先创建一数据库,Test --创建数据库

数据 2020-04-17
几种SQLServer分页的存储过程写法以及性能比较存储过程的5种分页写法,下面的代码是从忘了什么...
查看全文

(如果只给表中的部分字段插入值时

数据 2020-04-17
delete语法单表语法:   delete [low_priority] [quick] [ignore] from tbl_name [wherewhere_definition] [order by ......
查看全文

sql中日期日期周末月末函数

数据 2020-04-17
文章介绍了在sql中计算月末和周末的sql语句函数,有需要了解的朋友可以参考一下。代码如下...
查看全文

友情链接: 网站地图

Copyright © 2015-2019 http://www.koi-bumi.com. 韦德体育有限公司 版权所有