最近发现现有框架的通用查询存储过程的性能慢，于是仔细研究了下代码：
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