讨论：实现千万级数据的分页显示存储过程？？？？？
实现千万级数据的分页显示－－整理资料并测试（转） 选择自 gxh973121 的 Blog
修改一点点便于其他人使用
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
____________________________________________________
————————————————————————————————————————————
此查询存储过程就像这样的SQL语句
select * from where id>(select max(id) from (select top 100000 form table) as tempid)
如果有上百万条记录，最后一页
就要扫描1000000万行，对不对？
在SQL SERVER我下我试过很慢啊，要几分钟才行,在ASP环境下已经不能正确运行了!
各位有什么好的解决方案吗？
问题点数：0、回复次数：5Top
1 楼aohan（aohan）回复于 2004-12-03 23:45:13 得分 0
upTop
2 楼realgz（realgz）回复于 2004-12-04 09:50:49 得分 0
kw级的数据，没利用聚集索引，算了吧。。。。。。。。
Top
3 楼clkun（我来是学习）回复于 2004-12-04 12:41:52 得分 0
聚集索引是什么意思，能简单明了的说明我们常用的举例子吗？Top
4 楼xiaye0908（<逍遥E剑>）回复于 2004-12-04 13:06:03 得分 0
<%! int Size=20;//每页几行数据。
int Count=0;//总页数。
int zz=0;
%>
<% handlePage.setPageSize(Size);%>
<%String strtext=request.getParameter("textfield4");
handlePage.CovC(strtext);
%>
<% Connection con;
Statement sql,sqls;
ResultSet rs,rss;
try{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");}
catch(ClassNotFoundException e){}
con=DriverManager.getConnection("jdbc:odbc:6848","sa","123");
sqls=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
rss=sqls.executeQuery("select count(cname) from info where cname like '%"+handlePage.getCovC()+"%'");
rss.next();
int lastRow=Integer.parseInt(rss.getString(1));
handlePage.setPageCount(lastRow,Size);
//当前页码。
%>
<%String str=response.encodeRedirectURL("select.jsp");%>
<% String integer=request.getParameter("a"); //获取表单提交的信息。
int s;
out.print(integer);
if(integer==null)
{ handlePage.setShowPage(0);
}
else if(integer.equals("next"))
{ s=handlePage.getShowPage();
s=s+1;
handlePage.setShowPage(s);
if(s<1)//当小于最小页码的时候；
{s=1;
handlePage.setShowPage(0);}
if(s>=handlePage.getPageCount())//当大于总页的时候；
{s=handlePage.getPageCount();
handlePage.setShowPage(s); }
}
else if(integer.equals("previous"))
{ s=handlePage.getShowPage();
s=s-1;
handlePage.setShowPage(s);
if(s<1)
{s=1;
handlePage.setShowPage(0);}
if(s>=handlePage.getPageCount())
{s=handlePage.getPageCount();
handlePage.setShowPage(s);}
}
else{
try{s=Integer.parseInt(integer);}
catch(NumberFormatException e)
{s=1;}
if(s<=1)
{s=1;
handlePage.setShowPage(0);
zz=1;
}
if(s>=handlePage.getPageCount())
{s=handlePage.getPageCount();}
handlePage.setShowPage(s);
}
if(lastRow<handlePage.getPageSize())
{
handlePage.setShowPage(0);
s=1;
zz=1;
}
else { zz=handlePage.getPageCount();
s=handlePage.getShowPage()+1;
}
sql=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
rs=sql.executeQuery("select top "+handlePage.getPageSize()+" webadd,cname from info where cname like '%"+handlePage.getCovC()+"%' and cname not in(select top "+(handlePage.getShowPage()*handlePage.getPageSize())+" cname from info where cname like '%"+handlePage.getCovC()+"%'order by cname asc) order by cname asc");
out.print("<table border=0 width=300 align=left cellpadding=0 cellspacing=0>");
while(rs.next())
{out.print("<tr>");
out.print("<td><font size=2><a href="+rs.getString(1)+" target='_blank'>"+rs.getString(2)+"</a></font></td>");
out.print("</tr>");
}
out.print("</table>");
con.close();
%>Top
5 楼azmiao（amiao）回复于 2004-12-08 22:28:35 得分 0
兄弟一个想法，
比如要获得100－200之间的记录
create view v1 as select top 200 * from tbl order by id asc
select top 100 * from v1 order by id descTop
