存储过程优化
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

create 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 ''%车%'')'
end

exec(@sql)

数据量大就很慢，有时就查不出来，求高手邦下。

------解决方案--------------------
從如上的語句，應該會報錯啊
定義了臨時表#table1 
而在exec(@sql)里用到它，會報錯的吧??
------解决方案--------------------
1.优化语句
2.在相关表增加合适索引

------解决方案--------------------
用执行计划
依据执行计划建相关索引

------解决方案--------------------
先创建临时表，可以在动态SQL语句中访问，这个没有问题。


如果这个条件有值，肯定会慢：
if @leixing = '车库 ' 
set @sql=@sql+ ' and (h.Shi like ' '%车% ' ' or h.Hao like ' '%车% ' ') ' 

因为like '%车%' 用不到索引。


------解决方案--------------------
同意6楼的看法,'%'在放在开始位置是不能使用索引的
------解决方案--------------------
ALTER PROCEDURE [dbo].[App_SellHousePeople]
@Name varchar(30)=null, 
@id varchar(50)=null, 
@cishu nvarchar(20)= null,