数据库存储过程分页显示

注:需要建立索引复制代码 代码如下:/*
经测试,在14483461条记录中查询第100000页,每页10条记录按升序和降序第一次时间均为0.47秒,第二次时间均为0.43秒,测试语法如下:
execGetRecordFromPagenews,newsid,10,100000
news为表名,newsid为关键字段,使用时请先对newsid建立索引。 */ /*
函数名称:GetRecordFromPage 函数功能:获取指定页的数据
参数说明:@tblName包含数据的表名 @fldName关键字段名 @PageSize每页记录数
@PageIndex要获取的页码 @OrderType排序类型,0-升序,1-降序
@strWhere查询条件(注意:不要加where) 作 者:铁拳 邮
箱:unjianhua_kki@sina.com”sunjianhua_kki@sina.com 创建时间:2004-07-04
修改时间:2004-07-04 */ CreatePROCEDUREGetRecordFromPage
@tblNamevarchar(255),–表名 @fldNamevarchar(255),–字段名
@PageSizeint=10,–页尺寸 @PageIndexint=1,–页码
@OrderTypebit=0,–设置排序类型,非0值则降序
@strWherevarchar(2000)=”–查询条件(注意:不要加where) AS
declare@strSQLvarchar(6000)–主语句
declare@strTmpvarchar(1000)–临时变量
declare@strOrdervarchar(500)–排序类型 if@OrderType!=0 begin
set@strTmp=”(selectmin” set@strOrder=”orderby[“+@fldName+”]desc” end
else begin set@strTmp=”(selectmax”
set@strOrder=”orderby[“+@fldName+”]asc” end
set@strSQL=”selecttop”+str(@PageSize)+”*from[”
+@tblName+”]where[“+@fldName+”]”+@strTmp+”([”
+@fldName+”])from(selecttop”+str((@PageIndex-1)*@PageSize)+”[”
+@fldName+”]from[“+@tblName+”]”+@strOrder+”)astblTmp)” +@strOrder
if@strWhere!=” set@strSQL=”selecttop”+str(@PageSize)+”*from[”
+@tblName+”]where[“+@fldName+”]”+@strTmp+”([”
+@fldName+”])from(selecttop”+str((@PageIndex-1)*@PageSize)+”[”
+@fldName+”]from[“+@tblName+”]where”+@strWhere+””
+@strOrder+”)astblTmp)and”+@strWhere+””+@strOrder if@PageIndex=1 begin
set@strTmp=”” if@strWhere!=” set@strTmp=”where(“+@strWhere+”)”
set@strSQL=”selecttop”+str(@PageSize)+”*from[”
+@tblName+”]”+@strTmp+””+@strOrder end exec(@strSQL) GO

原帖子如下:

图片 1图片 2/**//*
图片 3 名称:spAll_DeleteNoneUnique
图片 4 输入:要查询的表名和字段列表
图片 5 输出:
图片 6 调用:
图片 7 说明:实现千万级数据的分页显示!–可以在5秒内获取1448万条记录里的第1200页的100条记录,雄不?
图片 8 作者:铁拳
图片 9 邮件:
图片 10 网站:
图片 11 更新:20040610
图片 12 支持:
图片 13 版权:转述时请注明来源:用思维创造未来的Wellknow.net
图片 14*/
图片 15
图片 16CREATE PROCEDURE GetRecordFromPage
图片 17    @tblName      varchar(255),       — 表名
图片 18    @fldName      varchar(255),       — 字段名
图片 19    @PageSize     int = 10,           — 页尺寸
图片 20    @PageIndex    int = 1,            — 页码
图片 21    @IsCount      bit = 0,            — 返回记录总数, 非 0 值则返回
图片 22    @OrderType    bit = 0,            — 设置排序类型, 非 0 值则降序
图片 23    @strWhere     varchar(1000) = ”  — 查询条件 (注意: 不要加 where)
图片 24AS
图片 25
图片 26declare @strSQL   varchar(6000)       — 主语句
图片 27declare @strTmp   varchar(100)        — 临时变量
图片 28declare @strOrder varchar(400)        — 排序类型
图片 29
图片 30if @OrderType != 0
图片 31begin
图片 32    set @strTmp = “<(select min”
图片 33    set @strOrder = ” order by [” + @fldName +”] desc”
图片 34end
图片 35else
图片 36begin
图片 37    set @strTmp = “>(select max”
图片 38    set @strOrder = ” order by [” + @fldName +”] asc”
图片 39end
图片 40
图片 41set @strSQL = “select top ” + str(@PageSize) + ” * from [“
图片 42    + @tblName + “] where [” + @fldName + “]” + @strTmp + “([“
图片 43    + @fldName + “]) from (select top ” + str((@PageIndex-1)*@PageSize) + ” [“
图片 44    + @fldName + “] from [” + @tblName + “]” + @strOrder + “) as tblTmp)”
图片 45    + @strOrder
图片 46
图片 47if @strWhere != ”
图片 48    set @strSQL = “select top ” + str(@PageSize) + ” * from [“
图片 49        + @tblName + “] where [” + @fldName + “]” + @strTmp + “([“
图片 50        + @fldName + “]) from (select top ” + str((@PageIndex-1)*@PageSize) + ” [“
图片 51        + @fldName + “] from [” + @tblName + “] where ” + @strWhere + ” “
图片 52        + @strOrder + “) as tblTmp) and ” + @strWhere + ” ” + @strOrder
图片 53
图片 54if @PageIndex = 1
图片 55begin
图片 56    set @strTmp = “”
图片 57    if @strWhere != ”
图片 58        set @strTmp = ” where ” + @strWhere
图片 59
图片 60    set @strSQL = “select top ” + str(@PageSize) + ” * from [“
图片 61        + @tblName + “]” + @strTmp + ” ” + @strOrder
图片 62end
图片 63
图片 64if @IsCount != 0
图片 65    set @strSQL = “select count(*) as Total from [” + @tblName + “]”
图片 66
图片 67exec (@strSQL)
图片 68
图片 69GO
图片 70
图片 71

修改后,便于使用的代码:

图片 72修改一点点便于其他人使用 
图片 73CREATE PROCEDURE GetRecordFromPage 
图片 74@tblName varchar(255), — 表名 
图片 75@fldName varchar(255), — 字段名 
图片 76@OrderfldName varchar(255), — 排序字段名 
图片 77@StatfldName varchar(255), — 统计字段名 
图片 78@PageSize int = 10, — 页尺寸 
图片 79@PageIndex int = 1, — 页码 
图片 80@IsCount bit = 0, — 返回记录总数, 非 0 值则返回 
图片 81@OrderType bit = 0, — 设置排序类型, 非 0 值则降序 
图片 82@strWhere varchar(1000) = ” — 查询条件 (注意: 不要加 where) 
图片 83AS 
图片 84
图片 85declare @strSQL varchar(6000) — 主语句 
图片 86declare @strTmp varchar(100) — 临时变量 
图片 87declare @strOrder varchar(400) — 排序类型 
图片 88
图片 89if @OrderType != 0 
图片 90begin 
图片 91set @strTmp = “<(select min” 
图片 92set @strOrder = ” order by [” + @OrderfldName +”] desc” 
图片 93end 
图片 94else 
图片 95begin 
图片 96set @strTmp = “>(select max” 
图片 97set @strOrder = ” order by [” + @OrderfldName +”] asc” 
图片 98end 
图片 99
图片 100set @strSQL = “select top ” + str(@PageSize) + ” ” + @fldName + ” from [” 
图片 101+ @tblName + “] where [” + @OrderfldName + “]” + @strTmp + “([” 
图片 102+ @OrderfldName + “]) from (select top ” + str((@PageIndex-1)*@PageSize) + ” [” 
图片 103+ @OrderfldName + “] from [” + @tblName + “]” + @strOrder + “) as tblTmp)” 
图片 104+ @strOrder 
图片 105
图片 106if @strWhere != ” 
图片 107set @strSQL = “select top ” + str(@PageSize) + ” ” + @fldName + ” from [” 
图片 108+ @tblName + “] where [” + @OrderfldName + “]” + @strTmp + “([” 
图片 109+ @OrderfldName + “]) from (select top ” + str((@PageIndex-1)*@PageSize) + ” [” 
图片 110+ @OrderfldName + “] from [” + @tblName + “] where ” + @strWhere + ” ” 
图片 111+ @strOrder + “) as tblTmp) and ” + @strWhere + ” ” + @strOrder 
图片 112
图片 113if @PageIndex = 1 
图片 114begin 
图片 115set @strTmp = “” 
图片 116if @strWhere != ” 
图片 117set @strTmp = ” where ” + @strWhere 
图片 118
图片 119set @strSQL = “select top ” + str(@PageSize) + ” ” + @fldName + ” from [” 
图片 120+ @tblName + “]” + @strTmp + ” ” + @strOrder 
图片 121end 
图片 122
图片 123if @IsCount != 0 
图片 124set @strSQL = “select count(” + @StatfldName + “) as Total from [” + @tblName + “]” 
图片 125
图片 126exec (@strSQL) 
图片 127
图片 128GO 
图片 129

说明:
增加了下面两个部分,其他人拷贝去可根据自己需要进行设定。
@OrderfldName varchar(255), — 排序字段名
@StatfldName varchar(255), — 统计字段名

fldName作用由排序转变为控制需要打开的字段。
@fldName varchar(255), — 字段名

----------------------------------------------
个人测试结果:
通过测试,性能并没有原帖子所说的好。但这可能是机器原因,要知道,在SQL
Server中,2千万条空记录大约要占用3G左右的空间,而插入这2千万条记录,在我的测试平台上耗费了近10个小时,内存占用从125M增长到350M左右。我实际测试了一下,在建立索引的情况下,执行一次根据主键,返回一条记录的查询,在512M内存,2.2G
CPU,20G单分区存储数据库文件,100M局域网的配置情况下,平均大约需要15s左右(这只是我的个人测试,不具有任何实质性意义。)。

另外我发现,对于上述配置的机器,运行SQL
Server时,在百万级别以下的表中执行查询--(索引良好,没有坏点,或者最新索引),速度差别不大,但达到8位数以上,也就是千万以上时候,SQL
Server表现就不太好了,当然,这与机器配置有很大关系了。但无可否认,SQL
Server 
2000还不是企业级数据库的最佳选择,在Oracle执行类似的查询,性能要好于SQL
Server。插入执行完毕的时间是4个小时,内存占用一直没有变化:400M。执行相同查询,需要的时间是10s左右。

小弟最近忙于一个公积金项目,对于部门数据库、中央数据库的调度进行了一些评估,Oracle软件+SUN/HP的硬件仍然占了中国政府机构服务器的大部分市场,再就是IBM,占据了高端和甚高端的大部分业务。

微软在这方面打个胜仗,还有很长的路要走--个人认为,和硬件公司合作研发相配套硬件是不二法门。

发表评论

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