存储过程优化
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,
@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 可以提高效率
