两个分页存储过程的效率比较(均为多表查询)

--解密存储过程

exec sp_decrypt 'p_page'

 

use Newt8c8

go

 

if exists(select * from dbo.sysobjects where name='p_page')

drop procedure p_page

go

 

/**分页的存储过程*/

create procedure p_page

(

    @Tables varchar(1000),      --表名,可以是多个表,但不能用别名

    @PrimaryKey varchar(100),   --主键,可以为空,但@Order为空时该值不能为空

    @Fields varchar(1000) = '*', --查询字段(多个表的字段),为空表示select *

    @PageSize int = 15,         --每页记录数

    @CurrentPage int = 1,       --当前页，表示第页

    @Filter varchar(1000) = null,--条件，可以为空，不用填where

    @Group varchar(1000) = null, --分组依据，可以为空，不用填group by

    @Sort varchar(200) = null,  --排序,默认按主键升序排列,不用填order by

    @TotalPage int output       --总页数

)

with encryption      --加密

 

as

set nocount on

declare @intResult int

 

begin tran

 

declare @sql nvarchar(4000)

if @Filter is null or @Filter=''

    set @Sql = 'select @intResult = count(' + @PrimaryKey + ') from ' + @Tables

else

    set @Sql = 'select @intResult = count(' + @PrimaryKey + ') from ' + @Tables

              + ' where + ' + @Filter

 

exec sp_executesql @sql,N'@intResult int output',@intResult output

select @TotalPage= ceiling((@intResult+0.0)/@PageSize)

 

 

if @Sort is null or @Sort = ''

    set @Sort = @PrimaryKey

 

declare @SortTable varchar(100)

declare @SortName varchar(100)

declare @strSortColumn varchar(200)

declare @operator char(2)

declare @type varchar(100)

declare @prec int

 

if charindex('desc',@Sort)>0

    begin

       set @strSortColumn = replace(@Sort, 'desc', '')

       set @operator = '<='

    end

else

    if charindex('asc', @Sort) > 0

       begin

           set @strSortColumn = replace(@Sort, 'asc', '')

           set @operator = '>='

       end

    else

       begin

           set @strSortColumn = @SORT

           set @operator = '>='

       end

 

if charindex('.', @strSortColumn) > 0

    begin

       set @SortTable = substring(@strSortColumn, 0, charindex('.',@strSortColumn))

       set @SortName=substring(@strSortColumn,charindex('.',@strSortColumn)+1,

                            len(@strSortColumn))

    end

else

    begin

       set @SortTable = @Tables

       set @SortName = @strSortColumn

    end

 

select @type=t.name, @prec=c.prec

from sysobjects o

join syscolumns c on o.id=c.id

join systypes t on c.xusertype=t.xusertype

where o.name = @SortTable and c.name = @SortName

 

if charindex('char', @type) > 0

    set @type = @type + '(' + cast(@prec as varchar) + ')'

 

declare @strPageSize varchar(50)

declare @strStartRow varchar(50)

declare @strFilter varchar(1000)

declare @strSimpleFilter varchar(1000)

declare @strGroup varchar(1000)

 

if @CurrentPage < 1

    set @CurrentPage = 1

 

set @strPageSize = cast(@PageSize as varchar(50))

set @strStartRow = cast(((@CurrentPage - 1)*@PageSize + 1) as varchar(50))

 

if @Filter is not null and @Filter != ''

    begin

       set @strFilter = ' where ' + @Filter + ' '

       set @strSimpleFilter = ' and ' + @Filter + ' '

    end

else

    begin

       set @strSimpleFilter = ''

       set @strFilter = ''

    end

 

if @Group is not null and @Group != ''

    set @strGroup = ' group by ' + @Group + ' '

else

    set @strGroup = ''

 

set @sql = 'declare @SortColumn ' + @type + ' set rowcount ' + @strStartRow

           + ' select @SortColumn= '+ @strSortColumn + ' from ' + @Tables

           + @strFilter + ' ' + @strGroup + ' order by ' + @Sort + ' set rowcount '

           + @strPageSize + ' select ' + @Fields + ' from ' + @Tables + ' where '

           + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' '

           + @strGroup + ' order by ' + @Sort + ''

exec(@sql)

print @sql

if @@Error <> 0

    begin

       RollBack Tran

       Return -1

    end

else

    begin

       commit Tran

       return @intResult

    end

 

select count(1) from song

 

--测试数据(音乐专辑)

declare

@return_value int,

@TotalPage int,

@timediff datetime

select @timediff=getdate()

exec @return_value=p_page

@Tables='MusicAlbum left outer join Singer on MusicAlbum.SingerID=Singer.RecordID

       left outer join MusicArea on Singer.MusicAreaID=MusicArea.RecordID',

@PrimaryKey='MusicAlbum.RecordID',

@Fields='MusicAlbum.RecordID,MusicAlbum.SingerID,MusicAlbum.Name

       as AlbumName,Singer.Name as SingerName,MusicArea.Name as MusicArea,

       MusicAlbum.SongList,MusicAlbum.IntroductionSmall,MusicAlbum.IssueDate,

       MusicAlbum.Label,MusicAlbum.CreateDate',

@PageSize=20,

@CurrentPage=1,

@Filter='',

@Group='',

@Sort='MusicAlbum.CreateDate desc',

@TotalPage=@TotalPage output

select @TotalPage as N'@TotalPage'

select 'Return Value' = @return_value

select datediff(ms,@timediff,GetDate()) as 耗时

 

--查看CPU用时和占用时间,以此优化存储过程

set statistics io on/off

set statistics time on/off

 

--测试数据(流行歌曲)

declare

@return_value int,

@TotalPage int,

@timediff datetime

select @timediff=Getdate()

exec @return_value=p_page

@Tables='Song left outer join Singer on Song.SingerIDList=Singer.RecordID

       left outer join MusicArea on Singer.MusicAreaID=MusicArea.RecordID',

@PrimaryKey='Song.RecordID',

@Fields='Song.RecordID,MusicArea.Name as Area,Song.Name as songName,

       Singer.Name as singerName,Song.State as IsOpen,

       Song.CreateDate as Date,Song.EditPeople as Editor',

@PageSize=20,

@CurrentPage=15244,

@Filter='',

@Group='',

@Sort='Song.CreateDate desc',

@TotalPage=@TotalPage output

select @TotalPage as N'@TotalPage'

select 'Return Value' = @return_value

select datediff(ms,@timediff,GetDate()) as 耗时

 

 

--查看CPU用时和占用时间,以此优化存储过程

set statistics io on

set statistics time on

 

   

 

 

set ansi_nulls on

go

 

set quoted_identifier on

go

 

use Newt8c8

go

 

if exists(select * from dbo.sysobjects where name='page_2005')

drop procedure page_2005

go

 

create proc page_2005

     @TableName varchar(500),          --表名

     @Fields varchar(5000) = '*',      --字段名(全部字段为*)

     @OrderField varchar(5000),         --排序字段(必须!支持多字段)

     @sqlWhere varchar(5000) = Null,   --条件语句(不用加where)

     @pageSize int,                    --每页多少条记录

     @pageIndex int = 1 ,               --指定当前为第几页

     @totalRecord int = 0,             --总记录数

     @TotalPage int output             --返回总页数

 

as

begin

    Begin tran --开始事务

       declare @sql nvarchar(4000);

       if @totalRecord<=0

       begin

           --计算总记录数

           if (@SqlWhere='' or @sqlWhere=null)

              set @sql = 'select @totalRecord = count(*) from ' + @TableName

           else

              set @sql = 'select @totalRecord = count(*) from '

                         + @TableName + ' with(nolock) where ' + @sqlWhere

           exec sp_executesql @sql,N'@totalRecord int output',@totalRecord output--计算总记录数  

       end

       --计算总页数

       select @TotalPage=ceiling((@totalRecord+0.0)/@PageSize)

       if (@SqlWhere='' or @sqlWhere=NULL)

           set @sql = 'select * from (select row_number() over(order by ' + @OrderField

                     + ') as rowId,' + @Fields + ' from ' + @TableName

       else

       set @sql = 'select * FROM (select row_number() over(order by ' + @OrderField

                  + ') as rowId,' + @Fields + ' from ' + @TableName + ' with(nolock) where '

                  + @SqlWhere

       --处理页数超出范围情况

       if @PageIndex<=0

           set @pageIndex = 1

       if @pageIndex>@TotalPage

           set @pageIndex = @TotalPage

       --处理开始点和结束点

       declare @StartRecord int

       declare @EndRecord int

       set @StartRecord = (@pageIndex-1)*@PageSize + 1

       set @EndRecord = @StartRecord + @pageSize - 1

       --继续合成sql语句

       set @Sql = @Sql + ') as t where rowId between ' + Convert(varchar(50),@StartRecord)

                  + ' and ' +  Convert(varchar(50),@EndRecord)

       print @sql

       exec(@Sql)

       ---------------------------------------------------

       If @@Error <> 0

           begin

              rollBack tran

              return -1

           end

       else

           begin

              commit tran

              return @totalRecord ---返回记录总数

           end

    end

go

 

 

--测试数据

declare

@return_value int,

@totalRecord int,

@TotalPage int,

@timediff datetime

select @totalRecord=count(*) from Song

select @timediff=Getdate()

exec @return_value=page_2005

@TableName='Song left outer join Singer on Song.SingerIDList=Singer.RecordID

       left outer join MusicArea on Singer.MusicAreaID=MusicArea.RecordID',

@Fields='Song.RecordID,MusicArea.Name as Area,Song.Name as songName,

       Singer.Name as singerName,Song.State as IsOpen,

       Song.CreateDate as Date,Song.EditPeople as Editor',

@sqlWhere='',

@OrderField='Song.RecordID',

@pageSize=20,

@pageIndex=15244,

@totalRecord=@totalRecord,

@TotalPage=@TotalPage output

select @TotalPage as N'@TotalPage'

select 'Return Value' = @return_value

select datediff(ms,@timediff,GetDate()) as 耗时

 

 

 

p_page和page_2005的分页效率比较(均支持多表查询):

1.    总体查询效率(查询所有数据时):

P_page用时:12898ms

page_2005用时:10256ms

2. 查询前20条数据效率:

P_page用时:1330ms

page_2005用时:3ms

3. 中间查询效率(查询到400条数据时):

P_page用时:1393ms

page_2005用时:76ms

4. 中间查询效率(查询到4000条数据时):

P_page用时:1310ms

page_2005用时:790ms

5. 中间查询效率(查询到40000条数据时):

P_page用时:1266ms

page_2005用时:773ms

6. 中间查询效率(查询到400000条数据时):

P_page用时:890ms

page_2005用时:5936ms

7. 查询到最后一页时:

P_page用时:900ms

page_2005用时:5966ms

总结：如果数据在50万条以上，要求多表（多字段）查询，且最后10页的查询也要快,则使用p_page分页存储过程,反之则用page_2005