老生常谈，关于分页的问题

zhgroup 2009-04-16 03:08:03
对于分页，我们都在用存储过程，但是当数据量比较大的时候，比如说数据量超过50W条的时候，分页的时候很明显有延迟，一般在2秒左右，当超过100W条数据的时候，这个延迟可能还要更长一些,我先贴一下我用的分页存储过程
sp_SplitPage(

 @Tables varchar( 1000 ) = ' ',	--表名称或级联名称

 @PrimaryKey varchar(100)= ' ',	--主键名称

 @Sort varchar(200)= ' ',		--排序列,含ASC/DESC

 @CurrentPage int = 1,	--当前页码

 @PageSize int = 10,		--每页显示记录数

 @Fields varchar(1000) =' ',	--读取的字段列表

 @Filter varchar(1000)= ' ',	--查询条件

 @Group varchar(1000) =' '	--聚合查询信息

)

AS

/*存储查询字符串*/

DECLARE @strSQL nvarchar( 2048 )

set @strSQL = ' '



/*存储查询条件*/

DECLARE @strFilters nvarchar( 1000 )

set @strFilters = ' '

/*存储排序字段*/

DECLARE @strSort nvarchar( 300 )

set @strSort = ' '

/*存储聚合查询*/

DECLARE @strGroup nvarchar( 1100 )

set @strGroup = ' '

if( @Filter IS NOT NULL AND @Filter != '' )

BEGIN

	if( @CurrentPage = 1 )

		set @strFilters = ' WHERE ( ' + @Filter + ' ) '

	ELSE

		set @strFilters = ' ( ' + @Filter + ' ) '

END





if( @Sort IS NOT NULL AND @Sort != '' )

BEGIN

	set @strSort = ' ORDER BY ' + @Sort

END



if( @Group IS not null AND @Group != '' )

	BEGIN

		set @strGroup = ' Group By ' + @Group

	END

ELSE

	BEGIN

		set @strGroup = ' '

	END



/*纠正查询字段信息*/

if( @Fields IS NULL OR @Fields = '' )

	set @Fields = ' * '



/*存储每页提取的记录数*/

DECLARE @strTop nvarchar( 10 )

set @strTOP = Convert( nvarchar(10),@PageSize )



print @strTOP



print @strGroup



/*如果是第一页,则直接提取*/

if( @CurrentPage = 1 )

BEGIN

 	set @strSQL = 'SELECT Top ' +@strTop + ' ' + @Fields + ' FROM ' + @Tables + @strFilters + @strSort + @strGroup 

END

ELSE

BEGIN

	set @strSQL = 'SELECT Top ' + convert( nvarchar(10),@PageSize ) + ' ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strFilters + ' AND (' + @PrimaryKey + ' NOT IN ( SELECT TOP ' + Convert( nvarchar(10),( ( @CurrentPage - 1 ) * @PageSize ) ) + ' ' + @PrimaryKey + ' FROM ' + @Tables + ' WHERE ' + @strFilters + @strSort + ' ) ) ' + @strSort

END



print @strSQL

/*执行查询*/

exec sp_executesql @strSQL

这个存储过程好像应该是应用比较广泛的吧，但是不知道大家都在用那个呢，这个存储过程现在就遇到瓶颈了，50W以上、100W以上，速度明显变慢啊,你们是如何解决这种大量数据分页的呢，欢迎参加讨论
...全文
507 45 打赏 收藏 转发到动态 举报
写回复
用AI写文章
45 条回复
切换为时间正序
请发表友善的回复…
发表回复
陌上花花 2009-04-19
  • 打赏
  • 举报
 回复
持续关注中
nosuchtracter 2009-04-19
  • 打赏
  • 举报
 回复
标记下
huming_h 2009-04-18
  • 打赏
  • 举报
 回复
顶。
AuC 2009-04-18
  • 打赏
  • 举报
 回复
路过
tddw 2009-04-17
  • 打赏
  • 举报
 回复
给你一个看看吧，呵呵，自己写的，用了一年多了。很稳定！
而且调用比你那个方便的多，五个参数而已。一直内部使用，第一次对外公开。



/***************************************************************

分页存储过程 

Create BY Tellov 2008-05-28

tellov@gmail.com

注意:使用本存储过程,SQL语句中必须要带有Order BY 子句



支持多表链接查询,支持别名,支持嵌套SELECT语句

注:语句中如果带有group by ,则页总数和记录数无法正确统计



-- Modify BY Tellov 2008-12-10 修正了多字段排序且序不同时分页会出现返回数据集不正确的问题

***************************************************************

参数说明:

1.@sql			查询语句

2.@CurrentPage	当前页

3.@PageSize		页大小

4.@Counts		查询到的记录总数

5.@pageCount	页总数

***************************************************************/

ALTER PROCEDURE [dbo].[Sp1_Exec_Page]

(

	@Sql nvarchar(4000),				-- 要执行的SQL语句

	@CurrentPage int = 1,			-- 当前页

	@PageSize int = 30,				-- 每页纪录数

	@counts int output,				-- 纪录总数

	@pageCount int output			-- 页总数

)

--WITH ENCRYPTION

AS



Set Lock_TimeOut 2000



declare @sort nvarchar(500)

declare @variableName nvarchar(300)

declare @setVariableName nvarchar(2000)

declare @where nvarchar(4000)

declare @temp nvarchar(max)

declare @tempwhere nvarchar(2000)

select @sql = replace(@sql,'  ',' ')

select @where = ''

select @setVariableName = ''

select @variableName = ''





-- 取出orderBy

if CHARINDEX(' order by ',@sql) = 0

begin

	select '使用此存储过程时查询语句中必须带上order by 子句';

end

else

begin

	declare @wherelocation int,@orderbylocation int,@groupbylocation int



	-- 取最后一个order by 的位置

	select @orderbylocation = CHARINDEX(' order by ',@sql)

	while CHARINDEX(' order by ',@sql,@orderbylocation+10) <> 0

	begin

		select @orderbylocation = CHARINDEX(' order by ',@sql,@orderbylocation+10)

	end



	-- 默认当前页

	IF @CurrentPage < 1

		SET @CurrentPage = 1



	-- 获取总行数

	select @temp = left(@Sql,@orderbylocation) 

	select @temp = 'Select @counts = count(1) from ( ' + @temp + ') as __tt'

	--print @temp

	exec sp_executesql @temp,N'@counts int output',@counts output



	-- 获取总分页数

	If @counts < @pageSize

		Select @pageCount = 1

	else

		Select @pageCount = ((@Counts-1) / @pageSize) +1



	-- 提高效率,如果是第一页直接返回

	if @CurrentPage = 1

	begin

		declare @returntemp nvarchar(4000)

		select @returntemp = 'select top ' + convert(nvarchar(20),@pageSize) + substring(@sql,CHARINDEX('select ',@sql) + 6,len(@sql))

		exec(@returntemp)

		--print @returntemp

	end

	else

	begin		

		-- 取最后一个WHERE的位置

		select @wherelocation = CHARINDEX(' where ',@sql)

		while @wherelocation <> 0 and CHARINDEX(' on ',@sql,@wherelocation + 7) <> 0

		begin

			select @wherelocation = CHARINDEX(' where ',@sql,@wherelocation + 7)

		end



		-- 取最后一个group by 的位置

		select @groupbylocation = CHARINDEX(' group by ',@sql)

		while CHARINDEX(' group by ',@sql,@groupbylocation+10) <> 0

		begin

			select @groupbylocation = CHARINDEX(' group by ',@sql,@groupbylocation+10)

		end



		select @sort = substring(@sql,@orderbylocation+10,len(@sql))



		-- 生成执行SQL时必须的语句

		select @sort = @sort + ','

		select @tempwhere = ''

		while CHARINDEX(',',@sort) > 0

		begin 

			select @temp = substring(@sort,0,CHARINDEX(',',@sort))

			select @temp = replace(@temp,' desc','')

			select @temp = replace(@temp,' asc','')

			select @setVariableName = @setVariableName + '@' + ltrim(replace(@temp,'.','')) + '=Convert(nvarchar(300),' + @temp + ',21),'

			select @variableName = @variableName + '@' + ltrim(replace(@temp,'.','')) + ' nvarchar(300),'

			--print @variableName

			if @tempwhere <> ''

			begin

				select @where = @where + ' or (' + right(@tempwhere,len(@tempwhere)-4)

			end



			select @where = @where + ' and ' + @temp

			if CHARINDEX(' desc',@sort) <> 0

			begin

				select @where = @where + ' <= @' +  ltrim(replace(@temp,'.',''))

			end

			else

			begin

				select @where = @where + ' >= @' +  ltrim(replace(@temp,'.',''))

			end

			

			if @tempwhere <> ''

			begin

				select @where = @where + ')'

			end



			select @tempwhere = @tempwhere + ' and ' + @temp + ' = @' + ltrim(replace(@temp,'.',''))



			select @sort = substring(@sort,CHARINDEX(',',@sort)+1,len(@sort))

		end



		select @variableName = left(@variableName,len(@variableName)-1)

		select @setVariableName = left(@setVariableName,len(@setVariableName)-1)

		select @where = '(' + substring(@where,6,len(@where))+ ')'



		--print @where

		--print @setVariableName

		--print @variableName



		-- 设置RowCount

		declare @strStartRow int

		Select @strStartRow = ((@CurrentPage - 1)*@PageSize + 1)

		Set RowCount @strStartRow



		--print @sql



		-- 最终执行Sql

		select @temp = 'DECLARE ' + @variableName + '

		SET ROWCOUNT ' + Convert(nvarchar(20),@strStartRow) + '

		Select ' + @setVariableName + substring(@Sql,charindex(' from ',@Sql),8000) + '

		SET ROWCOUNT ' + Convert(nvarchar(20),@PageSize) + '

		'

		--print @temp

		--print @sql

		--print @wherelocation



		if @wherelocation <> 0

			select @temp = @temp + STUFF(@sql,@wherelocation+7,0,@where + ' and ')

		else

		begin

			if @groupbylocation <> 0

				select @temp = @temp + STUFF(@sql,@groupbylocation + 1,0,' where ' + @where + ' ')

			else if @orderbylocation <> 0

				select @temp = @temp + STUFF(@sql,@orderbylocation + 1,0,' where ' + @where + ' ')

		end



		exec(@temp)



		--print @temp

	end

end
INCpig 2009-04-17
  • 打赏
  • 举报
 回复
学习中
陌上花花 2009-04-17
  • 打赏
  • 举报
 回复
学习下
gao512008 2009-04-17
  • 打赏
  • 举报
 回复
sutdy
zhgroup 2009-04-17
  • 打赏
  • 举报
 回复
怎么没人了呢
zhgroup 2009-04-17
  • 打赏
  • 举报
 回复 
-- 字符串缓存实现的通用分页存储过程(转自邹建)

ALTER   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

使用邹建的存储过程，条件如下:
declare @p int
exec sp_pageview 'shop','pid',3,10,'pid','pid','title like ''%电脑''',@p output
查询时间为14秒,title字段已经建立索引了
zzxap 2009-04-17
  • 打赏
  • 举报
 回复
Like '%keys%' 是不会用到索引的 改为Like '%keys'
itliyi 2009-04-17
  • 打赏
  • 举报
 回复
学习。。。
itcrazyman 2009-04-17
  • 打赏
  • 举报
 回复
1.用top top
2.关键列索引问题
zhgroup 2009-04-17
  • 打赏
  • 举报
 回复
最主要的就是对于很多字符串查询，比如说按标题查询，那么查询条件就应该是Title Like '%keys%'
这个是最耗费时间的，不知道大家有什么好的优化方法呢？
一弗楚 2009-04-17
  • 打赏
  • 举报
 回复
学习
悔说话的哑巴 2009-04-17
  • 打赏
  • 举报
 回复
[Quote=引用 18 楼 yiyeqiubo 的回复:]
我做的项目不是很大，觉得用Top挺方便的!
[/Quote]
我的也是
qq2013 2009-04-17
  • 打赏
  • 举报
 回复
ding
zhgroup 2009-04-17
  • 打赏
  • 举报
 回复
这个我觉得排序是一个非常影响查询速度的问题，即使你对排序字段建了索引，还有就是查询字段，对于字符串字段使用Like和=这两种操作符，在大量数据查询的时候性能影响很大
数据量在10W以下的应该看不出太大的影响
quanhy5 2009-04-17
  • 打赏
  • 举报
 回复
nx
yagebu1983 2009-04-16
  • 打赏
  • 举报
 回复
这要涉及到数据库的优化了。。。
东西太多了。。
例如：索引、exists等。。。
加载更多回复（23）
浅谈SQLServer数据库分页
数据库分页是老生常谈的问题了。如果使用ORM框架，再使用LINQ的话，一个Skip和Take就可以搞定。但是有时由于限制，需要使用存储过程来实数据库分页是老生常谈的问题了。如果使用ORM框架，再使用LINQ的话，一个Skip和...
浅谈SQL Server数据库分页
数据库分页是老生常谈的问题了。如果使用ORM框架，再使用LINQ的话，一个Skip和Take可以搞定。但是有时由于限制，需要使用存储过程来实现。在SQLServer中使用存储过程实现分页的已经有很多方法了。之前在面试中遇到过...
MongoDB实战之---初级篇
如何优化索引，提升查询效率更是老生常谈的问题。分布式集群如何有效利用空间来换取时间上的增量，达到百万并发。 学习完本课程后，同学们将对MongoDB有一个系统的认识，并且对如何应用MongoDB开发也会形成一套系统...
MySQL百万级数据量分页查询方法及其优化建议
数据库SQL优化是老生常谈的问题，在面对百万级数据量的分页查询，又有什么好的优化建议呢？下面将列举了一些常用的方法，供大家参考学习！ 方法1: 直接使用数据库提供的SQL语句 语句样式: MySQL中,可用如下方法: ...
应用架构设计“防火”经验分享
这里也想记录下来给更多的同学分享一下，当然很多都是老生常谈的常识，但是有时候不经意就会忘记一些血的教训。系统设计的时候总是估摸不到会有大数据量从远端传输过来（例如处理Http请求时，对于大附件内容的处理，...
.NET社区

62,046

社区成员

669,049

社区内容

发帖
与我相关
我的任务
社区描述
.NET技术交流专区
javascript云原生 企业社区
社区管理员
  • ASP.NET
  • .Net开发者社区
  • R小R
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告

.NET 社区是一个围绕开源 .NET 的开放、热情、创新、包容的技术社区。社区致力于为广大 .NET 爱好者提供一个良好的知识共享、协同互助的 .NET 技术交流环境。我们尊重不同意见，支持健康理性的辩论和互动，反对歧视和攻击。

希望和大家一起共同营造一个活跃、友好的社区氛围。

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

+ 用AI写文章