[转]Sql server 大数据量分页存储过程效率测试附代码

注:

[1]. 后台常住应用测试为属功能测试。

[2]. 单机应用可毫无考虑做契约测试。

[3].
异常测试包括弱网测试,比如低速网络信号、网络时断时续,网络切换以及无网等,突然断电等。

[4]. 其他硬件功能专项测试包括硬件功能关闭,硬件功能十分等。

[5].
用户测试包括收集用户用信息,并转移用户实际使用的测试用例来对系统开展测试。

3,利用select
top和中等变量–此方法为网上有人说效益最佳,所以贴出并测试

对运动采用,首先它实质上啊是软件系统,所以通用的软件测试方法技术都可动用。其次它又具有嵌入式的特性,比如开需要交叉编译、需要远距离调试、硬件资源相对不足等。所以移动采用之测试为时有发生夫殊的处,比如为亟需交叉编译、远程测试和各种硬件相关测试等。对应的动采用之测试策略及测试架构也有那个特殊性的处在。

OK,我们首先创建同数据库:data_Test,并当这个数据库中创造同阐明:tb_TestTable

第一我们拿走采用之限量限制于智能移动操作系统(比如Android、iOS、WinPhone等)上,包括手机采用,智能设备以等。

图片 1😉

image.png

图片 2😉

基本功测试

  • 效益测试 (Function Test)[1] 。
  • 合并测试(Integration Test )
  • 单元测试(Unit Test)
  • 契约测试(Contract Test)[2]

 

智能手机和智能装备的推广需要大量之使用来支撑。随着以数量的多,业务复杂度的增高,移动应用为越来越需要各种测试来确保应用与设备本身的不利和平静运行。因此活动使用测试的需求呢越好,大量关于走使用测试的图书应运而生,比如《Android移动性能实战》,《腾讯iOS测试实践》、《移动APP性能测评与优化》、《深入了解Android自动化测试》、《精通移动App测试实战:技术、工具和案例》等。

图片 3😉

这些书还介绍了汪洋底走采用测试实践,但是不管看有些本书,学习有些种测试方法、测试技术或者测试工具和框架,首先还是用上学并运用测试策略及测试架构。如果没在平等起制定好的测试策略及测试架构,而是盲目进行各种测试,很有或从倍功半。

 

制订测试架构

经过测试优先级表,我们获取了简易版的测试策略,然后便活该制定测试架构了。由于嵌入式软件之特殊性,其测试架构也和正常的桌面系统与服务器系统发生必然之区别。下图为对地方样列测试策略相对应之效用测试架构:

image.png

祈求中单对功能测试进行了更加的事无巨细架构设计,并无对另测试照集成测试、兼容性测试和平静测试相当开展详尽架构设计,感兴趣的读者可以因自己种之实际上情形要好尝试一下。

通过这个架构图,可以比较系统与直观的打听各种类型测试的分布、关系以及测试系统的架等。

然后配合测试优先级表就可比较好的指点团开展中之测试,比如制定更好的测试计划,制定更合乎之自动化测试系统等。并且还可以再使得的评估产品质量,比如什么项目的测试没有开,那么那些特定地方就存于高的风险。

但是其他软件系统还是在缺陷以及高风险的,关键是看这些弱点对于开发商与用户产生的影响发生差不多百般,风险是无是在可控范围外之。永远不要品味去找到有缺陷并消除,而是要从风险大小、影响程度等各国面综合考虑,增加集体对此产品质量的信念,并且永不对客户发出严重的不可开交范围的影响。

 

制定测试策略

自将走测试分为三种植档次,分别是基础测试、进阶测试和活测试,其中基础测试是成品能对并飞速交付的基本保障,扩展测试主要是为增进软件系统的健壮性,而活测试主要是经产品角度与用户角度去思辨要进行的测试。下面分别点数了广阔的老三栽类型测试。

测试环境

今天我们来谈谈移动测试的测试策略和测试架构。

图片 4😉

出品测试

  • 易用性测试(Usability Test)
  • A/B测试(A/B Test)
  • 产品在线测试(Product Verification Test or Product Online Test)
  • 用户测试(Customer Test)[5]

于一个中小型项目来讲,很多时资源都是甚简单的,很不便形成完美类型的测试,大型项目更是如此,更难发生足多之资源做有类型的测试。而且恐怕还由于组织人员的技术力量欠缺,或者所持有的测试相关的技巧栈的局限,以及支出测试环境和软件系统架构的限,有些种类的测试是力不从心展开的。

因而,制定测试策略的第一点在根据质量要求的优先级,并参考团队的各种限制来指定。

率先通过和PO、PM等开展座谈得产品质量需求的优先级,然后因优先级指定相应类别的测试。再因集团的资源、项目周期、技术能力及各种限制来制定相应的测试方法和测试技术,其中包括以自动化测试或手动测试、使用啊测试工具和测试框架、测试的限量以及水平等。

下表是一个典型手机用的测试策略表的样例(这个只是一个效仿项目的样表,真实项目蒙之各类信息应该更多,并且可因具体情况添加新列。并且注意,这些测试并不一定由测试人员或者QA来做,应该由一切集体一同搭档完成):

image.png

说明中的身分要求优先级的得到是一个较麻烦的过程,需要和一一利益相关者一起讨论并且协商得到。

据悉是测试优先级表,就明白应该将资源先投入到大优先级的测试着。等强优先级的测试完了组织可以领之品位后,再按照优先级做生一个品类的测试。这个表中的先行级在开过程中未是纯属免转移的。如果PO、PM等补益相关者对于产品质量需求的先级有了改观,在博团队同意后,还需要变更这表中的测试优先级。所以用时跟组织更新测试进度,并随即得到团队相继角色对测试和产品质量需求的申报及创新。

副可以根据测试金字塔抵模型来构思不同门类测试间的关联以及工作量,但是众多场面下啊得毫无参考这些测试模型,因为运动采用之复杂度一般不见面特意强,并且当前大部分情形下,移动采用被复杂的政工逻辑都见面尽可能在服务器端进行处理,所以移动采用很多时只是一个用户交互系统,所以理应尽量的形成会潜移默化用户用的E2E流程测试,然后再次持续召开任何类的测试。

然而于以活动使用中实现复杂工作的型,测试策略要该尽可能考虑测试项目中测试用例重复的题目,尽量避免重复的用例,降低测试成本。

遂我本着效率最高的select
max方法用2划分法开展了扩大,代码取自互联网,我修改了ASC排序时得不顶价值的BUG,测试结果:

进阶测试

  • 配合测试(Compatibility Test)
  • UI视觉测试(UI Visual Test)
  • 属性轮廓(Profiling)
  • 平安测试(Security Test)
  • 很测试(Exception Test)[3]
  • 猴子测试(Monkey Test)
  • 设置、升级跟卸载测试(Install、Upgrade and Uninstall Test)
  • 耐用测试(Endurance Test)
  • 耗电测试(Power Consumption Test)
  • 流量测试(Network Traffic Test)
  • 其他硬件功能专项测试[4]

 

 

 

图片 5😉

 

 

图片 6😉

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

 

 

 

软件:Windows server 2003    +   Sql server 2005

图片 19😉

 

 

 

图片 20😉

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

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

5,利用临时表及Row_number

 

当路遭到,我们常遇上或因此到分页,那么当深数据量(百万级以上)下,哪种分页算法效率最地道呢?我们不妨用事实摆。

图片 21😉

图片 22😉

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

 

 1图片 23create procedure proc_paged_with_selectMax  –利用select top and select max(列) 
 2图片 24
 3图片 25    @pageIndex int,  –页索引 
 4图片 26    @pageSize int    –页记录数 
 5图片 27
 6图片 28as 
 7图片 29begin 
 8图片 30set nocount on; 
 9图片 31    declare @timediff datetime 
10图片 32    declare @sql nvarchar(500) 
11图片 33    select @timediff=Getdate() 
12图片 34    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图片 35    execute(@sql) 
14图片 36    select datediff(ms,@timediff,GetDate()) as 耗时 
15图片 37set nocount off; 
16图片 38end

这种测试就以单机进行,并且没有于事实上支付WEB项目遭到分页测试,测试项为较单一,所以不够全面系统,但自从夫效率比上,我们可以当数据库分页算法上进展中的主宰。

 

图片 39😉

 

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

 

 

 

图片 40😉

 

图片 41😉

 

正文转自:http://www.cnblogs.com/lli0077/archive/2008/09/03/1282862.html

图片 42😉

自测试结果来拘禁,使用2分法确实可以提高效率并而效率进一步稳定,我还要增了第159999页的测试,用时才296ms,效果相当的没错!

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

 

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

下一场我们以数表中插入2000000久数:

 

OK,至此,存储过程创建了,我们分别于各页10漫长数的状下以第2页,第1000页,第10000页,第100000页,第199999页进行测试,耗时单位:ms 
每页测试5浅沾该平均值

 

施行示例:exec proc_paged_2part_selectMax
‘tb_testTable’,’ID,userName,userPWD,userEmail’,10,100000,’ID’,0,null,’ID’,0

 

 

 

 

图片 43😉

  1图片 44–/*—–存储过程 分页处理 孙伟 2005-03-28开立 ——-*/ 
  2图片 45–/*—–存储过程 分页处理 浪尘 2008-9-1改动———-*/ 
  3图片 46–/*—– 对数据开展了2私分处理要查询前半片数额及查询后半片段数据性相同 ——-*/ 
  4图片 47
  5图片 48alter PROCEDURE proc_paged_2part_selectMax 
  6图片 49
  7图片 50@tblName     nvarchar(200),        —-要显的发明要多个说明的连接 
  8图片 51@fldName     nvarchar(500) = ‘*’,    —-要显得的字段列表 
  9图片 52@pageSize    int = 10,        —-每页显示的记录个数 
 10图片 53@page        int = 1,        —-要显得那无异页的记录 
 11图片 54@fldSort    nvarchar(200) = null,    —-排序字段列表或条件 
 12图片 55@Sort        bit = 0,        —-排序方法,0为升序,1也降序(如果是大抵字段排列Sort指代末一个排序字段的排顺序(最后一个排序字段未加以排序标记)–程序传参如:’ SortA Asc,SortB Desc,SortC ‘) 
 13图片 56@strCondition    nvarchar(1000) = null,    —-查询条件,不需where 
 14图片 57@ID        nvarchar(150),        —-主表的主键 
 15图片 58@Dist                 bit = 0,           —-是否丰富查询字段的 DISTINCT 默认0不长/1补充加 
 16图片 59@pageCount    int = 1 output,            —-查询结果分页后底总页数 
 17图片 60@Counts    int = 1 output                —-查询到的记录数 
 18图片 61
 19图片 62AS 
 20图片 63SET NOCOUNT ON 
 21图片 64Declare @sqlTmp nvarchar(1000)        —-存放动态变化的SQL语句 
 22图片 65Declare @strTmp nvarchar(1000)        —-存放取得查询结果总数的询问语句 
 23图片 66Declare @strID     nvarchar(1000)        —-存放取得查询开头或最后ID的查询语句 
 24图片 67
 25图片 68Declare @strSortType nvarchar(10)    —-数据排序规则A 
 26图片 69Declare @strFSortType nvarchar(10)    —-数据排序规则B 
 27图片 70
 28图片 71Declare @SqlSelect nvarchar(50)         —-对含蓄DISTINCT的询问进行SQL构造 
 29图片 72Declare @SqlCounts nvarchar(50)          —-对含有DISTINCT的总和查询进行SQL构造 
 30图片 73
 31图片 74declare @timediff datetime  –耗时测试时差 
 32图片 75select @timediff=getdate() 
 33图片 76
 34图片 77if @Dist  = 0 
 35图片 78begin 
 36图片 79    set @SqlSelect = ‘select ‘ 
 37图片 80    set @SqlCounts = ‘Count(*)’ 
 38图片 81end 
 39图片 82else 
 40图片 83begin 
 41图片 84    set @SqlSelect = ‘select distinct ‘ 
 42图片 85    set @SqlCounts = ‘Count(DISTINCT ‘+@ID+’)’ 
 43图片 86end 
 44图片 87
 45图片 88
 46图片 89if @Sort=0 
 47图片 90begin 
 48图片 91    set @strFSortType=’ ASC ‘ 
 49图片 92    set @strSortType=’ DESC ‘ 
 50图片 93end 
 51图片 94else 
 52图片 95begin 
 53图片 96    set @strFSortType=’ DESC ‘ 
 54图片 97    set @strSortType=’ ASC ‘ 
 55图片 98end 
 56图片 99
 57图片 100
 58图片 101
 59图片 102——–生成查询语句——– 
 60图片 103–此处@strTmp为获查询结果数量之语句 
 61图片 104if @strCondition is null or @strCondition=”     –没有安装显示准 
 62图片 105begin 
 63图片 106    set @sqlTmp =  @fldName + ‘ From ‘ + @tblName 
 64图片 107    set @strTmp = @SqlSelect+’ @Counts=’+@SqlCounts+’ FROM ‘+@tblName 
 65图片 108    set @strID = ‘ From ‘ + @tblName 
 66图片 109end 
 67图片 110else 
 68图片 111begin 
 69图片 112    set @sqlTmp = + @fldName + ‘From ‘ + @tblName + ‘ where (1>0) ‘ + @strCondition 
 70图片 113    set @strTmp = @SqlSelect+’ @Counts=’+@SqlCounts+’ FROM ‘+@tblName + ‘ where (1>0) ‘ + @strCondition 
 71图片 114    set @strID = ‘ From ‘ + @tblName + ‘ where (1>0) ‘ + @strCondition 
 72图片 115end 
 73图片 116
 74图片 117—-获得查询结果到底数据—– 
 75图片 118exec sp_executesql @strTmp,N’@Counts int out ‘,@Counts out 
 76图片 119declare @tmpCounts int 
 77图片 120if @Counts = 0 
 78图片 121    set @tmpCounts = 1 
 79图片 122else 
 80图片 123    set @tmpCounts = @Counts 
 81图片 124
 82图片 125    –取得分页总数 
 83图片 126    set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize 
 84图片 127
 85图片 128    /**//**现阶段页大于总页数 取最后一页**/ 
 86图片 129    if @page>@pageCount 
 87图片 130        set @page=@pageCount 
 88图片 131
 89图片 132    –/*—–数据分页2私分处理——-*/ 
 90图片 133    declare @pageIndex int –总数/页大小 
 91图片 134    declare @lastcount int –总数%页大小  
 92图片 135
 93图片 136    set @pageIndex = @tmpCounts/@pageSize 
 94图片 137    set @lastcount = @tmpCounts%@pageSize 
 95图片 138    if @lastcount > 0 
 96图片 139        set @pageIndex = @pageIndex + 1 
 97图片 140    else 
 98图片 141        set @lastcount = @pagesize 
 99图片 142
100图片 143    –//***展示分页 
101图片 144    if @strCondition is null or @strCondition=”     –没有设置显示标准 
102图片 145    begin 
103图片 146        if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2   –前半有数量处理 
104图片 147            begin  
105图片 148                if @page=1 
106图片 149                    set @strTmp=@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName                         
107图片 150                        +’ order by ‘+ @fldSort +’ ‘+ @strFSortType 
108图片 151                else 
109图片 152                begin 
110图片 153                    if @Sort=1 
111图片 154                    begin                     
112图片 155                    set @strTmp=@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName 
113图片 156                        +’ where ‘+@ID+’ <(select min(‘+ @ID +’) from (‘+ @SqlSelect+’ top ‘+ CAST(@pageSize*(@page-1) as Varchar(20)) +’ ‘+ @ID +’ from ‘+@tblName 
114图片 157                        +’ order by ‘+ @fldSort +’ ‘+ @strFSortType+’) AS TBMinID)’ 
115图片 158                        +’ order by ‘+ @fldSort +’ ‘+ @strFSortType 
116图片 159                    end 
117图片 160                    else 
118图片 161                    begin 
119图片 162                    set @strTmp=@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName 
120图片 163                        +’ where ‘+@ID+’ >(select max(‘+ @ID +’) from (‘+ @SqlSelect+’ top ‘+ CAST(@pageSize*(@page-1) as Varchar(20)) +’ ‘+ @ID +’ from ‘+@tblName 
121图片 164                        +’ order by ‘+ @fldSort +’ ‘+ @strFSortType+’) AS TBMinID)’ 
122图片 165                        +’ order by ‘+ @fldSort +’ ‘+ @strFSortType  
123图片 166                    end 
124图片 167                end     
125图片 168            end 
126图片 169        else 
127图片 170            begin 
128图片 171            set @page = @pageIndex-@page+1 –后半片段数码处理 
129图片 172                if @page <= 1 –最后一页数据显示                 
130图片 173                    set @strTmp=@SqlSelect+’ * from (‘+@SqlSelect+’ top ‘+ CAST(@lastcount as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName 
131图片 174                        +’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TempTB’+’ order by ‘+ @fldSort +’ ‘+ @strFSortType  
132图片 175                else 
133图片 176                    if @Sort=1 
134图片 177                    begin 
135图片 178                    set @strTmp=@SqlSelect+’ * from (‘+@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName 
136图片 179                        +’ where ‘+@ID+’ >(select max(‘+ @ID +’) from(‘+ @SqlSelect+’ top ‘+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +’ ‘+ @ID +’ from ‘+@tblName 
137图片 180                        +’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TBMaxID)’ 
138图片 181                        +’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TempTB’+’ order by ‘+ @fldSort +’ ‘+ @strFSortType 
139图片 182                    end 
140图片 183                    else 
141图片 184                    begin 
142图片 185                    set @strTmp=@SqlSelect+’ * from (‘+@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName 
143图片 186                        +’ where ‘+@ID+’ <(select min(‘+ @ID +’) from(‘+ @SqlSelect+’ top ‘+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +’ ‘+ @ID +’ from ‘+@tblName 
144图片 187                        +’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TBMaxID)’ 
145图片 188                        +’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TempTB’+’ order by ‘+ @fldSort +’ ‘+ @strFSortType  
146图片 189                    end 
147图片 190            end 
148图片 191    end 
149图片 192
150图片 193    else –有查询条件 
151图片 194    begin 
152图片 195        if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2   –前半组成部分多少处理 
153图片 196        begin 
154图片 197                if @page=1 
155图片 198                    set @strTmp=@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName                         
156图片 199                        +’ where 1=1 ‘ + @strCondition + ‘ order by ‘+ @fldSort +’ ‘+ @strFSortType 
157图片 200                else if(@Sort=1) 
158图片 201                begin                     
159图片 202                    set @strTmp=@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName 
160图片 203                        +’ where ‘+@ID+’ <(select min(‘+ @ID +’) from (‘+ @SqlSelect+’ top ‘+ CAST(@pageSize*(@page-1) as Varchar(20)) +’ ‘+ @ID +’ from ‘+@tblName 
161图片 204                        +’ where (1=1) ‘ + @strCondition +’ order by ‘+ @fldSort +’ ‘+ @strFSortType+’) AS TBMinID)’ 
162图片 205                        +’ ‘+ @strCondition +’ order by ‘+ @fldSort +’ ‘+ @strFSortType 
163图片 206                end 
164图片 207                else 
165图片 208                begin 
166图片 209                    set @strTmp=@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName 
167图片 210                        +’ where ‘+@ID+’ >(select max(‘+ @ID +’) from (‘+ @SqlSelect+’ top ‘+ CAST(@pageSize*(@page-1) as Varchar(20)) +’ ‘+ @ID +’ from ‘+@tblName 
168图片 211                        +’ where (1=1) ‘ + @strCondition +’ order by ‘+ @fldSort +’ ‘+ @strFSortType+’) AS TBMinID)’ 
169图片 212                        +’ ‘+ @strCondition +’ order by ‘+ @fldSort +’ ‘+ @strFSortType  
170图片 213                end            
171图片 214        end 
172图片 215        else 
173图片 216        begin  
174图片 217            set @page = @pageIndex-@page+1 –后半有的数处理 
175图片 218            if @page <= 1 –最后一页数据显示 
176图片 219                    set @strTmp=@SqlSelect+’ * from (‘+@SqlSelect+’ top ‘+ CAST(@lastcount as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName 
177图片 220                        +’ where (1=1) ‘+ @strCondition +’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TempTB’+’ order by ‘+ @fldSort +’ ‘+ @strFSortType                      
178图片 221            else if(@Sort=1) 
179图片 222                    set @strTmp=@SqlSelect+’ * from (‘+@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName 
180图片 223                        +’ where ‘+@ID+’ >(select max(‘+ @ID +’) from(‘+ @SqlSelect+’ top ‘+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +’ ‘+ @ID +’ from ‘+@tblName 
181图片 224                        +’ where (1=1) ‘+ @strCondition +’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TBMaxID)’ 
182图片 225                        +’ ‘+ @strCondition+’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TempTB’+’ order by ‘+ @fldSort +’ ‘+ @strFSortType     
183图片 226            else 
184图片 227                    set @strTmp=@SqlSelect+’ * from (‘+@SqlSelect+’ top ‘+ CAST(@pageSize as VARCHAR(4))+’ ‘+ @fldName+’ from ‘+@tblName 
185图片 228                        +’ where ‘+@ID+’ <(select min(‘+ @ID +’) from(‘+ @SqlSelect+’ top ‘+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +’ ‘+ @ID +’ from ‘+@tblName 
186图片 229                        +’ where (1=1) ‘+ @strCondition +’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TBMaxID)’ 
187图片 230                        +’ ‘+ @strCondition+’ order by ‘+ @fldSort +’ ‘+ @strSortType+’) AS TempTB’+’ order by ‘+ @fldSort +’ ‘+ @strFSortType             
188图片 231        end     
189图片 232    end 
190图片 233
191图片 234——返回查询结果—– 
192图片 235exec sp_executesql @strTmp 
193图片 236select datediff(ms,@timediff,getdate()) as 耗时 
194图片 237–print @strTmp 
195图片 238SET NOCOUNT OFF 
196图片 239GO
197图片 240

 1图片 241–插入数据 
 2图片 242set identity_insert tb_TestTable on 
 3图片 243declare @count int 
 4图片 244set @count=1 
 5图片 245while @count<=2000000 
 6图片 246begin  
 7图片 247    insert into tb_TestTable(id,userName,userPWD,userEmail) values(@count,’admin’,’admin888′,’lli0077@yahoo.com.cn’) 
 8图片 248    set @count=@count+1 
 9图片 249end 
10图片 250set identity_insert tb_TestTable off

 

 1图片 251create procedure proc_paged_with_Midvar  –利用ID>最特别ID值和中等变量 
 2图片 252
 3图片 253    @pageIndex int, 
 4图片 254    @pageSize int 
 5图片 255
 6图片 256as 
 7图片 257    declare @count int 
 8图片 258    declare @ID int 
 9图片 259    declare @timediff datetime 
10图片 260    declare @sql nvarchar(500) 
11图片 261begin 
12图片 262set nocount on; 
13图片 263    select @count=0,@ID=0,@timediff=getdate() 
14图片 264    select @count=@count+1,@ID=case when @count<=@pageSize*@pageIndex then ID else @ID end from tb_testTable order by id 
15图片 265    set @sql=’select top ‘+str(@pageSize)+’ * from tb_testTable where ID>’+str(@ID) 
16图片 266    execute(@sql) 
17图片 267    select datediff(ms,@timediff,getdate()) as 耗时 
18图片 268set nocount off; 
19图片 269end
20图片 270

图片 271😉

 1图片 272create procedure proc_paged_with_notin  –利用select top and select not in 
 2图片 273
 3图片 274    @pageIndex int,  –页索引 
 4图片 275    @pageSize int    –每页记录数 
 5图片 276
 6图片 277as 
 7图片 278begin 
 8图片 279    set nocount on; 
 9图片 280    declare @timediff datetime –耗时 
10图片 281    declare @sql nvarchar(500) 
11图片 282    select @timediff=Getdate() 
12图片 283    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图片 284    execute(@sql)  –因select top后无支技直接对接参数,所以写成了字符串@sql 
14图片 285    select datediff(ms,@timediff,GetDate()) as 耗时 
15图片 286    set nocount off; 
16图片 287end

自家第一写了五独常因此存储过程:

存过 第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

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

 1图片 288create procedure proc_paged_with_Rownumber  –利用SQL 2005中的Row_number() 
 2图片 289
 3图片 290    @pageIndex int, 
 4图片 291    @pageSize int 
 5图片 292
 6图片 293as 
 7图片 294    declare @timediff datetime 
 8图片 295begin 
 9图片 296set nocount on; 
10图片 297    select @timediff=getdate() 
11图片 298    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图片 299    select datediff(ms,@timediff,getdate()) as 耗时 
13图片 300set nocount off; 
14图片 301end
15图片 302

 1图片 303create procedure proc_CTE  –利用临时表及Row_number 
 2图片 304
 3图片 305    @pageIndex int,  –页索引 
 4图片 306    @pageSize int    –页记录数 
 5图片 307
 6图片 308as 
 7图片 309    set nocount on; 
 8图片 310    declare @ctestr nvarchar(400) 
 9图片 311    declare @strSql nvarchar(400) 
10图片 312    declare @datediff datetime 
11图片 313begin 
12图片 314    select @datediff=GetDate() 
13图片 315    set @ctestr=’with Table_CTE as 
14图片 316                (select ceiling((Row_number() over(order by ID ASC))/’+str(@pageSize)+’) as page_num,* from tb_TestTable)’; 
15图片 317    set @strSql=@ctestr+’ select * From Table_CTE where page_num=’+str(@pageIndex) 
16图片 318end 
17图片 319    begin 
18图片 320        execute sp_executesql @strSql 
19图片 321        select datediff(ms,@datediff,GetDate()) 
20图片 322    set nocount off; 
21图片 323    end
22图片 324

 

发表评论

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