百万数据分页显示效率的问题

mirzlm 2009-03-05 04:03:46 


String sql="select * from users where userID between "+num1+" and "+num2;

userID为主键是以1,2,3,4,5...递增的形式增加
优点：效率高的惊人，显示最后一页的数据时，只花了15毫秒的时间
缺点：当userID中的某段被删除了，如userID＝11到20这段数据没了，那第2页就没有数据显示了，
而且如果中间有被删除了，那本来一页显示10条的，就会变成显示9条，8条或更少




String sql="select * from (" 

+"select top "+num1+" * from "

+"(select top "+num2+" * from users order by userID) a "

+"order by a.userID desc" 

+") b order by b.userID";

使用top来取
优点：格式统一，每页显示数一致，最后一页也是一样
缺点：效率一般，随着数据量的增加而降低，比如100W的数据量，第一页的时候，只要30毫秒左右，最后一页要2375毫秒左右，
而且如果数据总量不是每页显示记录数的整数倍的话，最后一页与最后第二页会有相同的记录出现



String sql="select * from " 

+"( " 

+"select t.* , (select count(userID) from users where userID < t.userID) + 1 px from users t" 

+") m " 

+"where px between "+num1+" and "+num2;

此方法是一网上朋友告诉我的，我在测试的时候，在数据量30W的时候，我用的sql server 2005数据库就卡住了，无法显示
不知道是不是我哪里用错了



String sql="select * from ("

+"select *,px=(select count(1)+1 from users where userID<t.userID) from users t "

+") tt "

+"where px between "+num1+" and "+num2;

此方法与上面的方法差不多，效果也差不多，在数据量30W的时候，数据库就卡住了
也请告知下



String sql="select count(userID) as sum from users";

我用了这个方法来取得记录总数，以100条数据为例，他要取一下要花1984毫秒左右的时间
这里随便问一下 有没有更优的方法



以上几种是我用的方法，想请问下，现在都用什么方式达到最优的效率
请前辈们不吝赐教，先谢谢了
...全文
202 13 打赏 收藏 转发到动态 举报
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
asd410130630 2010-09-12
  • 打赏
  • 举报
 回复
e盛大大苏打上打算 大苏打按时打算
蓝天-2022 2010-09-12
  • 打赏
  • 举报
 回复
挺好，收藏
mirzlm 2009-03-05
  • 打赏
  • 举报
 回复
再次感谢各位前辈的帮助：） 结帐
mirzlm 2009-03-05
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 dawugui 的回复:]
引用 8 楼 mirzlm 的回复:
以上这类是不是都会调用存储过程？ 怎么让java调用他们呢。。。我不会实现

去JAVA那边问问如何用JAVA调用SQL的存储过程.
[/Quote]

好的，正有此意，：）
dawugui 2009-03-05
  • 打赏
  • 举报
 回复
[Quote=引用 8 楼 mirzlm 的回复:]
以上这类是不是都会调用存储过程？ 怎么让java调用他们呢。。。我不会实现
[/Quote]
去JAVA那边问问如何用JAVA调用SQL的存储过程.
mirzlm 2009-03-05
  • 打赏
  • 举报
 回复
以上这类是不是都会调用存储过程？ 怎么让java调用他们呢。。。我不会实现
mirzlm 2009-03-05
  • 打赏
  • 举报
 回复
先感谢楼上几位前辈的提点，我先拿去消化下，再测试下，有问题再提，嘿
nzperfect 2009-03-05
  • 打赏
  • 举报
 回复

我的分页高效算法：
http://www.dmblogs.com/nzBlog/article.asp?id=25
xing020010 2009-03-05
  • 打赏
  • 举报
 回复
汗 ，大家手都真快
xing020010 2009-03-05
  • 打赏
  • 举报
 回复 


create proc Proc_paged

(

	@pagesize int,

	@pagenum int,

	@pagecount int output

)

as

begin

--声明变量

declare @tmptable table(id int identity (1,1),userid nchar(5))

declare @idBengin int

declare @idend int

--构造内存表

insert into @tmptable (userid )(select userid from users)

select @pagecount=count(*) from @tmptable

if(@pagecount%@pagesize>0)

set @pagecount=@pagecount/@pagesize+1

else

set @pagecount=@pagecount/@pagesize

set @idBengin=(@pagenum-1)*@pagesize

set @idend=@idBengin+@pagesize

select t2.id,t1.* from users t1,@tmptable t2 where t1.userid=t2.userid and

	 t2.id>@idBengin and t2.id<=@idend

end

看看这个，查询任何页时间都差不多，不存在前边方法的缺点
dawugui 2009-03-05
  • 打赏
  • 举报
 回复 
SQL 2005的ROW_NUMBER()实现分页功能



DECLARE @pagenum AS INT, @pagesize AS INT

SET @pagenum = 2

SET @pagesize = 3

SELECT *

FROM (SELECT ROW_NUMBER() OVER(ORDER BY newsid DESC) AS rownum, 

        newsid, topic, ntime, hits

      FROM news) AS D

WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize

ORDER BY newsid DESC 



aspx里面只需给SQL传入pageid和条数即可。 



CSDN上还有个存储过程实现分页的代码： 





ALTER PROCEDURE news_Showlist

(

@tblName   varchar(255),       -- 表名

@strGetFields varchar(1000),  -- 需要返回的列

@fldName varchar(255),      -- 排序的字段名

@PageSize   int ,          -- 页尺寸

@PageIndex  int ,           -- 页码

@strWhere  varchar(1500),  -- 查询条件(注意: 不要加where)

@Sort varchar(255)      --排序的方法



)

AS

declare @strSQL   varchar(5000)       -- 主语句

declare @strTmp   varchar(110)        -- 临时变量

declare @strOrder varchar(400)        -- 排序类型



if @Sort = 'desc'

begin

set @strTmp = '<(select min'

set @strOrder = ' order by ' + @fldName +' desc'

--如果@OrderType不是，就执行降序，这句很重要！

end

else

begin

set @strTmp = '>(select max'

set @strOrder = ' order by ' + @fldName +' asc'

end

 

if @PageIndex = 1

begin

if @strWhere != ''   

  begin

set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ' + @tblName 

+ ' where ' + @strWhere + ' ' + @strOrder

  end

else

  begin

set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from '+ @tblName 

+ ' '+ @strOrder

  end

--如果是第一页就执行以上代码，这样会加快执行速度

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

exec (@strSQL)

RETURN
dawugui 2009-03-05
  • 打赏
  • 举报
 回复
你看邹老大这几个哪个适用?

--TOP n 实现的通用分页存储过程(转自邹建)

CREATE PROC sp_PageView

@tbname     sysname,           	--要分页显示的表名

@FieldKey   nvarchar(1000),  	--用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段

@PageCurrent int=1,           	--要显示的页码

@PageSize   int=10,            	--每页的大小(记录数)

@FieldShow nvarchar(1000)='',  	--以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段

@FieldOrder nvarchar(1000)='',  	--以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC

                                          用于指定排序顺序

@Where    nvarchar(1000)='', 	--查询条件

@PageCount int OUTPUT         	--总页数

AS

SET NOCOUNT ON

--检查对象是否有效

IF OBJECT_ID(@tbname) IS NULL

BEGIN

	RAISERROR(N'对象"%s"不存在',1,16,@tbname)

	RETURN

END

IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0

	AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0

	AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0

BEGIN

	RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)

	RETURN

END



--分页字段检查

IF ISNULL(@FieldKey,N'')=''

BEGIN

	RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)

	RETURN

END



--其他参数检查及规范

IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1

IF ISNULL(@PageSize,0)<1 SET @PageSize=10

IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'

IF ISNULL(@FieldOrder,N'')=N''

	SET @FieldOrder=N''

ELSE

	SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)

IF ISNULL(@Where,N'')=N''

	SET @Where=N''

ELSE

	SET @Where=N'WHERE ('+@Where+N')'



--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)

IF @PageCount IS NULL

BEGIN

	DECLARE @sql nvarchar(4000)

	SET @sql=N'SELECT @PageCount=COUNT(*)'

		+N' FROM '+@tbname

		+N' '+@Where

	EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT

	SET @PageCount=(@PageCount+@PageSize-1)/@PageSize

END



--计算分页显示的TOPN值

DECLARE @TopN varchar(20),@TopN1 varchar(20)

SELECT @TopN=@PageSize,

	@TopN1=(@PageCurrent-1)*@PageSize



--第一页直接显示

IF @PageCurrent=1

	EXEC(N'SELECT TOP '+@TopN

		+N' '+@FieldShow

		+N' FROM '+@tbname

		+N' '+@Where

		+N' '+@FieldOrder)

ELSE

BEGIN

	--处理别名

	IF @FieldShow=N'*'

		SET @FieldShow=N'a.*'



	--生成主键(惟一键)处理条件

	DECLARE @Where1 nvarchar(4000),@Where2 nvarchar(4000),

		@s nvarchar(1000),@Field sysname

	SELECT @Where1=N'',@Where2=N'',@s=@FieldKey

	WHILE CHARINDEX(N',',@s)>0

		SELECT @Field=LEFT(@s,CHARINDEX(N',',@s)-1),

			@s=STUFF(@s,1,CHARINDEX(N',',@s),N''),

			@Where1=@Where1+N' AND a.'+@Field+N'=b.'+@Field,

			@Where2=@Where2+N' AND b.'+@Field+N' IS NULL',

			@Where=REPLACE(@Where,@Field,N'a.'+@Field),

			@FieldOrder=REPLACE(@FieldOrder,@Field,N'a.'+@Field),

			@FieldShow=REPLACE(@FieldShow,@Field,N'a.'+@Field)

	SELECT @Where=REPLACE(@Where,@s,N'a.'+@s),

		@FieldOrder=REPLACE(@FieldOrder,@s,N'a.'+@s),

		@FieldShow=REPLACE(@FieldShow,@s,N'a.'+@s),

		@Where1=STUFF(@Where1+N' AND a.'+@s+N'=b.'+@s,1,5,N''),	

		@Where2=CASE

			WHEN @Where='' THEN N'WHERE ('

			ELSE @Where+N' AND ('

			END+N'b.'+@s+N' IS NULL'+@Where2+N')'



	--执行查询

	EXEC(N'SELECT TOP '+@TopN

		+N' '+@FieldShow

		+N' FROM '+@tbname

		+N' a LEFT JOIN(SELECT TOP '+@TopN1

		+N' '+@FieldKey

		+N' FROM '+@tbname

		+N' a '+@Where

		+N' '+@FieldOrder

		+N')b ON '+@Where1

		+N' '+@Where2

		+N' '+@FieldOrder)

END



-- 字符串缓存实现的通用分页存储过程(转自邹建)

CREATE PROC sp_PageView

@tbname     sysname,           	--要分页显示的表名

@FieldKey   sysname,           	--用于定位记录的主键(惟一键)字段,只能是单个字段

@PageCurrent int=1,             	--要显示的页码

@PageSize   int=10,            	--每页的大小(记录数)

@FieldShow  nvarchar(1000)='',  	--以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段

@FieldOrder  nvarchar(1000)='', 	--以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC

                                          用于指定排序顺序

@Where     nvarchar(1000)='',  	--查询条件

@PageCount  int OUTPUT        	--总页数

AS

DECLARE @sql nvarchar(4000)

SET NOCOUNT ON

--检查对象是否有效

IF OBJECT_ID(@tbname) IS NULL

BEGIN

	RAISERROR(N'对象"%s"不存在',1,16,@tbname)

	RETURN

END

IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0

	AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0

	AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0

BEGIN

	RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)

	RETURN

END



--分页字段检查

IF ISNULL(@FieldKey,N'')=''

BEGIN

	RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)

	RETURN

END



--其他参数检查及规范

IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1

IF ISNULL(@PageSize,0)<1 SET @PageSize=10

IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'

IF ISNULL(@FieldOrder,N'')=N''

	SET @FieldOrder=N''

ELSE

	SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)

IF ISNULL(@Where,N'')=N''

	SET @Where=N''

ELSE

	SET @Where=N'WHERE ('+@Where+N')'



--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)

IF @PageCount IS NULL

BEGIN

	SET @sql=N'SELECT @PageCount=COUNT(*)'

		+N' FROM '+@tbname

		+N' '+@Where

	EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT

	SET @PageCount=(@PageCount+@PageSize-1)/@PageSize

END



--计算分页显示的TOPN值

DECLARE @TopN varchar(20),@TopN1 varchar(20)

SELECT @TopN=@PageSize,

	@TopN1=@PageCurrent*@PageSize	



--第一页直接显示

IF @PageCurrent=1

	EXEC(N'SELECT TOP '+@TopN

		+N' '+@FieldShow

		+N' FROM '+@tbname

		+N' '+@Where

		+N' '+@FieldOrder)

ELSE

BEGIN

	SELECT @PageCurrent=@TopN1,

		@sql=N'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN

			+N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@FieldKey

			+N' as varchar(8000))),N'''''''') ELSE N'''' END FROM '+@tbname

			+N' '+@Where

			+N' '+@FieldOrder

	SET ROWCOUNT @PageCurrent

	EXEC sp_executesql @sql,

		N'@n int,@s nvarchar(4000) OUTPUT',

		@PageCurrent,@sql OUTPUT

	SET ROWCOUNT 0

	IF @sql=N''

		EXEC(N'SELECT TOP 0'

			+N' '+@FieldShow

			+N' FROM '+@tbname)

	ELSE

	BEGIN

		SET @sql=STUFF(@sql,1,1,N'')		

		--执行查询

		EXEC(N'SELECT TOP '+@TopN

			+N' '+@FieldShow

			+N' FROM '+@tbname

			+N' WHERE '+@FieldKey

			+N' IN('+@sql

			+N') '+@FieldOrder)

	END

END


--使用系统存储过程实现的通用分页存储过程(转自邹建)

CREATE PROC sp_PageView   

@sql         ntext,     --要执行的sql语句

@PageCurrent int=1,     --要显示的页码

@PageSize    int=10,    --每页的大小

@PageCount   int OUTPUT --总页数

AS

SET NOCOUNT ON

DECLARE @p1 int

--初始化分页游标

EXEC sp_cursoropen 

	@cursor=@p1 OUTPUT,

	@stmt=@sql,

	@scrollopt=1,

	@ccopt=1,

	@rowcount=@PageCount OUTPUT



--计算总页数

IF ISNULL(@PageSize,0)<1 

	SET @PageSize=10

SET @PageCount=(@PageCount+@PageSize-1)/@PageSize

IF ISNULL(@PageCurrent,0)<1 OR ISNULL(@PageCurrent,0)>@PageCount

	SET @PageCurrent=1

ELSE

	SET @PageCurrent=(@PageCurrent-1)*@PageSize+1



--显示指定页的数据

EXEC sp_cursorfetch @p1,16,@PageCurrent,@PageSize



--关闭分页游标

EXEC sp_cursorclose @p1
arrow_gx 2009-03-05
  • 打赏
  • 举报
 回复
http://blog.csdn.net/arrow_gx/archive/2008/05/26/2481506.aspx
JSP分页显示数据
实现分页功能，需要知道数据的总个数，每页应该有多少条数据，以及当前页码。假如总共有300条数据，每页20条，那么应该就有15页；假设有301条数据，每页20条，这时候就需要16页。因此，总页数可以这样计算：总页数=...
java实现数据分页展示
java实现分页显示数据分页分页分类分页实现大体流程实现 分页 在开发中我们经常需要从后端查询数据到前端页面展示，当我们的数据很多的时候就可以使用到分页技术，会使得我们的数据显示更加美观合理。 分页分类 有两...
ASP.NET（五）：ASP.net实现真分页显示数据
导读：在上篇文章中，介绍了用假分页实现数据的分页显示 ，而避免了去拖动滚动条。但，假分页在分页的同时，其实是拖垮了查询效率的。每一次分页都得重新查询一遍数据，那么有没有方法可以同时兼顾效率和分页呢，那...
java 内存分页_百万级数据，分页如何处理？
https://me.csdn.net/Danny_idea推荐阅读最近遇到了这么一个情况，数据库里面的数据由于长期的堆积，导致数据量不断的上升，而后台的系统每次进行分页查询的时候，效率都会降低很多。后来查看了一下之后，发现此时的...
beego数据的分页显示
分页的好处：如果没有分页，我们访问完数据就要全部在页面显示，有分页之后我们可以显示部分数据，好处有一下两点。 方便浏览，分页浏览可以更方便我们平常访问网页。 提高访问网站速度。如果一次性把数据全部从...
MS-SQL Server

34,597

社区成员

254,586

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章