美洲杯波胆_美洲杯波胆赔率
做最好的网站
来自 计算机教程 2019-05-30 11:10 的文章
当前位置: 美洲杯波胆 > 计算机教程 > 正文

美洲杯波胆:大数据量分页存储过程效率测试附

测试环境
硬件:CPU 酷睿双核T5750 内存:2G
软件:Windows server 2003 sql server 2005
OK,我们首先创建一数据库:data_Test,并在此数据库中创建一表:tb_TestTable

 

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

 

测试环境

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

复制代码 代码如下:

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

本文由美洲杯波胆发布于计算机教程,转载请注明出处:美洲杯波胆:大数据量分页存储过程效率测试附

关键词: