这两个分页高手分别写了自己的分页方法,我想知道哪个分页效率更高一些。1、webdiyer(陕北吴旗娃) ,我用的就是他的分页控件。
关键语句如下:
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select Pid from TPutong where reader like '%'+@yonghu +'%' order by Pid desc
select O.Pid,O.title from TPutong O,@indextable t where O.Pid=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
2、“铁拳”的千万级分页方法,关键代码如下:
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)
ASdeclare @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"
endset @strSQL = "select top " + str(@PageSize) + " * from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
+ @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"
+ @strOrderif @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 + " " + @strOrderif @PageIndex = 1
begin
set @strTmp = ""
if @strWhere != ''
set @strTmp = " where " + @strWhere set @strSQL = "select top " + str(@PageSize) + " * from ["
+ @tblName + "]" + @strTmp + " " + @strOrder
endif @IsCount != 0
set @strSQL = "select count(*) as Total from [" + @tblName + "]"exec (@strSQL)GO请大家分析一下,目的不是分高低,是共同学习进步。有得罪之处,千万望见谅!!拜谢!
关键语句如下:
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select Pid from TPutong where reader like '%'+@yonghu +'%' order by Pid desc
select O.Pid,O.title from TPutong O,@indextable t where O.Pid=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
2、“铁拳”的千万级分页方法,关键代码如下:
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)
ASdeclare @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"
endset @strSQL = "select top " + str(@PageSize) + " * from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
+ @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"
+ @strOrderif @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 + " " + @strOrderif @PageIndex = 1
begin
set @strTmp = ""
if @strWhere != ''
set @strTmp = " where " + @strWhere set @strSQL = "select top " + str(@PageSize) + " * from ["
+ @tblName + "]" + @strTmp + " " + @strOrder
endif @IsCount != 0
set @strSQL = "select count(*) as Total from [" + @tblName + "]"exec (@strSQL)GO请大家分析一下,目的不是分高低,是共同学习进步。有得罪之处,千万望见谅!!拜谢!
我也想知道好的分页方法似乎都是
第一步先得到小结果集
第二步根据not in、in或<、>等来选出最后的结果
根据记录条数(百万级和千万级)和内存的大小,每一步都应该有适当的方法
希望测试过的朋友指点
if @IsCount != 0
set @strSQL = "select count(*) as Total from [" + @tblName + "]"
好像永远都不会执行的