sqlserver2005利用临时表和@@RowCount提高分页查询存储过程性能示例分享

程序员文章站 2022-06-04 23:02:16

最近发现现有框架的通用查询存储过程的性能慢，于是仔细研究了下代码：复制代码代码如下:alter procedure [dbo].[areaselect] &...

最近发现现有框架的通用查询存储过程的性能慢，于是仔细研究了下代码：

alter procedure [dbo].[areaselect]
    @pagesize int=0,
    @currentpage int=1,
    @identifier int=null,
    @parentid int=null,
    @arealevel int=null,
    @children int=null,
    @areaname nvarchar(50)=null,
    @path nvarchar(max)=null,
    @status int=null,
    @alt int=null
as
begin
    set nocount on;
    if (not @areaname is null)    set @areaname='%'+@areaname+'%'
    if (not @path is null)    set @path='%'+@path+'%'
    if (@pagesize>0)
    begin
        declare @totalpage int
        select @totalpage=count(identifier) from area where
        (@identifier is null or identifier=@identifier)and
        (@parentid is null or parentid=@parentid)and
        (@arealevel is null or arealevel=@arealevel)and
        (@children is null or children=@children)and
        (@areaname is null or areaname like @areaname)and
        (@path is null or path like @path)and
        (@status is null or status=@status)and
        (@alt is null or alt=@alt)
        if(@totalpage%@pagesize=0)
        begin
            set @totalpage=@totalpage/@pagesize
        end
        else
        begin
            set @totalpage=round(@totalpage/@pagesize,0)+1
        end
        select top (@pagesize) identifier,parentid,arealevel,children,areaname,path,status,alt,@totalpage as totalpage from area where
        identifier not in (select top (@pagesize*(@currentpage-1))identifier from area where
        (@identifier is null or identifier=@identifier)and
        (@parentid is null or parentid=@parentid)and
        (@arealevel is null or arealevel=@arealevel)and
        (@children is null or children=@children)and
        (@areaname is null or areaname like @areaname)and
        (@path is null or path like @path)and
        (@status is null or status=@status)and
        (@alt is null or alt=@alt)
            order by areaname asc)
        and
        (@identifier is null or identifier=@identifier)and
        (@parentid is null or parentid=@parentid)and
        (@arealevel is null or arealevel=@arealevel)and
        (@children is null or children=@children)and
        (@areaname is null or areaname like @areaname)and
        (@path is null or path like @path)and
        (@status is null or status=@status)and
        (@alt is null or alt=@alt)
            order by areaname asc
    end
    else
    begin
        select identifier,parentid,arealevel,children,areaname,path,status,alt from area where
        (@identifier is null or identifier=@identifier)and
        (@parentid is null or parentid=@parentid)and
        (@arealevel is null or arealevel=@arealevel)and
        (@children is null or children=@children)and
        (@areaname is null or areaname like @areaname)and
        (@path is null or path like @path)and
        (@status is null or status=@status)and
        (@alt is null or alt=@alt)
            order by areaname asc
    end
end

发现每次查询都需要按条件查询依次area表，性能太低，于是利用临时表将符合条件的记录取出来，然后针对临时表进行查询，代码修改如下：
alter procedure [dbo].[areaselect]
    @pagesize int=0,
    @currentpage int=1,
    @identifier int=null,
    @parentid int=null,
    @arealevel int=null,
    @children int=null,
    @areaname nvarchar(50)=null,
    @path nvarchar(max)=null,
    @status int=null,
    @alt int=null
as
begin
    set nocount on;
    if (not @areaname is null)    set @areaname='%'+@areaname+'%'
    if (not @path is null)    set @path='%'+@path+'%'


    if (@pagesize>0)
    begin
        --创建临时表
        select
        identifier,parentid,arealevel,children,areaname,path,status,alt
        into #temp_area
        from area where
        (@identifier is null or identifier=@identifier)and
        (@parentid is null or parentid=@parentid)and
        (@arealevel is null or arealevel=@arealevel)and
        (@children is null or children=@children)and
        (@areaname is null or areaname like @areaname)and
        (@path is null or path like @path)and
        (@status is null or status=@status)and
        (@alt is null or alt=@alt)
        order by areaname asc

        declare @totalpage int
        declare @sumcount int

        --取总数
        select @sumcount=count(identifier) from #temp_area

        if(@sumcount%@pagesize=0)
        begin
            set @totalpage=@sumcount/@pagesize
        end
        else
        begin
            set @totalpage=round(@sumcount/@pagesize,0)+1
        end
        select top (@pagesize) identifier,parentid,arealevel,children,areaname,
        path,status,alt,@totalpage as totalpage,@sumcount as sumcount
        from #temp_area
        where
        identifier not in (select top (@pagesize*(@currentpage-1))identifier from #temp_area))
    end
    else
    begin
        select identifier,parentid,arealevel,children,areaname,path,status,alt from area where
        (@identifier is null or identifier=@identifier)and
        (@parentid is null or parentid=@parentid)and
        (@arealevel is null or arealevel=@arealevel)and
        (@children is null or children=@children)and
        (@areaname is null or areaname like @areaname)and
        (@path is null or path like @path)and
        (@status is null or status=@status)and
        (@alt is null or alt=@alt)
            order by areaname asc
    end
end

经过使用临时表的确提高性能，不过有发现一个问题，就是count(identifier)的确很耗性能，于是又进行修改了
：

        select identifier,parentid,arealevel,children,areaname,path,status,alt from area where
        (@identifier is null or identifier=@identifier)and
        (@parentid is null or parentid=@parentid)and
        (@arealevel is null or arealevel=@arealevel)and
        (@children is null or children=@children)and
        (@areaname is null or areaname like @areaname)and
        (@path is null or path like @path)and
        (@status is null or status=@status)and
        (@alt is null or alt=@alt)
        order by areaname asc
    end
end