关于存储过程优化



Declare @S1 Nvarchar(4000)
Select @S1 = N' Select TB1_LS.BH'
Select @S1 = @S1 + N', SUM(Case BuMen When ''' + BuMen + N''' Then JinE_DianCai Else 0 End) As [' +BuMen+']' 
From TB1_LS Group By BuMen
Select @S1 = @S1 + N' From TB1_LS Group By TB1_LS.BH'

Declare @S2 Nvarchar(4000)
Select @S2 = N' Select TB2.BH,TB2.JinE_FuWuFei,TB2.JinE_Bu,TB2.JinE_Zhe,TB3.QuYu,TB2.BanCi,TB2.JinE_Del'
From TB2
Select @S2 = @S2 + N' From TB2,TB3 where TB2.BH_CT=TB3.BH_CT and TB2.RiQi>='''+convert(varchar(10),@RiQi_B,120) +''' and RiQi<='''+convert(varchar(10),@RiQi_E,120) +''' Group by TB2.JiNE_Zhe,TB3.QuYu,TB2.BanCi,TB2.JinE_Del,TB2.BH,TB2.JinE_FuWuFei,TB2.JinE_Bu'


Declare @S3 Nvarchar(4000)
Select @S3 = N' Select TB2.NAME,TB2.BH_Card,TB2.BH,TB2.JinE_Zhe'
From TB2
Select @S3 = @S3 + N' From TB2 Group by TB2.NAME,TB2.BH_Card,TB2.BH,TB2.JiNE_Zhe'

declare @sql nvarchar(4000)
set @sql = N'select * from ((' + @S1 + N') as a inner join (' + @S2 + N') as b on a.BH = b.BH) inner join (' + @S3 + N')as c on b.BH=c.BH order by B.BH'
EXEC(@sql)
GO

这个是我的存储过程,本来就慢,现在随着数据量增多就越来越慢,可以怎么优化一下?

------解决方案--------------------
关注up
------解决方案--------------------
看着眼花...
你这样写 数据多了 不仅仅是慢的问题
甚至会出现 
@sql 变量8000 字符装不下的问题...


你把
EXEC(@sql)
改成
print(@sql)

再发来看看嘛... - .-
 

------解决方案--------------------
Declare @S1 Nvarchar(4000) 
Select @S1 = N ' Select TB1_LS.BH ' --建议楼主把这些赋值的语句直接用Set @S1 = N ' Select TB1_LS.BH ' ,下面还有很多，select绝对比set要慢吧```
Select @S1 = @S1 + N ', SUM(Case BuMen When ' ' ' + BuMen + N ' ' ' Then JinE_DianCai Else 0 End) As [ ' +BuMen+ '] '
From TB1_LS Group By BuMen 
Select @S1 = @S1 + N ' From TB1_LS Group By TB1_LS.BH ' 

Declare @S2 Nvarchar(4000) 
Select @S2 = N ' Select TB2.BH,TB2.JinE_FuWuFei,TB2.JinE_Bu,TB2.JinE_Zhe,TB3.QuYu,TB2.BanCi,TB2.JinE_Del ' 
From TB2 
Select @S2 = @S2 + N ' From TB2,TB3 where TB2.BH_CT=TB3.BH_CT and TB2.RiQi >= ' ' '+convert(varchar(10),@RiQi_B,120) + ' ' ' and RiQi <= ' ' '+convert(varchar(10),@RiQi_E,120) + ' ' ' Group by TB2.JiNE_Zhe,TB3.QuYu,TB2.BanCi,TB2.JinE_Del,TB2.BH,TB2.JinE_FuWuFei,TB2.JinE_Bu ' 


Declare @S3 Nvarchar(4000) 
Select @S3 = N ' Select TB2.NAME,TB2.BH_Card,TB2.BH,TB2.JinE_Zhe ' 
From TB2 
Select @S3 = @S3 + N ' From TB2 Group by TB2.NAME,TB2.BH_Card,TB2.BH,TB2.JiNE_Zhe ' 

declare @sql nvarchar(4000) 
set @sql = N 'select * from (( ' + @S1 + N ') as a inner join ( ' + @S2 + N ') as b on a.BH = b.BH) inner join ( ' + @S3 + N ')as c on b.BH=c.BH order by B.BH ' 
EXEC(@sql) 
GO 

------解决方案--------------------
转换别是方式吧，太多了，当然会慢了。