实现千万级数据的分页显示－－整理资料并测试（转）
2024-07-21 02:06:41
供稿：网友
菜鸟学堂：
create procedure getrecordfrompage
@tblname varchar(255), -- 表名
@fldname varchar(255), -- 字段名
@pagesize int = 10, -- 页尺寸
@pageindex int = 1, -- 页码
@iscount bit = 0, -- 返回记录总数, 非 0 值则返回
@ordertype bit = 0, -- 设置排序类型, 非 0 值则降序
@strwhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
as
declare @strsql varchar(6000) -- 主语句
declare @strtmp varchar(100) -- 临时变量
declare @strorder varchar(400) -- 排序类型
if @ordertype != 0
begin
set @strtmp = "<(select min"
set @strorder = " order by [" + @fldname +"] desc"
end
else
begin
set @strtmp = ">(select max"
set @strorder = " order by [" + @fldname +"] asc"
end
set @strsql = "select top " + str(@pagesize) + " * from ["
+ @tblname + "] where [" + @fldname + "]" + @strtmp + "(["
+ @fldname + "]) from (select top " + str((@pageindex-1)*@pagesize) + " ["
+ @fldname + "] from [" + @tblname + "]" + @strorder + ") as tbltmp)"
+ @strorder
if @strwhere != ''
set @strsql = "select top " + str(@pagesize) + " * from ["
+ @tblname + "] where [" + @fldname + "]" + @strtmp + "(["
+ @fldname + "]) from (select top " + str((@pageindex-1)*@pagesize) + " ["
+ @fldname + "] from [" + @tblname + "] where " + @strwhere + " "
+ @strorder + ") as tbltmp) and " + @strwhere + " " + @strorder
if @pageindex = 1
begin
set @strtmp = ""
if @strwhere != ''
set @strtmp = " where " + @strwhere
set @strsql = "select top " + str(@pagesize) + " * from ["
+ @tblname + "]" + @strtmp + " " + @strorder
end
if @iscount != 0
set @strsql = "select count(*) as total from [" + @tblname + "]"
exec (@strsql)
go
修改后，便于使用的代码：
修改一点点便于其他人使用
create procedure getrecordfrompage
@tblname varchar(255), -- 表名
@fldname varchar(255), -- 字段名
@orderfldname varchar(255), -- 排序字段名
@statfldname varchar(255), -- 统计字段名
@pagesize int = 10, -- 页尺寸
@pageindex int = 1, -- 页码
@iscount bit = 0, -- 返回记录总数, 非 0 值则返回
@ordertype bit = 0, -- 设置排序类型, 非 0 值则降序
@strwhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
as
declare @strsql varchar(6000) -- 主语句
declare @strtmp varchar(100) -- 临时变量
declare @strorder varchar(400) -- 排序类型
if @ordertype != 0
begin
set @strtmp = "<(select min"
set @strorder = " order by [" + @orderfldname +"] desc"
end
else
begin
set @strtmp = ">(select max"
set @strorder = " order by [" + @orderfldname +"] asc"
end
set @strsql = "select top " + str(@pagesize) + " " + @fldname + " from ["
+ @tblname + "] where [" + @orderfldname + "]" + @strtmp + "(["
+ @orderfldname + "]) from (select top " + str((@pageindex-1)*@pagesize) + " ["
+ @orderfldname + "] from [" + @tblname + "]" + @strorder + ") as tbltmp)"
+ @strorder
if @strwhere != ''
set @strsql = "select top " + str(@pagesize) + " " + @fldname + " from ["
+ @tblname + "] where [" + @orderfldname + "]" + @strtmp + "(["
+ @orderfldname + "]) from (select top " + str((@pageindex-1)*@pagesize) + " ["
+ @orderfldname + "] from [" + @tblname + "] where " + @strwhere + " "
+ @strorder + ") as tbltmp) and " + @strwhere + " " + @strorder
if @pageindex = 1
begin
set @strtmp = ""
if @strwhere != ''
set @strtmp = " where " + @strwhere
set @strsql = "select top " + str(@pagesize) + " " + @fldname + " from ["
+ @tblname + "]" + @strtmp + " " + @strorder
end
if @iscount != 0
set @strsql = "select count(" + @statfldname + ") as total from [" + @tblname + "]"
exec (@strsql)
go
说明：
增加了下面两个部分，其他人拷贝去可根据自己需要进行设定。
@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，占据了高端和甚高端的大部分业务。
微软在这方面打个胜仗，还有很长的路要走－－个人认为，和硬件公司合作研发相配套硬件是不二法门。
