优化存储过程

[wishY]

ALTER PROCEDURE App_Floor_Analyse
	@year nvarchar(8) = null,
	@term nvarchar(64) = null,
	@cishu nvarchar(4) = null
AS
	create table #table_avg
	(
		[month] nvarchar(8),
		[count] float,
		avg_price int,
		avg_totalprice int,
		avg_area int
	)
	declare @sql nvarchar(1000)
	set @sql = 'select Convert(nvarchar(2),d.CheckTime,101) as [month],count(*) as [count],avg(d.Price) as avg_price,
	avg(d.TotalPrice) as avg_totalprice,avg(h.MianJi) as avg_area from Deals as d inner join Houses as h on d.HouseID = h.HouseID'
	set @sql = @sql + ' where Convert(nvarchar(4),d.CheckTime,102) = '''+@year+''' '
	set @sql = @sql + ' and h.FirstHandID = '''+@cishu+''' '
	if  @cishu = '1'
	begin
		if  @term <> ''
		set @sql = @sql + ' and h.XiangMu like ''%'+@term+'%'' '
	end
	else
	begin
		if @term <> ''
		set @sql = @sql + ' and (h.XiangMu like ''%'+@term+'%'' or h.Address like ''%'+@term+'%''  )'
	end
	set @sql = @sql + ' group by Convert(nvarchar(2),d.CheckTime,101)'
	
	insert into #table_avg exec sp_executesql @sql
	
	
	create table #table_cishu
	(
		[month] nvarchar(8),
		one int,
		two int,
		three int,
		five int
	)
	declare @strsql nvarchar(1000)
	set @strsql = 'SELECT [month],sum([count]) FROM (SELECT Convert(nvarchar(2),d.CheckTime,101) as [month] ,COUNT(D.BuyID) as [count] 
	FROM Deals as D INNER JOIN Houses as H ON D.HouseID = H.HouseID '
	set @strsql = @strsql + ' where Convert(nvarchar(4),d.CheckTime,102) = '''+@year+''' '
	set @strsql = @strsql + ' and h.FirstHandID = '''+@cishu+''' '
	if  @cishu = '1'
	begin
		if  @term <> ''
		set @strsql = @strsql + ' and h.XiangMu like ''%'+@term+'%'' '
	end
	else
	begin
		if @term <> ''
		set @strsql = @strsql + ' and (h.XiangMu like ''%'+@term+'%'' or h.Address like ''%'+@term+'%''  )'
	end
	
	
	declare @sql1 nvarchar(1000)
	set @sql1 = @strsql + '  group by D.BuyID,Convert(nvarchar(2),d.CheckTime,101) having count(D.BuyID) = 1) AS 临时表 group by [month]'
    
    
    declare @sql2 nvarchar(1000)
	set @sql2 = @strsql + '  group by D.BuyID,Convert(nvarchar(2),d.CheckTime,101) having count(D.BuyID) = 2) AS 临时表 group by [month]'
    

	declare @sql3 nvarchar(1000)
	set @sql3 = @strsql + '  group by D.BuyID,Convert(nvarchar(2),d.CheckTime,101) having (count(D.BuyID) = 3 or count(D.BuyID) = 4 or count(D.BuyID) = 5)) AS 临时表 group by [month]'

    
    declare @sql4 nvarchar(1000)
	set @sql4 = @strsql + '  group by D.BuyID,Convert(nvarchar(2),d.CheckTime,101) having count(D.BuyID) > 5) AS 临时表 group by [month]'
    
   create table #table1
    (
      [month] nvarchar(8),
      [count] int
    )
    insert into #table1 exec sp_executesql @sql1 --1次
    create table #table2
    (
      [month] nvarchar(8),
      [count] int
    )
    insert into #table2 exec sp_executesql @sql2 --2次
     create table #table3
    (
      [month] nvarchar(8),
      [count] int
    )
    insert into #table3 exec sp_executesql @sql3 --3-5次
    create table #table4
    (
      [month] nvarchar(8),
      [count] int
    )
    insert into #table4 exec sp_executesql @sql4 --5次
    
    insert into #table_cishu  select t1.[month] ,isnull(t1.[count],0) as one,isnull(t2.[count],0) as two,isnull(t3.[count],0) as three,isnull(t4.[count],0) as five  
    from #table1  t1 left join #table2  t2 on t1.[month] = t2.[month]
    left join #table3  t3 on t1.[month] = t3.[month] left join #table4 t4 on t1.[month] = t4.[month]

    create table #table_people
    (
		[month] nvarchar(8),
		sh int,
		wd int,
		hw int,
		gw int
    )
    declare @str_sql nvarchar(1000)
    set @str_sql = 'select [month] , sum([count]) from ( select Convert(nvarchar(2),d.CheckTime,101) as [month], count(*) as [count] 
	 from Deals as d inner join Houses as h on d.HouseID = h.HouseID inner join Peoples as p on p.PeopleID in (d.BuyID,d.SellID) inner join Country as c on p.CountryID = c.CountryID '
    set @str_sql = @str_sql + ' where Convert(nvarchar(4),d.CheckTime,102) = '''+@year+''' '
	set @str_sql = @str_sql + ' and h.FirstHandID = '''+@cishu+''' '
	if  @cishu = '1'
	begin
		if  @term <> ''
		set @str_sql = @str_sql + ' and h.XiangMu like ''%'+@term+'%'' '
	end
	else
	begin
		if @term <> ''
		set @str_sql = @str_sql + ' and (h.XiangMu like ''%'+@term+'%'' or h.Address like ''%'+@term+'%''  )'
	end
	--上海
	declare @sql_1 nvarchar(1000)
	set @sql_1 = @str_sql + ' group by CityID,Convert(nvarchar(2),d.CheckTime,101) having CityID = 2) as 临时表 group by [month]'
	--外地
	declare @sql_2 nvarchar(1000)
	set @sql_2 = @str_sql + ' and p.CountryID =2 group by CityID,Convert(nvarchar(2),d.CheckTime,101) having CityID <> 2) as 临时表 group by [month]'
	--国外
	declare @sql_3 nvarchar(1000)
	set @sql_3 = @str_sql + ' group by p.CountryID,Convert(nvarchar(2),d.CheckTime,101) having p.CountryID = 5) as 临时表 group by [month]'
	--海外华人
	declare @sql_4 nvarchar(1000)
	set @sql_4 = @str_sql + ' group by CityID,Convert(nvarchar(2),d.CheckTime,101) having CityID = 6) as 临时表 group by [month]'
    
    create table #table_1
    (
      [month] nvarchar(8),
      [count] int
    )
    insert into #table_1 exec sp_executesql @sql_1
    create table #table_2
    (
      [month] nvarchar(8),
      [count] int
    )
    insert into #table_2 exec sp_executesql @sql_2
     create table #table_3
    (
      [month] nvarchar(8),
      [count] int
    )
    insert into #table_3 exec sp_executesql @sql_3
    create table #table_4
    (
      [month] nvarchar(8),
      [count] int
    )
    insert into #table_4 exec sp_executesql @sql_4
	
     insert into #table_people  select t_1.[month] ,isnull(t_1.[count],0) as one,isnull(t_2.[count],0) as two,isnull(t_3.[count],0) as three,isnull(t_4.[count],0) as five  
    from #table_1  t_1 left join #table_2  t_2 on t_1.[month] = t_2.[month]
    left join #table_3  t_3 on t_1.[month] = t_3.[month] left join #table_4 t_4 on t_1.[month] = t_4.[month]
    
    
    select t_a.[month] ,t_a.[count] ,cast(t_a.avg_price as nvarchar(8)) + '元' as avg_price ,cast((t_a.avg_totalprice/10000) as nvarchar(8)) + '万',
    cast(t_a.avg_area as nvarchar(8)) + '㎡', cast((convert(numeric(8,0),round(t_c.one/t_a.[count] ,2)*100)) as nvarchar(8)) + '%' as one,
    cast((convert(numeric(8,0),round(t_c.two/t_a.[count] ,2)*100)) as nvarchar(8)) + '%' as two,cast((convert(numeric(8,0),round(t_c.three/t_a.[count] ,2)*100)) as nvarchar(8)) + '%' as three,
    cast((convert(numeric(8,0),round(t_c.five/t_a.[count] ,2)*100)) as nvarchar(8)) + '%' as five,cast((convert(numeric(8,0),round(t_p.sh/t_a.[count] ,2)*100)) as nvarchar(8)) + '%' as sh,
    cast((convert(numeric(8,0),round(t_p.wd/t_a.[count] ,2)*100)) as nvarchar(8)) + '%' as wd,cast((convert(numeric(8,0),round(t_p.hw/t_a.[count] ,2)*100)) as nvarchar(8)) + '%' as hw,
    cast((convert(numeric(8,0),round(t_p.gw/t_a.[count] ,2)*100)) as nvarchar(8)) + '%' as gw
    from #table_avg as t_a left join #table_cishu as t_c on t_a.[month] = t_c.[month] left join #table_people as t_p on t_a.[month] = t_p.[month]
    

帮忙优化下，谢谢. 如果改成几个存储过程输出速度会提高吗？

[wishY]

结果:

month    count                     avg_price  Column1    Column2    one       two       three     five      sh        wd        hw        gw        
-------- ------------------------- ---------- ---------- ---------- --------- --------- --------- --------- --------- --------- --------- --------- 
01       18                        9185元      163万       561㎡       56%       44%       0%        0%        72%       33%       28%       0%        
02       5                         11421元     229万       201㎡       100%      0%        0%        0%        60%       20%       0%        0%        
03       3                         12247元     246万       201㎡       100%      0%        0%        0%        100%      0%        0%        0%        
(19 行受影响)
(返回 3 行)
@RETURN_VALUE = 0
完成 [dbo].[App_Floor_Analyse] 运行。

[TigerEatAngil]

根本不用动态的就可以实现的
用分支语句，静态的select可以生成更好的执行计划，也就是效率会提高

[wishY]

楼上什么意思，没完全理解。
我需要后面出来的结果，我想不出还能怎样做呢？