分页存储过程效率对比

1. 随便找了个网上效率被认为比较高的分页过程

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xp_getpager_user_dt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)  
    drop procedure [dbo].[xp_getpager_user_dt]  
    GO  
    CREATE PROCEDURE [dbo].[xp_getpager_user_dt]  
    @pcount int output,    --总页数输出  
    @rcount int output,    --总记录数输出  
    @tablename nvarchar(100),    --查询表名  
    @keys varchar(50),        --主键  
    @fields nvarchar(500),    --查询字段  
    @where nvarchar(3000),    --查询条件  
    @sortfields nvarchar(100),    --排序字段  
    @beginindex int=0,        --开始位置  
    @pageindex int=1,        --当前页数  
    @pagesize int=100        --页大小  
    AS  
    SET NOCOUNT ON  
    SET ANSI_WARNINGS ON  
    IF @pagesize < 0 OR @pageindex < 0  
    BEGIN          
    RETURN  
    END  
    DECLARE @new_where1 NVARCHAR(3000)  
    DECLARE @new_order1 NVARCHAR(100)  
    DECLARE @new_order2 NVARCHAR(100)  
    DECLARE @Sql NVARCHAR(4000)  
    DECLARE @SqlCount NVARCHAR(4000)  
    DECLARE @Top int  
    if(@beginindex <=0)  
        set @beginindex=0  
    else  
        set @beginindex=@beginindex-1  
    IF ISNULL(@where,'') = ''  
        SET @new_where1 = ' '  
    ELSE  
        SET @new_where1 = ' WHERE ' + @where  
    IF ISNULL(@sortfields,'') <> ''   
    BEGIN  
        SET @new_order1 = ' ORDER BY ' + Replace(@sortfields,'desc','')  
        SET @new_order1 = Replace(@new_order1,'asc','desc')  
        SET @new_order2 = ' ORDER BY ' + @sortfields  
    END  
    ELSE  
    BEGIN  
        SET @new_order1 = ' ORDER BY ID DESC'  
        SET @new_order2 = ' ORDER BY ID ASC'  
    END  
    SET @SqlCount = 'SELECT @rcount=COUNT(1),@pcount=CEILING((COUNT(1)+0.0)/'  
                + CAST(@pagesize AS NVARCHAR)+') FROM ' + @tablename + @new_where1  
    EXEC SP_EXECUTESQL @SqlCount,N'@rcount INT OUTPUT,@pcount INT OUTPUT',  
                   @rcount OUTPUT,@pcount OUTPUT  
    IF @pageindex > CEILING((@rcount+0.0)/@pagesize)    --如果输入的当前页数大于实际总页数,则把实际总页数赋值给当前页数  
    BEGIN  
        SET @pageindex =  CEILING((@rcount+0.0)/@pagesize)  
    END  
    set @sql = 'select '+ @fields +' from ' + @tablename + ' w1 '  
        + ' where '+ @keys +' in ('  
            +'select top '+ ltrim(str(@pagesize)) +' ' + @keys + ' from '  
            +'('  
                +'select top ' + ltrim(STR(@pagesize * @pageindex + @beginindex)) + ' ' + @keys + ' FROM '  
            + @tablename + @new_where1 + @new_order2   
            +') w ' + @new_order1  
        +') ' + @new_order2  
    print(@sql)  
    Exec(@sql)  
    GO 

2. 优化后的分页过程

create PROC [dbo].[xp_GetPager_user_dt2]    
@quitdate nvarchar(10)='2015-01-01',
@userno nvarchar(10)='',
@sortfields nvarchar(100)='',
@pageindex int=1,
@pagesize int=5
AS
begin  

--构建执行脚本
declare @sql nvarchar(1800)='',
        --存储对象
        @tablename NVARCHAR(50)=' v_pn_users_fromlocal',
        --返回字段
        @returnfields nvarchar(1000)='',
        --where 条件
        @where nvarchar(200)=' where 1=1 ',--and abs([Status]) >= 10 and [Status] <> 40  and isvalid<>-1 ,
        --上次查询数量
        @lastcount int =-1  

        --计算前面查询的数据总数
        set @lastcount=(@pageindex-1)*@pagesize
        if @lastcount<0 set @lastcount=0
        --判断排序字段
        if @sortfields=''
          set @sortfields='quitdate'
          
 
--*******************************************返回字段设定*****************************************
set @returnfields='code,name,fname,email,isvalid,hiredate,hirevalid,quitdate,costcenter,sex,IDCard,PassDate '
--*******************************************由条件构建Where***************************************
 
if @quitdate<>'2015-01-01'
   set @where+=' and quitdate  > '''+@quitdate+''''
if @userno<>''
   set @where+=' and code = '''+@userno+''''  
 
--*******************************************由条件构建SQL***************************************
set @sql='

declare @lastmaxid int=0,@total int
--缓存的临时表，并创建检索rowID
if object_id(''tempdb..#t'') is not null
    drop table #t

select row_number() over (order by '+@sortfields+') rowid, * into #t from '+@tablename

set @sql += @where

--获取按照执行排序，前面查询的数据的最大ID
set @sql +=' select top '+cast(@lastcount as nvarchar)+'  @lastmaxid=max(rowid) from #t'
set @sql+= ' group by rowid'

--获取符合条件的数据总数
set @sql+=' select @total=count(*) from #t'

--**********************************************构造执行返回结果的SQL*****************************************
set @sql+=' select '
if @pagesize>0
   set @sql+='top '+cast(@pagesize as nvarchar)
   set @sql+=' rowid '
   
   if @returnfields<>''
   begin
        set @sql+=','+@returnfields        
   end            
   set @sql+=' from #t
            where rowid>@lastmaxid order by rowid'

set @sql+=' select @total total,'+cast(@pagesize as nvarchar)+' pagesize,'+cast(@pageindex as nvarchar)+' pageindex'

set @sql+=' if object_id(''tempdb..#t'') is not null
    drop table #t'
exec(@sql)  

END

效率对比

1. declare @pcount int,@rcount int
exec [dbo].[xp_getpager_user_dt]  
    @pcount  output,    --总页数输出  
    @rcount  output,    --总记录数输出  
    @tablename ='v_pn_users_fromlocal',    --查询表名  
    @keys ='code',        --主键  
    @fields ='code,name,fname,email,isvalid,hiredate,hirevalid,quitdate,costcenter,sex,IDCard,PassDate',    --查询字段  
    @where ='quitdate>''2015-01-01''',    --查询条件  
    @sortfields ='quitdate',    --排序字段  
    @beginindex =0,        --开始位置  
    @pageindex =1,        --当前页数  
    @pagesize =100

执行时间：1 min 18s

2. [xp_GetPager_user_dt2] @quitdate='2015-01-01',@pagesize=100

执行时间：<1s