忻州谜靠工贸有限公司_适合千万数据查询分页操作的一个通用存储过程

一、引言

?????????????? 最近上班比较忙,所以就很少写东西了,MongoDB系列的文章也要拖后了,没办法,工作第一,没工作就没饭吃了。今天正好的有点时间,就把我最近搞得一些东西,记录下来。

?????????????? 在软件行业,稍微大一点的公司,相关数据的存储量就可能会很大,当我们做系统的时候,一定会使用存储过程进行分页显示,至于为什么分页显示,就不用我多说了吧。最近我在做一个系统,为了让系统支持大数据量,做了很多测试,主要是测试各种分页算法的优劣。最后,经过自己的多番测试,确定了这个存储过程。测试环境是:
????????????????????????????????????????????? 硬件环境:CPU是12核的,内存是8G的大小,
????????????????????????????????????????????? 软件环境:Visual Studio 2015,数据库是 Sql Server 2008 R2,数据量大概有3千万左右
????????????????????????????????????????????? ORM环境:IBatis.Net
?
?????????????? 测试效果就不贴图了,如果大家想去测试,可以自行操作,首先要说明的一点,如果针对ID主键进行分页查询,最多是300-400毫秒之间就可以把分页数据显示出来,无论是针对第一页还是最后一页显示时间都是差不多的。再者说,如果是针对其他字段的查询分页,必须针对该字段建立索引,查询速度也在毫秒之间可以完成,经过自己的多轮测试,确定了最终的存储过程的样式。当然如果针对查询的分页的字段没有索引页可以查询出来,时间就比较长了。6分钟,10分钟都有可能。由于今天只讨论存储过程,其他的就不说了,比如分库,分表,水平拆分或者垂直拆分等知识。

二、存储过程代码

????????????? 当然,这个存储过程也参考了很多前辈的资料,才最终确定的。修改了其原有的Bug,和语法山的一些错误。其他细节也做了一些微调,形成了这个最后的版本,如果大家有更好的写法,也欢迎大家留言讨论。废话不多说了,今天就把代码发出来,该存储过程已经经过测试,完全没有问题,大家可以放心使用,代码如下:

yi yin yan?????????????? zui jin shang ban bi jiao mang, suo yi jiu hen shao xie dong xi le, MongoDB xi lie de wen zhang ye yao tuo hou le, mei ban fa, gong zuo di yi, mei gong zuo jiu mei fan chi le. jin tian zheng hao de you dian shi jian, jiu ba wo zui jin gao de yi xie dong xi, ji lu xia lai.?????????????? zai ruan jian hang ye, shao wei da yi dian de gong si, xiang guan shu ju de cun chu liang jiu ke neng hui hen da, dang wo men zuo xi tong de shi hou, yi ding hui shi yong cun chu guo cheng jin xing fen ye xian shi, zhi yu wei shen me fen ye xian shi, jiu bu yong wo duo shuo le ba. zui jin wo zai zuo yi ge xi tong, wei le rang xi tong zhi chi da shu ju liang, zuo le hen duo ce shi, zhu yao shi ce shi ge zhong fen ye suan fa de you lie. zui hou, jing guo zi ji de duo fan ce shi, que ding le zhe ge cun chu guo cheng. ce shi huan jing shi:????????????????????????????????????????????? ying jian huan jing: CPU shi 12 he de, nei cun shi 8G de da xiao,????????????????????????????????????????????? ruan jian huan jing: Visual Studio 2015, shu ju ku shi Sql Server 2008 R2, shu ju liang da gai you 3 qian wan zuo you????????????????????????????????????????????? ORM huan jing: IBatis. Net??????????????? ce shi xiao guo jiu bu tie tu le, ru guo da jia xiang qu ce shi, ke yi zi xing cao zuo, shou xian yao shuo ming de yi dian, ru guo zhen dui ID zhu jian jin xing fen ye cha xun, zui duo shi 300400 hao miao zhi jian jiu ke yi ba fen ye shu ju xian shi chu lai, wu lun shi zhen dui di yi ye hai shi zui hou yi ye xian shi shi jian dou shi cha bu duo de. zai zhe shuo, ru guo shi zhen dui qi ta zi duan de cha xun fen ye, bi xu zhen dui gai zi duan jian li suo yin, cha xun su du ye zai hao miao zhi jian ke yi wan cheng, jing guo zi ji de duo lun ce shi, que ding le zui zhong de cun chu guo cheng de yang shi. dang ran ru guo zhen dui cha xun de fen ye de zi duan mei you suo yin ye ke yi cha xun chu lai, shi jian jiu bi jiao chang le. 6 fen zhong, 10 fen zhong dou you ke neng. you yu jin tian zhi tao lun cun chu guo cheng, qi ta de jiu bu shuo le, bi ru fen ku, fen biao, shui ping chai fen huo zhe chui zhi chai fen deng zhi shi. er cun chu guo cheng dai ma????????????? dang ran, zhe ge cun chu guo cheng ye can kao le hen duo qian bei de zi liao, cai zui zhong que ding de. xiu gai le qi yuan you de Bug, he yu fa shan de yi xie cuo wu. qi ta xi jie ye zuo le yi xie wei tiao, xing cheng le zhe ge zui hou de ban ben, ru guo da jia you geng hao de xie fa, ye huan ying da jia liu yan tao lun. fei hua bu duo shuo le, jin tian jiu ba dai ma fa chu lai, gai cun chu guo cheng yi jing jing guo ce shi, wan quan mei you wen ti, da jia ke yi fang xin shi yong, dai ma ru xia:

  1 USE [XXXXXXXX.BbbbbbbbCcccccccc.Dataware]
  2 GO
  3 /****** Object:  StoredProcedure [dbo].[usp_CommonDataResourcePaged]    Script Date: 2018/4/20 9:01:14 ******/
  4 SET ANSI_NULLS ON
  5 GO
  6 SET QUOTED_IDENTIFIER ON
  7 GO
  8 -- =============================================
  9 -- Author:        
 10 -- Create date: <2018-4-16 14:21>
 11 -- Description:    <通用的数据分页存储过程>
 12 -- =============================================
 13 ALTER PROCEDURE [dbo].[usp_CommonDataResourcePaged]
 14 (
 15     @TableName          nvarchar(200),           ----要显示的表或多个表的连接
 16     @FieldList          nvarchar(1500) = "*",    ----要显示的字段列表
 17     @PageSize           int = 20,                ----每页显示的记录个数
 18     @PageNumber         int = 1,                 ----要显示那一页的记录
 19     @SortFields         nvarchar(1000) = null,   ----排序字段列表或条件
 20     @EnabledSort        bit = 0,                 ----排序方法,0为升序,1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如:" SortA Asc,SortB Desc,SortC ")
 21     @QueryCondition     nvarchar(1500) = null,   ----查询条件,不需WHERE
 22     @Primarykey         nvarchar(50),            ----主表的主键
 23     @EnabledDistinct    bit = 0,                 ----是否添加查询字段的 DISTINCT 默认0不添加/1添加
 24     @PageCount          int = 1 output,          ----查询结果分页后的总页数
 25     @RecordCount        int = 1 output           ----查询到的记录数
 26 )
 27 AS
 28     SET NOCOUNT ON
 29     Declare @SqlResult nvarchar(1000)        ----存放动态生成的SQL语句
 30     Declare @SqlTotalCount nvarchar(1000)        ----存放取得查询结果总数的查询语句
 31     Declare @SqlStartOrEndID     nvarchar(1000)        ----存放取得查询开头或结尾ID的查询语句
 32     
 33     Declare @SortTypeA nvarchar(10)    ----数据排序规则A
 34     Declare @SortTypeB nvarchar(10)    ----数据排序规则B
 35     
 36     Declare @SqlDistinct nvarchar(50)         ----对含有DISTINCT的查询进行SQL构造
 37     Declare @SqlCountDistinct nvarchar(50)          ----对含有DISTINCT的总数查询进行SQL构造
 38     
 39     declare @timediff datetime  --耗时测试时间差
 40     SELECT @timediff=getdate()
 41     
 42     if @EnabledDistinct  = 0
 43         begin
 44             set @SqlDistinct = "SELECT "
 45             set @SqlCountDistinct = "Count(*)"
 46         end
 47     else
 48         begin
 49             set @SqlDistinct = "SELECT DISTINCT "
 50             set @SqlCountDistinct = "Count(DISTINCT "+@Primarykey+")"
 51         end
 52     
 53     if @EnabledSort=0
 54         begin
 55             set @SortTypeB=" ASC "
 56             set @SortTypeA=" DESC "
 57         end
 58     else
 59         begin
 60             set @SortTypeB=" DESC "
 61             set @SortTypeA=" ASC "
 62         end
 63     
 64     --------生成查询语句--------
 65     --此处@SqlTotalCount为取得查询结果数量的语句
 66     if @QueryCondition is null or @QueryCondition=""     --没有设置显示条件
 67         begin
 68             set @SqlResult =  @FieldList + " From " + @TableName
 69             set @SqlTotalCount = @SqlDistinct+" @RecordCount="+@SqlCountDistinct+" FROM "+@TableName
 70             set @SqlStartOrEndID = " From " + @TableName
 71         end
 72     else
 73         begin
 74             set @SqlResult = + @FieldList + " From " + @TableName + " WHERE (1>0) and " + @QueryCondition
 75             set @SqlTotalCount = @SqlDistinct+" @RecordCount="+@SqlCountDistinct+" FROM "+@TableName + " WHERE (1>0) and " + @QueryCondition
 76             set @SqlStartOrEndID = " From " + @TableName + " WHERE (1>0) and " + @QueryCondition
 77         end
 78     
 79     ----取得查询结果总数量-----
 80     exec sp_executesql @SqlTotalCount,N"@RecordCount int out ",@RecordCount out
 81 
 82     declare @TemporaryCount int --临时统计
 83     if @RecordCount = 0
 84         set @TemporaryCount = 1
 85     else
 86         set @TemporaryCount = @RecordCount
 87     
 88         --取得分页总数
 89         set @PageCount=(@TemporaryCount+@PageSize-1)/@PageSize
 90     
 91         /**当前页大于总页数 取最后一页**/
 92         if @PageNumber>@PageCount
 93             set @PageNumber=@PageCount
 94     
 95         --/*-----数据分页2分处理-------*/
 96         declare @pageIndex int --总数/页大小
 97         declare @lastcount int --总数%页大小 
 98     
 99         set @pageIndex = @TemporaryCount/@PageSize
100         set @lastcount = @TemporaryCount%@PageSize
101         if @lastcount > 0
102             set @pageIndex = @pageIndex + 1
103         else
104             set @lastcount = @pagesize
105     
106         --//***显示分页
107         if @QueryCondition is null or @QueryCondition=""     --没有设置显示条件
108         begin
109             if @pageIndex<2 or @PageNumber<=@pageIndex / 2 + @pageIndex % 2   --前半部分数据处理
110                 begin 
111                     if @PageNumber=1
112                         set @SqlTotalCount=@SqlDistinct+" TOP "+ CAST(@PageSize as VARCHAR(4))+" "+ @FieldList+" FROM "+@TableName+" ORDER BY "+ @SortFields +" "+ @SortTypeB
113                     else
114                     begin
115                         if @EnabledSort=1
116                         begin                    
117                         set @SqlTotalCount=@SqlDistinct+" TOP "+ CAST(@PageSize as VARCHAR(4))+" "+ @FieldList+" FROM "+@TableName
118                             +" WHERE "+@Primarykey+" <(SELECT MIN("+ @Primarykey +") FROM ("+ @SqlDistinct+" TOP "+ CAST(@PageSize*(@PageNumber-1) as Varchar(20)) +" "+ @Primarykey +" FROM "+@TableName
119                             +" ORDER BY "+ @SortFields +" "+ @SortTypeB+") AS TBMinID)"+" ORDER BY "+ @SortFields +" "+ @SortTypeB
120                         end
121                         else
122                         begin
123                         set @SqlTotalCount=@SqlDistinct+" TOP "+ CAST(@PageSize as VARCHAR(4))+" "+ @FieldList+" FROM "+@TableName
124                             +" WHERE "+@Primarykey+" >(SELECT MAX("+ @Primarykey +") FROM ("+ @SqlDistinct+" TOP "+ CAST(@PageSize*(@PageNumber-1) as Varchar(20)) +" "+ @Primarykey +" FROM "+@TableName
125                             +" ORDER BY "+ @SortFields +" "+ @SortTypeB+") AS TBMinID)"+" ORDER BY "+ @SortFields +" "+ @SortTypeB 
126                         end
127                     end    
128                 end
129             else
130                 begin
131                 set @PageNumber = @pageIndex-@PageNumber+1 --后半部分数据处理
132                     if @PageNumber <= 1 --最后一页数据显示                
133                         set @SqlTotalCount=@SqlDistinct+" * FROM ("+@SqlDistinct+" TOP "+ CAST(@lastcount as VARCHAR(4))+" "+ @FieldList+" FROM "+@TableName
134                             +" ORDER BY "+ @SortFields +" "+ @SortTypeA+") AS TempTB"+" ORDER BY "+ @SortFields +" "+ @SortTypeB 
135                     else
136                         if @EnabledSort=1
137                         begin
138                         set @SqlTotalCount=@SqlDistinct+" * FROM ("+@SqlDistinct+" TOP "+ CAST(@PageSize as VARCHAR(4))+" "+ @FieldList+" FROM "+@TableName
139                             +" WHERE "+@Primarykey+" >(SELECT MAX("+ @Primarykey +") FROM("+ @SqlDistinct+" TOP "+ CAST(@PageSize*(@PageNumber-2)+@lastcount as Varchar(20)) +" "+ @Primarykey +" FROM "+@TableName
140                             +" ORDER BY "+ @SortFields +" "+ @SortTypeA+") AS TBMaxID)"
141                             +" ORDER BY "+ @SortFields +" "+ @SortTypeA+") AS TempTB"+" ORDER BY "+ @SortFields +" "+ @SortTypeB
142                         end
143                         else
144                         begin
145                         set @SqlTotalCount=@SqlDistinct+" * FROM ("+@SqlDistinct+" TOP "+ CAST(@PageSize as VARCHAR(4))+" "+ @FieldList+" FROM "+@TableName
146                             +" WHERE "+@Primarykey+" <(SELECT MIN("+ @Primarykey +") FROM("+ @SqlDistinct+" TOP "+ CAST(@PageSize*(@PageNumber-2)+@lastcount as Varchar(20)) +" "+ @Primarykey +" FROM "+@TableName
147                             +" ORDER BY "+ @SortFields +" "+ @SortTypeA+") AS TBMaxID)"
148                             +" ORDER BY "+ @SortFields +" "+ @SortTypeA+") AS TempTB"+" ORDER BY "+ @SortFields +" "+ @SortTypeB 
149                         end
150                 end
151         end
152     
153         else --有查询条件
154         begin
155             if @pageIndex<2 or @PageNumber<=@pageIndex / 2 + @pageIndex % 2   --前半部分数据处理
156             begin
157                     if @PageNumber=1
158                         set @SqlTotalCount=@SqlDistinct+" TOP "+ CAST(@PageSize as VARCHAR(4))+" "+ @FieldList+" FROM "+@TableName                        
159                             +" WHERE 1=1 and " + @QueryCondition + " ORDER BY "+ @SortFields +" "+ @SortTypeB
160                     else if(@EnabledSort=1)
161                     begin                    
162                         set @SqlTotalCount=@SqlDistinct+" TOP "+ CAST(@PageSize as VARCHAR(4))+" "+ @FieldList+" FROM "+@TableName
163                             +" WHERE "+@Primarykey+" <(SELECT MIN("+ @Primarykey +") FROM ("+ @SqlDistinct+" TOP "+ CAST(@PageSize*(@PageNumber-1) as Varchar(20)) +" "+ @Primarykey +" FROM "+@TableName
164                             +" WHERE (1=1) and " + @QueryCondition +" ORDER BY "+ @SortFields +" "+ @SortTypeB+") AS TBMinID)"+" ORDER BY "+ @SortFields +" "+ @SortTypeB
165                     end
166                     else
167                     begin
168                         set @SqlTotalCount=@SqlDistinct+" TOP "+ CAST(@PageSize as VARCHAR(4))+" "+ @FieldList+" FROM "+@TableName
169                             +" WHERE "+@Primarykey+" >(SELECT MAX("+ @Primarykey +") FROM ("+ @SqlDistinct+" TOP "+ CAST(@PageSize*(@PageNumber-1) as Varchar(20)) +" "+ @Primarykey +"

当前文章:http://www.werapps.com/7lce56/81773-507385-24619.html

发布时间:00:52:39

开奖直播??545444.com??www.51549.com??81444香港开奖现场直播??珠算盘??九宫禁生肖狗??百家博心水论坛??正版挂牌规律??1861图库看图区??784949刘伯温论坛??