存储过程优化

Question

ALTER PROCEDURE [dbo].[App_SellHousePeople] 
@Name varchar(30)=null,
@id varchar(50)=null,
@cishu nvarchar(20)= null,
@qu nvarchar(20) = null,
@leixing nvarchar(20) = null,
@countryname nvarchar(16) = null,
@cityname nvarchar(32) = null,
@startdate datetime,
@enddate datetime
ascreate table #table1
(
 PeopleID int,
 allcount int
)
insert into #table1 SELECT dbo.Peoples.PeopleID, COUNT(dbo.Deals.BuyID) AS allcount
FROM dbo.Deals INNER JOIN
      dbo.Peoples ON dbo.Deals.BuyID = dbo.Peoples.PeopleID
      where Deals.CheckTime >= @startdate and Deals.CheckTime <= @enddate
GROUP BY dbo.Peoples.PeopleID
declare @sql varchar(8000)
set @sql='select distinct  p.Name as 姓名 ,Country.CountryName as country ,Citys.CityName as house,p.CardNumber as 身份证,t1.allcount as 次数 from Peoples p inner join
#table1 t1 on t1.PeopleID = p.PeopleID INNER JOIN
      Deals AS d ON d.BuyID = p.PeopleID INNER JOIN
      Country ON p.CountryID = Country.CountryID INNER JOIN
      Citys ON p.CityID = Citys.CityCode  INNER JOIN
dbo.Houses AS h ON d.HouseID = h.HouseID left join HouseType ht on ht.TypeID = h.HouseTypeID where '
set @sql = @sql + ' d.CheckTime>='''+cast(@startdate as nvarchar(10))+''' and'
set @sql = @sql + ' d.CheckTime<='''+cast(@enddate as nvarchar(10))+''''if @Name <> ''
set @sql=@sql+' and p.Name like '''+@Name+'%'''
if @id <> ''
set @sql=@sql+' and p.CardNumber like ''%'+@id+'%'''
if @countryname <> ''
set @sql =@sql+' and Country.CountryName = '''+@countryname+''''
if @cityname <> ''
set @sql =@sql+' and Citys.CityName = '''+@cityname+''''
if @cishu <> ''
if @cishu = '首次交易者'
set @sql=@sql+' and t1.allcount = 1'
if @cishu = '2次交易者'
set @sql=@sql+' and t1.allcount= 2'
if @cishu = '3-5次交易者'
set @sql=@sql+' and t1.allcount in (3,4,5)'
if @cishu = '5-10次交易者'
set @sql=@sql+' and t1.allcount in (6,7,8,9,10)'
if @cishu = '10次以上交易者'
set @sql=@sql+' and t1.allcount > 10'
if @qu <> ''
set @sql=@sql+' and h.qu='''+@qu+''''if @leixing <> ''
begin
if @leixing = '公寓'
set @sql=@sql+' and ht.HouseTypeName in (''新工房'',''公寓'')'
if @leixing = '别墅'
set @sql=@sql+' and ht.HouseTypeName = ''别墅'''
if @leixing = '联排'
set @sql=@sql+' and ht.HouseTypeName = ''联体'''
if @leixing = '办公'
set @sql=@sql+' and ht.HouseTypeName = ''办公楼'''
if @leixing = '商铺'
set @sql=@sql+' and ht.HouseTypeName = ''商铺'''
if @leixing = '车库'
set @sql=@sql+' and (h.Shi like ''%车%'' or h.Hao like ''%车%'')'
endexec(@sql)数据量大就很慢，有时就查不出来，求高手邦下。

Answer 1

從如上的語句，應該會報錯啊
定義了臨時表#table1 
而在exec(@sql)里用到它，會報錯的吧??

Answer 2

先创建临时表，可以在动态SQL语句中访问，这个没有问题。
如果这个条件有值，肯定会慢：
if @leixing =  '车库 ' 
set @sql=@sql+ ' and (h.Shi like  ' '%车% ' ' or h.Hao like  ' '%车% ' ') ' 因为like '%车%' 用不到索引。

Answer 3

ALTER PROCEDURE [dbo].[App_SellHousePeople]  
@Name varchar(30)=null, 
@id varchar(50)=null, 
@cishu nvarchar(20)= null, 
@qu nvarchar(20) = null, 
@leixing nvarchar(20) = null, 
@countryname nvarchar(16) = null, 
@cityname nvarchar(32) = null, 
@startdate datetime, 
@enddate datetime 
as 
SET NOCOUNT ON
create table #table1 
( 
 PeopleID int, 
 allcount int 
) 
insert into #table1 
SELECT B.PeopleID, COUNT(A.BuyID) AS allcount 
FROM dbo.Deals AS A WITH(NOLOCK) INNER JOIN 
      dbo.Peoples AS B WITH(NOLOCK)
 ON A.BuyID = B.PeopleID 
      where A.CheckTime  >= @startdate and A.CheckTime  <= @enddate 
GROUP BY B.PeopleID 
declare @sql varchar(8000) 
set @sql= '
select distinct 
 p.Name as 姓名 
,Country.CountryName as country 
,Citys.CityName as house
,p.CardNumber as 身份证
,t1.allcount as 次数 
from DBO.Peoples AS p WITH(NOLOCK) 
inner join 
#table1 t1 
on t1.PeopleID = p.PeopleID 
INNER JOIN 
dbo.Deals AS d WITH(NOLOCK)  
ON d.BuyID = p.PeopleID 
INNER JOIN 
dbo.Country with(nolock) 
ON p.CountryID = Country.CountryID 
INNER JOIN 
dbo.Citys with(nolock)
ON p.CityID = Citys.CityCode  
INNER JOIN 
dbo.Houses AS h with(nolock)  
ON d.HouseID = h.HouseID 
left join dbo.HouseType as ht with(nolock)  
on ht.TypeID = h.HouseTypeID where  ' 
set @sql = @sql +  ' d.CheckTime >= ' ' '+cast(@startdate as nvarchar(10))+ ' ' ' and ' 
set @sql = @sql +  ' d.CheckTime <= ' ' '+cast(@enddate as nvarchar(10))+ ' ' ' ' if @Name  <>  ' ' 
set @sql=@sql+ ' and p.Name like  ' ' '+@Name+ '% ' ' ' 
if @id  < >  ' ' 
set @sql=@sql+ ' and p.CardNumber like  ' '% '+@id+ '% ' ' ' 
if @countryname  < >  ' ' 
set @sql =@sql+ ' and Country.CountryName =  ' ' '+@countryname+ ' ' ' ' 
if @cityname  < >  ' ' 
set @sql =@sql+ ' and Citys.CityName =  ' ' '+@cityname+ ' ' ' ' 
if @cishu  < >  ' ' 
if @cishu =  '首次交易者 ' 
set @sql=@sql+ ' and t1.allcount = 1 ' 
if @cishu =  '2次交易者 ' 
set @sql=@sql+ ' and t1.allcount= 2 ' 
if @cishu =  '3-5次交易者 ' 
set @sql=@sql+ ' and t1.allcount in (3,4,5) ' 
if @cishu =  '5-10次交易者 ' 
set @sql=@sql+ ' and t1.allcount in (6,7,8,9,10) ' 
if @cishu =  '10次以上交易者 ' 
set @sql=@sql+ ' and t1.allcount  > 10 ' 
if @qu  < >  ' ' 
set @sql=@sql+ ' and h.qu= ' ' '+@qu+ ' ' ' ' if @leixing  < >  ' ' 
begin 
if @leixing =  '公寓 ' 
set @sql=@sql+ ' and ht.HouseTypeName in ( ' '新工房 ' ', ' '公寓 ' ') ' 
if @leixing =  '别墅 ' 
set @sql=@sql+ ' and ht.HouseTypeName =  ' '别墅 ' ' ' 
if @leixing =  '联排 ' 
set @sql=@sql+ ' and ht.HouseTypeName =  ' '联体 ' ' ' 
if @leixing =  '办公 ' 
set @sql=@sql+ ' and ht.HouseTypeName =  ' '办公楼 ' ' ' 
if @leixing =  '商铺 ' 
set @sql=@sql+ ' and ht.HouseTypeName =  ' '商铺 ' ' ' 
if @leixing =  '车库 ' 
set @sql=@sql+ ' and (h.Shi like  ' '%车% ' ' or h.Hao like  ' '%车% ' ') ' 
end exec(@sql) --建议用 EXEC SP_EXECUTESQL 可以提高效率