欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-10-31 08:51:34
最近发现现有框架的通用查询存储过程的性能慢,于是仔细研究了下代码: 复制代码 代码如下: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)的确很耗性能,于是又进行修改了

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 @sumcount int

        --创建临时表
        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
        --设置总记录数为刚操作的记录数
        set @sumcount=@@rowcount

        declare @totalpage int

        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