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 @[email protected]+' and p.Name like [email protected]+'%'''
if @id <> ''
set @[email protected]+' and p.CardNumber like [email protected]+'%'''
if @countryname <> ''
set @sql [email protected]+' and Country.CountryName = [email protected]+''''
if @cityname <> ''
set @sql [email protected]+' and Citys.CityName = [email protected]+''''
if @cishu <> ''
if @cishu = '首次交易者'
set @[email protected]+' and t1.allcount = 1'
if @cishu = '2次交易者'
set @[email protected]+' and t1.allcount= 2'
if @cishu = '3-5次交易者'
set @[email protected]+' and t1.allcount in (3,4,5)'
if @cishu = '5-10次交易者'
set @[email protected]+' and t1.allcount in (6,7,8,9,10)'
if @cishu = '10次以上交易者'
set @[email protected]+' and t1.allcount > 10'
if @qu <> ''
set @[email protected]+' and [email protected]+''''
if @leixing <> ''
begin
if @leixing = '公寓'
set @[email protected]+' and ht.HouseTypeName in (''新工房'',''公寓'')'
if @leixing = '别墅'
set @[email protected]+' and ht.HouseTypeName = ''别墅'''
if @leixing = '联排'
set @[email protected]+' and ht.HouseTypeName = ''联体'''
if @leixing = '办公'
set @[email protected]+' and ht.HouseTypeName = ''办公楼'''
if @leixing = '商铺'
set @[email protected]+' and ht.HouseTypeName = ''商铺'''
if @leixing = '车库'
set @[email protected]+' and (h.Shi like ''%车%'' or h.Hao like ''%车%'')'
end
exec(@sql)
数据量大就很慢，有时就查不出来，求高手邦下。
------解决方案--------------------
從如上的語句，應該會報錯啊
定義了臨時表#table1
而在exec(@sql)里用到它，會報錯的吧??
------解决方案--------------------
1.优化语句
2.在相关表增加合适索引
------解决方案--------------------
用执行计划
依据执行计划建相关索引
------解决方案--------------------
先创建临时表，可以在动态SQL语句中访问，这个没有问题。
如果这个条件有值，肯定会慢：
if @leixing = '车库 '
set @[email protected]+ ' 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,