100万条数据如何分页？？ 强人指教!!!

我用数据集配合SQLpage进行分页。 速度太慢了。。  翻一次页面 估计要20秒。
如何。。。 进行优化？？？

50 个解决方案

#1

分布存储过程不行?

#2

存储过程可以。

也可以用写方法分页--类似于存储过程。

#3

需要一次取那么多数据么

#4

数据库优化   
存储过程优化   
在要查询的表上建立适当的索引   
分区分页
http://topic.csdn.net/u/20080627/12/4a404bb1-6667-466a-925a-4d7bf690695e.html
http://topic.csdn.net/u/20090113/16/cac6480c-84ff-4ae3-8fa1-11cfa39fd8ea.html?28098

#5

用储存过程 楼主可考虑下用分页控件或者用ajax

#6

我比较菜。。。  不懂分布式存储过程和一般的有什么区别？
这个么？

1CREATE PROCEDURE sp_paging
 2@sqlstr NVARCHAR(4000), --查询字符串 
 3@currentpage INT, --第N页>=1
 4@pagesize INT --每页行数 
 5AS 
 6SET NOCOUNT ON 
 7declare @P1 INT, --P1是游标的id 
 8@rowcount INT 
 9declare @allPages int
10EXEC sp_cursoropen @P1 OUTPUT,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount OUTPUT 
11set @allPages=CEILING(1.0*@rowcount/@pagesize)
12if(@currentpage>@allPages)
13begin
14  set @currentpage=@allPages
15end 
16if(@currentpage<1)
17begin
18  set @currentpage=1
19end 
20SELECT @allPages AS Totalpages,@rowcount as Rowcounts,@currentpage as CurrentPage
21SET @currentpage=(@currentpage-1)*@pagesize+1 
22EXEC sp_cursorfetch @P1,16,@currentpage,@pagesize 
23EXEC sp_cursorclose @P1 
24SET NOCOUNT OFF
25GO

   

#7

一次取100W,如果樓主的頁面提供排序/翻頁/分頁,這個頁面會當掉
如樓上所說

#8

是进行分批取值么？  比如我一次取 1-1000 
第2次 取1001-2000？
这样？？  这个就是分布式存储过程？

#9

declare proc pager
(
@pagesize int
@pagecount int
)
as
select top (@pagecount) from table where id > (@pagesize * @pagecount)
类似这个，好久没有写了

#10

你的想法可 以

#11

引用 8 楼 lijing3333 的回复:

是进行分批取值么？  比如我一次取 1-1000
第2次 取1001-2000？
这样？？  这个就是分布式存储过程？

是的，就 是 这个 意思 

#12

一次取出1百万条那就不叫分页了.
处理思路就是一次取出1000条或者自己定个值,然后给这1000条分页,点击这1000条中的最后一页的时候再取一次.依此类推,楼上的已经说的很明白了.
还有100万的话推荐用存储过程处理,否则服务器内存会吃不消的.

#13

先求出总记录数方法：select count(*) form tablename
设置pagesize
获得页数，
根据pageindexchanging
获取currentpageindex
可以算出 每页的标识列的id，startindex和endindex
然后select column form tablename where id>=startindex & id<=endindex
以上大体思路就那样。

#14

引用 12 楼 wjn161 的回复:

一次取出1百万条那就不叫分页了.
处理思路就是一次取出1000条或者自己定个值,然后给这1000条分页,点击这1000条中的最后一页的时候再取一次.依此类推,楼上的已经说的很明白了.
还有100万的话推荐用存储过程处理,否则服务器内存会吃不消的.

ALTER proc [dbo].[proc_viewlist]
@Table varchar(300), --表名
@Where varchar(7500),--查询条件
@Cou varchar(500),--查询的列,查询全部用*
@NewPageIndex int,--当前页码
@PageSize int,--分页条数
@order varchar(100), --排序列,
@isDistinct int=0  --是否带distinct 如果带,则isDistinct为1,不带 可以省略此参数
as
declare @str varchar(8000)
---------------------------------------------查询总记录数------------------------------------
if @isDistinct <> 0
set @str='select count(1) from  (select '+@cou+' from '+@table
else
set @str='select count(1) from  '+@table
if len(@where)<>0
set @str=@str+' where '+@where
if @isDistinct <> 0
set @str=@str+' )a'
---------------------------------------------查询当前页记录-----------------------------------
if @isDistinct <> 0
set @str=@str+' select * from(select *,row_number() over(order by '+@order+' ) as Rownum from ( select '+@cou+' from '+@table
else
set @str=@str+' select * from ( select '+@cou+' ,row_number() over(order by '+@order+') as Rownum from '+@table
if len(@where)<>0
set @str=@str+' where '+@where
if @isDistinct <> 0
set @str=@str+' )T1)T '
else
set @str=@str+' )T '
set @str=@str+'where Rownum between '+cast((@NewPageIndex-1)*@PageSize+1 as varchar(20))+' and '+cast(@NewPageIndex*@PageSize as varchar(20))
if len(@order)<>0
set @str=@str+' order by '+@order
exec(@str)
print (@str)

#15

通用的分页方法是 select top 10*2 * from table where id not in(top 10*2 id from table)
例子为取第三页
通常都是看第一页，速度非常快

此方法看最后一页速度最慢， 所以尽量限定一下

#16

关键在于查询分页时是否充分合理的使用了索引。

我不否认使用存储过程比直接使用SQL语句要快，但这种快 不太感觉的出来。而当数据量达到百万级之后，查询时是否充分合理的使用索引，才是关键。

#17

引用 15 楼 yyz985 的回复:

通用的分页方法是 select top 10*2 * from table where id not in(top 10*2 id from table)
例子为取第三页
通常都是看第一页，速度非常快

此方法看最后一页速度最慢， 所以尽量限定一下

写错
select top 10 * from table where id not in(top 10*2 id from table)

#18

引用 11 楼 hetl_1985 的回复:

引用 8 楼 lijing3333 的回复:
 是进行分批取值么？  比如我一次取 1-1000
 第2次 取1001-2000？
 这样？？  这个就是分布式存储过程？

 是的，就 是 这个 意思

啊。这样啊。。。
那就是存储过程分页

#19

进来学习...

#20

查询程序一次最多取5000条数据，提供一个导出完整数据的功能，用户需要再慢速导给他。

#21

学习

#22

根据我的经验，程序一次返回超过500条记录，用户就没有兴趣阅读了。

应该给用户根多筛选项，减少数据量，让人一次去阅读100万数据，还要你的程序作什么？

#23

学习~~

#24

分页是很有学问的 思路不同 效率就不同！大家的效率如何，自己可以运行一下,看看每次翻页需要多长时间！

#25

数据库本身优化要好，使用高效率关键字！

#26

100万？建议用水冷服务器！哈哈。

#27

自己写分页代码，没次只取当前页次的数据出来！

#28

实现小数据量和海量数据的通用分页显示存储过程

　　建立一个 Web 应用，分页浏览功能必不可少。这个问题是数据库处理中十分常见的问题。经典的数据分页方法是:ADO 纪录集分页法，也就是利用ADO自带的分页功能（利用游标）来实现分页。但这种分页方法仅适用于较小数据量的情形，因为游标本身有缺点：游标是存放在内存中，很费内存。游标一建立，就将相关的记录锁住，直到取消游标。游标提供了对特定集合中逐行扫描的手段，一般使用游标来逐行遍历数据，根据取出数据条件的不同进行不同的操作。而对于多表和大表中定义的游标（大的数据集合）循环很容易使程序进入一个漫长的等待甚至死机。
　　更重要的是，对于非常大的数据模型而言，分页检索时，如果按照传统的每次都加载整个数据源的方法是非常浪费资源的。现在流行的分页方法一般是检索页面大小的块区的数据，而非检索所有的数据，然后单步执行当前行。
　　最早较好地实现这种根据页面大小和页码来提取数据的方法大概就是“俄罗斯存储过程”。这个存储过程用了游标，由于游标的局限性，所以这个方法并没有得到大家的普遍认可。
　　后来，网上有人改造了此存储过程，下面的存储过程就是结合我们的办公自动化实例写的分页存储过程：

-- =============================================
-- Author:        Takako_Yang
-- Create date: 2009/10/20
-- Description:    SQL如何分页以及优化
-- =============================================
CREATE PROCEDURE USP_Test_Select
@pagesize int,--页面大小,如每页存储10条记录
@pageindex int,--目前页面
@startdate datetime,--开始日期
@duedate datetime--结束日期
AS

BEGIN
with A as 
(
    select row_numer() over(order by date) as rowID,--依时间排序,定义一个自增列用作分页
            *
        from TableA
        where date between @startdate and @duedate    --by条件得到所有的资料
)
select * 
    from A
    where rowID >(@pageindex-1)*@pagesize and rowID<=@pageindex*@pagesize
    order by date
END
GO

/**//*
当然,with A as()这种写法还是可以再优化的.
表变量的效率>临时表的效率>with A as()的效率
以表变量为例,如下,省略Create部分
*/

BEGIN
declare @indexTable table(rowID int identity(1,1),nid int)
insert into @indexTable(nid)
select gid from TableA where date between @startdate and @duedate order by date
select A.* 
    from TableA A inner join @indexTable B
    on A.gid=B.nid
        and B.rowID>(@pageindex-1)*@pagesize and B.rowID<=@pageindex*@pagesize
    order by B.rowID
END

下面一段SQL是Happyflystone整理的分页function。摘录如下：

-- =============================================
-- Author:        Happyflystone
-- Create date: 2007/11/18
-- Description:    SQL如何分页以及优化
-- =============================================
-- 这一段Happyflystone写的太长了,而且效率要在1000页以上才能体现出来..我没看-.-谁有兴趣的可以看看

CREATE PROCEDURE pagination3
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = ''*'', -- 需要返回的列 
@fldName varchar(255)='''', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '''' -- 查询条件 (注意: 不要加 where)
AS

declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型

if @doCount != 0
begin
if @strWhere !=''''
set @strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere
else
set @strSQL = "select count(*) as Total from [" + @tblName + "]"
end 
--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况：

else
begin
if @OrderType != 0
begin
set @strTmp = "<(select min"
set @strOrder = " order by [" + @fldName +"] desc"
--如果@OrderType不是0，就执行降序，这句很重要！

end
else
begin
set @strTmp = ">(select max"
set @strOrder = " order by [" + @fldName +"] asc"
end

if @PageIndex = 1
begin
if @strWhere != '''' 

set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "
　　from [" + @tblName + "] where " + @strWhere + " " + @strOrder
else

set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " 
　　from ["+ @tblName + "] "+ @strOrder
--如果是第一页就执行以上代码，这样会加快执行速度

end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
　

set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " 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) +" "+@strGetFields+ " from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
+ @fldName + "] from [" + @tblName + "] where " + @strWhere + " "
+ @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder
end 

end 

exec (@strSQL)

GO

#29

你只要实现一句“从你需要的数据中取第m到第n条数据”的方法就OK了。

#30

学习下

#31

sql server 2005不是有个什么行号吗，可以用行号分页的

#32

用视图最简单了，我用过的

#33

蠢猪过程

#34

建好索引
每次取自己所要的部分数据

#35

100万数据并不太大，分页后效率还是很高的。LZ你确定你分页正确不，另外适当的索引也很重要。

#36

这个是我现在用的，参考：
http://blog.csdn.net/snoopy83101/archive/2009/09/09/4536582.aspx

#37

进来学习学习！

#38

 对 用存储过程！

#39

在数据库里分页还是取出来在在程序里分页？
前者会快很多

#40

学习

#41

存储过程分页吧，肯定可以，我们的数据量都150w了，速度2秒吧，建好索引

#42

关注

#43

这个我做过，分页存储过程可以。但只能用唯一性的主键排序，你想改成别的排序方式，对不起，你会发现翻页的时候页码在乱跳。
#28楼，你如果不能保证排序字段@fldName的唯一性，那这个存储过程就不能用。我举个例子，简单点的
id  name
1   a
2   a
3   c
4   a
5   a
6   b
7   b
8   c
9   c
10  a
where条件为空，如果pagesize=3，按name字段排序，可以看到表中name=a有5条
那么第一页取出的是id为1,2,4三条，
这时候点下一页，就把另两条name=a的过滤掉了。。。
如果是唯一的id就没问题。

#44

学习了 。

#45

学习来了

#46

引用 28 楼 takako_mu 的回复:

实现小数据量和海量数据的通用分页显示存储过程

　　建立一个 Web 应用，分页浏览功能必不可少。这个问题是数据库处理中十分常见的问题。经典的数据分页方法是:ADO 纪录集分页法，也就是利用ADO自带的分页功能（利用游标）来实现分页。但这种分页方法仅适用于较小数据量的情形，因为游标本身有缺点：游标是存放在内存中，很费内存。游标一建立，就将相关的记录锁住，直到取消游标。游标提供了对特定集合中逐行扫描的手段，一般使用游标来逐行遍历数据，根据取出数据条件的不同进行不同的操作。而对于多表和大表中定义的游标（大的数据集合）循环很容易使程序进入一个漫长的等待甚至死机。
　　更重要的是，对于非常大的数据模型而言，分页检索时，如果按照传统的每次都加载整个数据源的方法是非常浪费资源的。现在流行的分页方法一般是检索页面大小的块区的数据，而非检索所有的数据，然后单步执行当前行。
　　最早较好地实现这种根据页面大小和页码来提取数据的方法大概就是“俄罗斯存储过程”。这个存储过程用了游标，由于游标的局限性，所以这个方法并没有得到大家的普遍认可。
　　后来，网上有人改造了此存储过程，下面的存储过程就是结合我们的办公自动化实例写的分页存储过程：

SQL code-- =============================================
-- Author:        Takako_Yang
-- Create date: 2009/10/20
-- Description:    SQL如何分页以及优化
-- =============================================CREATEPROCEDURE USP_Test_Select@pagesizeint,--页面大小,如每页存储10条记录@pageindexint,--目前页面@startdatedatetime,--开始日期@duedatedatetime--结束日期ASBEGINwith Aas 
(select row_numer()over(orderby date)as rowID,--依时间排序,定义一个自增列用作分页*from TableAwhere datebetween@startdateand@duedate--by条件得到所有的资料)select*from Awhere rowID>(@pageindex-1)*@pagesizeand rowID<=@pageindex*@pagesizeorderby dateENDGO/**//*
当然,with A as()这种写法还是可以再优化的.
表变量的效率>临时表的效率>with A as()的效率
以表变量为例,如下,省略Create部分*/BEGINdeclare@indexTabletable(rowIDintidentity(1,1),nidint)insertinto@indexTable(nid)select gidfrom TableAwhere datebetween@startdateand@duedateorderby dateselect A.*from TableA Ainnerjoin@indexTable Bon A.gid=B.nidand B.rowID>(@pageindex-1)*@pagesizeand B.rowID<=@pageindex*@pagesizeorderby B.rowIDEND
下面一段SQL是Happyflystone整理的分页function。摘录如下：
SQL code-- =============================================
-- Author:        Happyflystone
-- Create date: 2007/11/18
-- Description:    SQL如何分页以及优化
-- =============================================
-- 这一段Happyflystone写的太长了,而且效率要在1000页以上才能体现出来..我没看-.-谁有兴趣的可以看看CREATEPROCEDURE pagination3@tblNamevarchar(255),-- 表名@strGetFieldsvarchar(1000)=''*'',-- 需要返回的列@fldNamevarchar(255)='''',-- 排序的字段名@PageSizeint=10,-- 页尺寸@PageIndexint=1,-- 页码@doCountbit=0,-- 返回记录总数, 非 0 值则返回@OrderTypebit=0,-- 设置排序类型, 非 0 值则降序@strWherevarchar(1500)=''''-- 查询条件 (注意: 不要加 where)ASdeclare@strSQLvarchar(5000)-- 主语句declare@strTmpvarchar(110)-- 临时变量declare@strOrdervarchar(400)-- 排序类型if@doCount!=0beginif@strWhere!=''''set@strSQL= "selectcount(*)as Totalfrom[" + @tblName + "]where "+@strWhereelseset@strSQL= "selectcount(*)as Totalfrom[" + @tblName + "]"end--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况：elsebeginif@OrderType!=0beginset@strTmp= "<(selectmin"set@strOrder= "orderby[" + @fldName +"]desc"--如果@OrderType不是0，就执行降序，这句很重要！endelsebeginset@strTmp= ">(selectmax"set@strOrder= "orderby[" + @fldName +"]asc"endif@PageIndex=1beginif@strWhere!=''''set@strSQL= "selecttop "+str(@PageSize)+" "+@strGetFields+ "
　　from[" + @tblName + "]where "+@strWhere+ " "+@strOrderelseset@strSQL= "selecttop "+str(@PageSize)+" "+@strGetFields+ " 
　　from["+ @tblName + "] "+@strOrder--如果是第一页就执行以上代码，这样会加快执行速度endelsebegin--以下代码赋予了@strSQL以真正执行的SQL代码　set@strSQL= "selecttop "+str(@PageSize)+" "+@strGetFields+ "from["
+ @tblName + "]where[" + @fldName + "]"+@strTmp+ "(["+ @fldName + "]) 
　　from (selecttop "+str((@PageIndex-1)*@PageSize)+ "["+ @fldName + "] 
　　from[" + @tblName + "]"+@strOrder+ ")as tblTmp)"+@strOrderif@strWhere!=''''set@strSQL= "selecttop "+str(@PageSize)+" "+@strGetFields+ "from["
+ @tblName + "]where[" + @fldName + "]"+@strTmp+ "(["
+ @fldName + "])from (selecttop "+str((@PageIndex-1)*@PageSize)+ "["
+ @fldName + "]from[" + @tblName + "]where "+@strWhere+ " "+@strOrder+ ")as tblTmp)and "+@strWhere+ " "+@strOrderendendexec (@strSQL)GO

顶这位啊。很详细了，再就是数据索引问题。一百万数据根本不是问题。

#47

谢谢各位。。。虽然很多代码看不太懂。  但是起码的思路倒是有了点。。。。。

#48

我也借楼主的地方学习了。

#49

先做个书签，以后需要用到的时候再回来学习！

#50

做书签是没有用的,