帮帮MM吧 面对120万的海量数据，我该怎么办，100分请高人留步

Question

我的数据库表中有120万条数据，我的分页太慢了(直接写在cs里)，但是我又不太懂SQL Server2000请那位高人给一个成熟的，分页存储过程(速度要快)，与调用这个存储过程的页面aspx与cs(不好意思，我不太懂得这个，以前就是直接在面写)，小妹先谢过了[email protected]

Answer 1

>>>我的分页太慢了确认每次取出的数据只是当前页的数据，参考Paging of Large Resultsets in ASP.NET
http://www.codeproject.com/aspnet/PagingLarge.asp

Answer 2

思归大哥，我　找你好久了，
http://community.csdn.net/Expert/topic/4323/4323022.xml?temp=.6950342

Answer 3

你搜索你下
我记得这个问题在CSDN里是有解决方法的
以前看到过的关于大数据量的分页

Answer 4

都是别人写的
CREATE procedure main_table_pwqzc
(@pagesize int,
@pageindex int,
@docount bit,
@this_id)
as
if(@docount=1)
begin
select count(id) from luntan where this_id=@this_id
end
else
begin
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 id from luntan where this_id=@this_id order by reply_time desc
select a.* from luntan a,@indextable t where a.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
end
GO存储过程会根据传入的参数@docount来确定是不是要返回所有要分页的记录总数
特别是这两行
set rowcount @PageUpperBound
insert into @indextable(nid) select id from luntan where this_id=@this_id order by reply_time desc真的是妙不可言!!set rowcount @PageUpperBound当记录数达到@PageUpperBound时就会停止处理查询
,select id 只把id列取出放到临时表里,select a.* from luntan a,@indextable t where a.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
而这句也只从表中取出所需要的记录,而不是所有的记录,结合起来,极大的提高了效率!!
妙啊,真的妙!!!!
sorry
应该是
CREATE procedure main_table_pwqzc
(@pagesize int,
@pageindex int,
@docount bit,
@this_id int)
as
if(@docount=1)
begin
select count(id) from luntan where this_id=@this_id
end
else
begin
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
create table #pageindex(id int identity(1,1) not null,nid int)
set rowcount @PageUpperBound
insert into #pageindex(nid)
select id from luntan where this_id=@this_id order by reply_time desc
select O.*
from luntan O,#pageindex p
where O.id=p.nid and p.id>@PageLowerBound and p.id<=@PageUpperBound order by p.id
end
GO
你看看这个，应该比那个更通用CREATE PROCEDURE Paging_RowCount
(
@Tables varchar(1000),
@PK varchar(100),
@Sort varchar(200) = NULL,
@PageNumber int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL)
AS/*Default Sorting*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PK/*Find the @PK type*/
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int/*Set sorting variables.*/
IF CHARINDEX('DESC',@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '<='
END
ELSE
BEGIN
IF CHARINDEX('ASC', @Sort) = 0
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
SET @operator = '>='
END
IF CHARINDEX('.', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
ENDSELECT @type=t.name, @prec=c.prec
FROM sysobjects o 
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @SortTable AND c.name = @SortNameIF CHARINDEX('char', @type) > 0
   SET @type = @type + '(' + CAST(@prec AS varchar) + ')'DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)/*Default Page Number*/
IF @PageNumber < 1
SET @PageNumber = 1/*Set paging variables.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50))/*Set filter & group variables.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''

/*Execute dynamic query*/
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
'
)
GO

Answer 5

#region 按条件统计行数
public void CountCells(string com)
{
try
{
string com1=com;
if (Label1.Text!="")
{
com1=com1+" where Msg like '%"+Label1.Text+"%'";
if(Textbox3.Text!=""&&Textbox4.Text!=""&&Textbox3.Text!=System.DateTime.Now.ToShortDateString())
{
com1+="and((CreateTime>='"+Textbox3.Text+"')and(CreateTime<='"+DateTime.Parse(Textbox4.Text).AddDays(+1)+"'))"+Label3.Text;
}
}
else
{
if(Textbox3.Text!=""&&Textbox4.Text!=""&&Textbox3.Text!=System.DateTime.Now.ToShortDateString())
{
com1+=" where ((CreateTime>='"+Textbox3.Text+"')and(CreateTime<='"+DateTime.Parse(Textbox4.Text).AddDays(+1)+"'))"+Label3.Text;
}
}              //Response.Write(com1);
DataBase DB=new DataBase();
sumcells=Int32.Parse(DB.CountRows(com1).ToString());
if(sumcells%Int32.Parse(LabelPage.Text)!=0)
{
pagenumb=(sumcells/Int32.Parse(LabelPage.Text))+1;
}
else
{
pagenumb=sumcells/Int32.Parse(LabelPage.Text);
}
Label7.Text=pagenumb.ToString();
y=sumcells;
DataGB();
}
catch
{
//JScript.Alert("1！");
JScript.Alert("请检查输入条件！");
JScript.GoHistory(-1);
return;
}
}
#endregion#region  绑定Gd
public void DataGB()
{
try
{
string com="";//LabelPage.Text 是分页行数
//DadaGdcom="select top "+LabelPage.Text+" * ,SUBSTRING(Title, 1, 4)as abc from (select top  "+y.ToString()+" *  from EmailLog  ";
DadaGdcom="select top "+LabelPage.Text+" *  from (select top  "+y.ToString()+" *  from Log  ";
string com1=" order by CreateTime)a order by CreateTime desc";
if (Label1.Text!="")
{
com=DadaGdcom+" where Msg like '%"+Label1.Text+"%'";
if(Textbox3.Text!=""&&Textbox4.Text!=""&&Textbox3.Text!=System.DateTime.Now.ToShortDateString())
{
com+="and ((CreateTime>='"+Textbox3.Text+"')and(CreateTime<='"+DateTime.Parse(Textbox4.Text).AddDays(+1)+"'))"+Label3.Text;
}
}
else
{
com=DadaGdcom;
if(Textbox3.Text!=""&&Textbox4.Text!=""&&Textbox3.Text!=System.DateTime.Now.ToShortDateString())
{
com+="where((CreateTime>='"+Textbox3.Text+"')and(CreateTime<='"+DateTime.Parse(Textbox4.Text).AddDays(+1)+"'))"+Label3.Text;
}
}
com+=com1;
// Response.Write(com+"\r\n");
Operate OP=new Operate();
DataTable datatable   = OP.GetCommonList(com);
if(Label4.Text=="1")
{
Button6.Enabled=false;
BtnFirstPage.Enabled=false;
}
else
{
Button6.Enabled=true;
BtnFirstPage.Enabled=true;
}
if(Label4.Text==Label7.Text)
{
BtnLastPage.Enabled=false;
Button7.Enabled=false;
}
else
{
BtnLastPage.Enabled=true;
Button7.Enabled=true;
}
}
catch
{
//throw new System.Exception();
//JScript.Alert(e.ToString());
JScript.Alert("请检查输入条件！");
JScript.GoHistory(-1);
return;
}
}
#endregion
private void Button7_Click(object sender, System.EventArgs e)//下一页按钮
{     
Label4.Text=Convert.ToString(Int32.Parse(Label4.Text)+1);
y=sumcells-(Int32.Parse(Label4.Text)-1)*Int32.Parse(LabelPage.Text);
DataGB();
} private void Button6_Click(object sender, System.EventArgs e)//上一页按钮
{
Label4.Text=Convert.ToString(Int32.Parse(Label4.Text)-1);
y=sumcells-(Int32.Parse(Label4.Text)-1)*Int32.Parse(LabelPage.Text);
DataGB();
} private void BtnFirstPage_Click(object sender, System.EventArgs e)//回首页按钮
{
Label4.Text="1";
y=sumcells-(Int32.Parse(Label4.Text)-1)*Int32.Parse(LabelPage.Text);
DataGB();
} private void BtnLastPage_Click(object sender, System.EventArgs e)//尾页按钮
{
Label4.Text=Label7.Text;
y=sumcells-(Int32.Parse(Label4.Text)-1)*Int32.Parse(LabelPage.Text);
DataGB();
}

Answer 6

这个问题在CSDN里，以前有的是，自己搜一下

Answer 7

考虑到随时灵活改变排序顺序、显示正确的序号等，用的越多越明白其实所谓的数据库分页方法解决不了速度问题。datagrid的分页已经很好了。只不过你获取datagrid的数据源的SQL应该使用类似“select top 5000 ....”这样的形式，也就是说只显示开头几千条记录就够了。没有哪个用户会愿意在上百个页面之间来回切换浏览。当记录超过5000的时候，你的页面可以给用户显示一个提示：“请输入查询匹配关键字以便缩小数据浏览范围！”。

Answer 8

通用的sqlserver2000分页方案。CREATE PROCEDURE sp_page
  @tb         varchar(50), --表名
  @col        varchar(50), --按该列来进行分页
  @coltype    int,         --@col列的类型,0-数字类型,1-字符类型,2-日期时间类型
  @orderby    bit,         --排序,0-顺序,1-倒序
  @collist    varchar(800),--要查询出的字段列表,*表示全部字段
  @pagesize   int,         --每页记录数
  @page       int,         --指定页
  @condition  varchar(800),--查询条件
  @pages      int OUTPUT   --总页数
AS
/*
功能描述：对指定表中满足条件的记录按指定列进行分页查询，分页可以顺序、倒序
         查询可以指定页大小、指定查询任意页、指定输出字段列表，返回总页数
作    者：pbsql
版    本：1.10
最后修改：2004-11-29
*/
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
IF @condition is null or rtrim(@condition)=''
BEGIN--没有查询条件
  SET @where1=' WHERE '
  SET @where2='  '
END
ELSE
BEGIN--有查询条件
  SET @where1=' WHERE ('+@condition+') AND '--本来有条件再加上此条件
  SET @where2=' WHERE ('+@condition+') '--原本没有条件而加上此条件
END
SET @sql='SELECT @pages=CEILING((COUNT(*)+0.0)/'+CAST(@pagesize AS varchar)+
         ') FROM '+@tb+@where2
EXEC sp_executesql @sql,N'@pages int OUTPUT',@pages OUTPUT--计算总页数
IF @orderby=0
  SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
           ' FROM '+@tb+@where1+@col+'>(SELECT MAX('+@col+') '+
           ' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
           @col+' FROM '+@tb+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col
ELSE
  SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
           ' FROM '+@tb+@where1+@col+'<(SELECT MIN('+@col+') '+
           ' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
           @col+' FROM '+@tb+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
           @col+' DESC'
IF @page=1--第一页
  SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+
    @where2+'ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
EXEC(@sql)
GO本存储过程高效，曾用500万条数据测试（已建索引），只返回分页只需3秒，影响效率的地方是计算总页数，若不需要可以注释掉--测试示例
declare @pages int
select identity(int,1,1) id,getdate() dt,xx=cast('' as varchar(10)) into #t
 from sysobjects
update #t set dt=dateadd(day,id-200,dt),
              xx='xxxx'+right('000000'+cast(id as varchar(10)),6)exec sp_page '#t','id',0,0,'*',10,2,'',@pages output--按id顺序取第二页
exec sp_page '#t','id',0,1,'*',10,2,'',@pages output--按id倒序取第二页
exec sp_page '#t','xx',1,0,'*',10,3,'',@pages output--按xx顺序取第三页
exec sp_page '#t','xx',1,1,'*',10,3,'',@pages output--按xx倒序取第三页
exec sp_page '#t','dt',2,0,'*',10,2,'',@pages output--按dt顺序取第二页
exec sp_page '#t','dt',2,1,'*',10,2,'',@pages output--按dt倒序取第二页select 总页数=@pagesdrop table #t

Answer 9

120万数据？
用sql的top分页既可以了。
每次调出要显示的行数即可。

Answer 10

http://blog.csdn.net/sunnystar365/archive/2005/09/28/491139.aspx
看一下这个里面分页的例子
使用存储过程，DataGrid自定义分页

Answer 11

http://dev.csdn.net/article/43/43936.shtm
海量数据库的查询优化及分页算法方案

Answer 12

以前寫的：
CREATE PROCEDURE [dbo].[USP_Page] 
(
 @currentPage int,  --第N页
 @pagesize  int,  --每页行数
 @tableName nvarchar(20), --表名
 @PKID nvarchar(20), --主鍵
 @orderBy nvarchar(20), --排序
 @where nvarchar(50) --條件
)
ASdeclare @strSql nvarchar(4000)
if @where='' set @where=' 1=1'
if @orderBy='' set @orderBy=@PKID
set @currentPage = @currentPage -1
set @strSql = ''set @strSql = @strSql + ' select count(*) from ' + @tableName + ' where '+ @whereset @strSql = @strSql + ' select top ' +  convert(nvarchar,@pagesize)  + ' * from ' + @tableName  
set @strSql = @strSql + ' where ' + @where  + ' and ( ' + @PKID 
set @strSql = @strSql + ' not in (  select top ' + convert(nvarchar, @currentPage * @pagesize) + ' ' +  @PKID + '  from '
set @strSql = @strSql + @tableName + ' where  ' + @where + ' order by  ' + @orderBy + ')) order by ' + @orderByexecute(@strSql)
GO像大量的數據就不要使用上面所說的利用游標來分頁了
要具體問題具體分析：像這樣的，可將上面的動態sql寫成靜態的，更能表現

Answer 13

按lxhvc(lanhai)提供的分页方案做，是不是有可能出现错误，在@col这一列的有重复值的情况下，比如要按时间来排序然后分页，有两条纪录a和b时间是一样的，a应该出现在第二页末尾，b应该出现在第三页的第一行，但是按照这种方案做，因为第三页的时间要大于前两页的时间的最大值，所以纪录b就不会显示出来了

Answer 14

实现千万级数据分页的存储过程http://blog.csdn.net/Sun_Jianhua/archive/2005/09/21/486654.aspx

Answer 15

CREATE PROCEDURE sp_page
  @tb         varchar(50), --表名
  @col        varchar(50), --按该列来进行分页
  @coltype    int,         --@col列的类型,0-数字类型,1-字符类型,2-日期时间类型
  @orderby    bit,         --排序,0-顺序,1-倒序
  @collist    varchar(800),--要查询出的字段列表,*表示全部字段
  @pagesize   int,         --每页记录数
  @page       int,         --指定页
  @condition  varchar(800),--查询条件
  @pages      int OUTPUT   --总页数
AS
/*
功能描述：对指定表中满足条件的记录按指定列进行分页查询，分页可以顺序、倒序
         查询可以指定页大小、指定查询任意页、指定输出字段列表，返回总页数
作    者：pbsql
版    本：1.10
最后修改：2004-11-29
*/
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
IF @condition is null or rtrim(@condition)=''
BEGIN--没有查询条件
  SET @where1=' WHERE '
  SET @where2='  '
END
ELSE
BEGIN--有查询条件
  SET @where1=' WHERE ('+@condition+') AND '--本来有条件再加上此条件
  SET @where2=' WHERE ('+@condition+') '--原本没有条件而加上此条件
END
SET @sql='SELECT @pages=CEILING((COUNT(*)+0.0)/'+CAST(@pagesize AS varchar)+
         ') FROM '+@tb+@where2
EXEC sp_executesql @sql,N'@pages int OUTPUT',@pages OUTPUT--计算总页数
IF @orderby=0
  SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
           ' FROM '+@tb+@where1+@col+'>(SELECT MAX('+@col+') '+
           ' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
           @col+' FROM '+@tb+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col
ELSE
  SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
           ' FROM '+@tb+@where1+@col+'<(SELECT MIN('+@col+') '+
           ' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
           @col+' FROM '+@tb+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
           @col+' DESC'
IF @page=1--第一页
  SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+
    @where2+'ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
EXEC(@sql)
GO本存储过程高效，曾用500万条数据测试（已建索引），只返回分页只需3秒，影响效率的地方是计算总页数，若不需要可以注释掉--测试示例
declare @pages int
select identity(int,1,1) id,getdate() dt,xx=cast('' as varchar(10)) into #t
 from sysobjects
update #t set dt=dateadd(day,id-200,dt),
              xx='xxxx'+right('000000'+cast(id as varchar(10)),6)exec sp_page '#t','id',0,0,'*',10,2,'',@pages output--按id顺序取第二页
exec sp_page '#t','id',0,1,'*',10,2,'',@pages output--按id倒序取第二页
exec sp_page '#t','xx',1,0,'*',10,3,'',@pages output--按xx顺序取第三页
exec sp_page '#t','xx',1,1,'*',10,3,'',@pages output--按xx倒序取第三页
exec sp_page '#t','dt',2,0,'*',10,2,'',@pages output--按dt顺序取第二页
exec sp_page '#t','dt',2,1,'*',10,2,'',@pages output--按dt倒序取第二页select 总页数=@pagesdrop table #t

Answer 16

not in 就行了..可是面对一天增加的数据就超过120W的该什么办. 流量统计系统

Answer 17

sqlsvr 一天一个表加120W 那牛

Answer 18

这不是一个存储过程就能解决的
可以参考以下几个帖子=======================
我发的两个帖子
http://community.csdn.net/Expert/TopicView3.asp?id=4182510
翻动100万级的数据 —— 只需几十毫秒 之揭秘篇：有详细的说明，不要错过。http://community.csdn.net/Expert/TopicView3.asp?id=4189627
翻动100万级的数据 —— 只需几十毫秒 之解释篇：本来不想再说了，但是有一个误会必须得说一下。==========================================
这里面有错误，看的时候要小心。
http://dev.csdn.net/article/43/43936.shtm
海量数据库的查询优化及分页算法方案

Answer 19

sfwxw0456(全职杀手) :
这个通用的分页存储过程有一点不明白  SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
           ' FROM '+@tb+@where1+@col+'>(SELECT MAX('+@col+') '+
           ' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
           @col+' FROM '+@tb+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col
语句中那个t（'ORDER BY '+@col+') t)）是什么意思呢？

Answer 20

我也碰到类似的问题，用的存储过程，中间用top取数据，具体就是取某一页的数据，如果符合条件的数据较多的话，那样查出来，显示前几页的数据很快，如果符合条件的数据很少，就一条或者几条，不够一页，同样非常的慢，什么出现数据不显示，请高手指教，这样的问题，怎么解决

Answer 21

提出数据填冲DataSet
然后序列化为Xml
DataSet ds;
…………
ds.WriteXml(……);
以后就不用从数据库中取了
ds.ReadXml(…………);
接分！！！