数据库教程:分页存储过程效率对比

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

需要了解更多数据库技术:分页存储过程效率对比，都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!