none
使用row_number()进行分页的疑问 RRS feed

  • 问题

  • 随着页码数字的增加,会导致速度越来越慢,我发现“where row_numbers>(@pageSize*(@pageindex-1))”这段如果数字比较大的话,效率明显下降很多,是不是因为这个字段没有索引所导致在筛选的时候会全表检索而使得速度变得越来越慢。

    CREATE procedure pro_A
    (

    @pageIndex int
    @pageSize tinyint
    )
    as

    select top(@pageSize) *
    from
    (
    select *,row_number() over(order by pm_plancode asc) as row_numbers
    from v_planmembers
    ) as t
    where row_numbers>(@pageSize*(@pageindex-1))

    2012年6月27日 13:16

全部回复

  • 个人对这个函数用于分页是比较讨厌的,因为一般很少人会去考虑怎么用才是合理的(我是看到了一堆人用这个写了个通用的分页,还沾沾自喜的到处传播)

    从执行计划上年,row_number是在查询结果集上去算这个值的,所以你的页越靠后,需要读取的数据就越多,自然也就越慢

    正确的用法是只对主键分页,对分页得到的主键再去关联明细数据,也就是应该用类似下面的这种处理

    with
    id as(
    	select 
    		row_id = row_number() over(order by pm_plancode asc)
    		id	-- 主键列名
    	from v_planmembers
    )
    select 
    	data.*
    from v_planmembers data
    	inner join id
    		on id.id = data.id
    where id.row_id >(@pageSize*(@pageindex-1))
    	and id.row_id < (@pageSize*(@pageindex))
    ;

    2012年6月28日 0:03
  • 用主键分页固然是性能提高,但是考虑一下某些场景,如果是需要根据某字段排序之后在进行分页,那么使用主键分页就有问题了。因为这时候主键的顺序已经打乱,已经不能根据主键的数字大小来判断顺序了。
    2012年6月28日 2:25
  • select row_id = row_number() over(order by pm_plancode asc) id -- 主键列名 from v_planmembers

    这段是不是也要对整表进行扫描的,会不会这块会影响性能

    2012年6月28日 2:31
  • 我试了你的方法,速度是快了近1倍。但是还是要近30秒时间,而且也会随着pageindex的数字上升导致速度严重变慢。虽然我发现真正影响速度的是v_planmembers这个view里join了2个表,不管是删除B还是C,都会使速度变得可用。假设B是一个view,这个view里使用了union all链接了两个表,我觉得是这个view影响了性能。

    select 
    from A a
    join B b on a.xx=b.xx
    join  C c on c.xxx=a.xxx

    2012年6月28日 2:48
  • 可以用indexed view来提高性能。不过会影响insert/delete/update的性能,增加空间占用。要仔细权衡利弊。

    想不想时已是想,不如不想都不想。

    2012年6月28日 3:09
    版主
  • @zjcxc.邹建

    "只对主键分页,对分页得到的主键再去关联明细数据" 思路感觉蛮好的,不过你的写法并没有体现你的说法,还是需要对整个表进行扫描排列的。


    2012年6月28日 4:34
  • @怡红公子

    除了indexed view,直接用为pm_plancode 建INDEX应该也可以的吧,因为就一个表嘛 ,其他的需要的列作为包含列。



    2012年6月28日 4:36
  • 你这个表的数据量大概是多少?

    数据量不是很大的话,直接给pm_plancode加上索引即可。

    2012年6月28日 6:03
    版主
  • @怡红公子

    除了indexed view,直接用为pm_plancode 建INDEX应该也可以的吧,因为就一个表嘛 ,其他的需要的列作为包含列。




    你哪里看见是一个表了?楼主不是说了吗,v_planmembers这个view里join了2个表

    想不想时已是想,不如不想都不想。

    2012年6月28日 9:07
    版主
  • 不好意思,看错了,这么说来,只能用indexed view才能取得比较好的性能了。
    2012年6月28日 11:33
  • 可以对pm_plancode加上聚集索引,这样分页的时候应该不会慢

    给我写信: QQ我:点击这里给我发消息

    2012年6月28日 12:04
  • 用到的字段都已经加了索引,结果的都一样。关键还是在view里面自身的问题。但是在之前要生成row_number,总是要扫描整表,这个可真是个问题。
    2012年6月29日 6:50
  • 用以下语句分析一下,或者看一下执行计划

    set statistics profile on 
    set statistics io on

    或者看一下这篇帖子

    http://topic.csdn.net/u/20100712/09/196befc8-ef1c-4493-9ba6-0a30d0c63340.html

    分析了row_number() over的作分页的性能


    给我写信: QQ我:点击这里给我发消息


    2012年6月29日 7:35
  • 一般我都是用主表、临时表做分页的因为支持sql2000,比较通用,性能还可以

    USE [GPOSDB]
    GO
    /****** 对象:  StoredProcedure [dbo].[CT_Append_Get]    脚本日期: 06/29/2012 15:26:36 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -----------------------------------------------------------------
    -- Name: CT_Append Store Procedure
    -- Author: -- DateTime: 2011-07-22
    -- Description: Data TableCT_Append , Get List
    -----------------------------------------------------------------
    /*
    @PageIndex 
    @TotalRecords 
    */	
    ALTER PROCEDURE [dbo].[CT_Append_Get]
    (
    	@PageIndex int,
    	@TotalRecords int
    )
    AS
    BEGIN
        DECLARE @Page int
    	DECLARE @PageLowerBound int
    	DECLARE @PageUpperBound int
    	DECLARE @RowsToReturn int
    
    	SET @Page = (@PageIndex - 1)
    
    	-- First set the rowcount
    	SET @RowsToReturn = @TotalRecords * (@Page + 1)
    	SET ROWCOUNT @RowsToReturn
    
    	-- Set the page bounds
    	SET @PageLowerBound = @TotalRecords * @Page
    	SET @PageUpperBound = @PageLowerBound + @TotalRecords + 1
    
    	-- Create a temp table to store the select results
    	CREATE TABLE #PageIndex 
    	(
    		IndexId int IDENTITY (1, 1) NOT NULL,
    		VC_A_SNNO INT 
    	)
    	
    	INSERT INTO #PageIndex (VC_A_SNNO)
    	SELECT 
    		[VC_A_SNNO]
    	FROM 
    		[CT_Append]
    		
        
    	SELECT
    		c.VC_A_SNNO,
    		[VC_A_AppendType] ,
    		[VC_A_CardNO] ,
    		[I_A_CardType] ,
    		[I_A_PointToOil] ,
    		[VC_TicketType] ,
    		[VC_TicketNO] ,
    		[DE_A_BAmount] ,
    		[DE_A_AppendAmount] ,
    		[DE_A_AAmount] ,
    		[D_A_AppendDateTime] ,
    		[VC_A_Remark] ,
    		[VC_A_OperatorNO] 
    	FROM [CT_Append] c , #PageIndex PageIndex
    	WHERE
    		c.VC_A_SNNO = PageIndex.VC_A_SNNO AND
    		PageIndex.IndexID > @PageLowerBound AND
    		PageIndex.IndexID < @PageUpperBound
    	
    	SELECT COUNT(VC_A_SNNO) AS TotalRecords FROM [CT_Append]
    	
    END
    
    


    给我写信: QQ我:点击这里给我发消息

    2012年6月29日 7:37
  • SQL Server 2012 已经原生支持 Paging.  http://msdn.microsoft.com/en-us/library/ms188385.aspx

    SELECT *
    FROM v_planmembers
    ORDER BY pm_plancode ASC
    OFFSET @pageSize*(@pageIndex-1) ROWS FETCH NEXT @pageSize ROWS ONLY

    2012年7月2日 2:48
  • 2012还没有正式版吧
    2012年7月2日 16:19
  • 还是RTM版,没有正式版


    给我写信: QQ我:点击这里给我发消息

    2012年7月3日 1:15
  • RTM is formal release.
    2012年7月3日 1:43
  • RTM(Release to Manufacturing)版

    软件在正式在零售商店上架前,需要一段时间来压片,包装、配销,所以程序代码必须在正式发行前一段时间就要完成,这个完成的程序代码叫做final.code,程序代码开发完成之后,要将母片送到工厂大量压片,这个版本就叫做rtm版。rtm版的程序码和正式版一样。但是和正式版也有不一样的地方:例如正式版中的oem不能升级安装,升级版要全新安装的话会检查旧版操作系统光盘等,这些就是Rtm和正式版不同的地方,但是它们的主要程序代码都是一样的。严格的说这种版本还是属于fpp零售版,需要激活。不过一般都习惯把正式版就称为RTM版。

    给我写信: QQ我:点击这里给我发消息

    2012年7月3日 3:33
  • SQL Server 2012 已经原生支持 Paging.  http://msdn.microsoft.com/en-us/library/ms188385.aspx

    SELECT *
    FROM v_planmembers
    ORDER BY pm_plancode ASC
    OFFSET @pageSize*(@pageIndex-1) ROWS FETCH NEXT @pageSize ROWS ONLY


    测试了,这个分页和 row_number() 分页差不多,没有针对分页做专门的性能优化的,比较郁闷
    2012年7月26日 1:46