过程优化问题

wzsjzjjxy 2010-12-10 12:42:43

如何优化，这是开发的损益表，按月出，然后利用水晶报表设计出来。但是现在运行要近一分钟，优化没有一点思路我。用sqlserver的优化引擎没提升建议。

ALTER proc SP_SYB1 --本年第一天
@DATETO DATETIME
AS
BEGIN
declare @i int
declare @datefirst datetime
declare @datelast datetime

declare @date datetime
set @date=dateadd(month,-month(dateadd(day,-day(@dateto)+1,@dateto))+1,dateadd(day,-day(@dateto)+1,@dateto)) --First day of this Year
set @datefirst=@date
set @date=dateadd(day,-1,dateadd(month,1,@date)) --last day of first month this year
set @datelast=@date

DECLARE @TEMP TABLE(FatherNum2 varchar(100),FatherName2 varchar(150),CatId3 int,Name3 varchar(150),month int,序号 varchar(20),YearBalance numeric(20,4),百分比 float)
DECLARE @TEMP_year TABLE(FatherNum2 varchar(100),FatherName2 varchar(150),CatId3 int,Name3 varchar(150),month int,序号 varchar(20),YearBalance numeric(20,4))

set @i=1
while @i<=month(@dateto)
BEGIN

insert into @temp_year
select distinct t66.catid as FatherNum2,t66.name as FatherName2,t77.catid as CatId3,t77.name as Name3
,(SELECT @i) AS MONTH
,序号=(case t66.name
when '一、主营业务收入' then 1
when '减：主营业务成本' then 2
when '营业费用' then 3
when '主营业务税金及附加' then 4
when '二、主营业务利润' then 5
when '加：其他业务利润' then 6
when '减：管理费用' then 7
when '财务费用' then 8
when '资产减值损失' then 9
when '三、营业利润' then 10
when '加：投资收益' then 11
when '补贴收入' then 12
when '营业外收入' then 13
when '减：营业外支出' then 14
when '加：以前年度损益调整' then 15
when '四、利润总额' then 16
when '减：所得税' then 17
when '五、净利润' then 18
end)
,(case
when t66.catid IN('6','148','152','149','153','154','159') then -sum(isnull(T33.CREDIT,0)-isnull(T33.DEBIT,0))over(partition by T66.CATID,T66.NAME)
else sum(isnull(T33.CREDIT,0)-isnull(T33.DEBIT,0)) over(partition by T66.CATID,T66.NAME)
end) as YearBalance
from (SELECT t0.catid,t0.name,t0.templateid,t0.levels,t0.fathernum,t1.absid,t1.name as 模板名称,t1.doctype
FROM OFRC T0 INNER JOIN OFRT T1 ON T0.TemplateId = T1.AbsId where absid=3) t11
left join (select * from frc1 where templateid=3) t22 on t11.catid=t22.catid
LEFT join (select * from jdt1 where company=1
and account in (select acctcode from frc1
where templateid=3)
and refdate<=@DATELAST and refdate >=@DATEFIRST) t33 on t22.acctcode=t33.account
full join (select * from ofrc where templateid=3) t55 on t11.fathernum = t55.catid
full join (select * from ofrc where templateid=3) t66 on t55.fathernum = t66.catid
left join (select * from ofrc where templateid=3) t77 on t66.fathernum=t77.catid
WHERE t77.name='损益类'
order by 序号

----------小计项-------------------------------------------------------------

declare @ID int
declare @Name nvarchar(30)
declare @param_1 int,@param_2 int, @param_3 int, @param_4 int, @param_5 int, @param_6 int, @param_7 int, @param_8 int
, @param_9 int, @param_10 int, @param_11 int, @param_12 int, @param_13 int, @param_14 int, @param_15 int, @param_16 int
, @param_17 int, @param_18 int, @param_19 int, @param_20 int
,@op_1 nvarchar(2),@OP_2 nvarchar(2), @OP_3 nvarchar(2), @OP_4 nvarchar(2), @OP_5 nvarchar(2), @OP_6 nvarchar(2), @OP_7 nvarchar(2), @OP_8 nvarchar(2)
, @OP_9 nvarchar(2), @op_10 nvarchar(2), @op_11 nvarchar(2), @op_12 nvarchar(2), @op_13 nvarchar(2), @op_14 nvarchar(2), @op_15 nvarchar(2), @op_16 nvarchar(2)
, @op_17 nvarchar(2), @op_18 nvarchar(2), @op_19 nvarchar(2)

declare @Total_2 Numeric(19,2)
declare @Value_2 Numeric(19,2)

declare cur cursor for --定义游标,获取公式及合计项--使用中间表@temp_year
select catid,name
,param_1,param_2,param_3,param_4,param_5,param_6,param_7,param_8,param_9,param_10
,param_11,param_12,param_13,param_14,param_15,param_16,param_17,param_18,param_19,param_20
,OP_1,OP_2,OP_3,OP_4,OP_5,OP_6,OP_7,OP_8,OP_9,OP_10
,OP_11,OP_12,OP_13,OP_14,OP_15,OP_16,OP_17,OP_18,OP_19
from ofrc where subsum='Y' and templateid=3 and param_1>0
order by catid

open cur
fetch next from cur into @ID,@Name,@param_1,@param_2, @param_3, @param_4, @param_5,@param_6,@param_7,@param_8
,@param_9, @param_10, @param_11, @param_12, @param_13, @param_14, @param_15, @param_16
,@param_17, @param_18, @param_19, @param_20,@op_1,@OP_2,@OP_3,@OP_4,@OP_5,@OP_6,@OP_7,@OP_8
,@OP_9,@op_10,@op_11,@op_12,@op_13,@op_14,@op_15,@op_16,@op_17,@op_18,@op_19

while @@fetch_status=0
begin
DECLARE @CATID INT
SET @CATID=@ID

set @Total_2=0 --期末
set @Value_2=0

select @Value_2=isnull((select YearBalance from @temp_year where fathernum2=@param_1 ),0)
set @Total_2=@Value_2

if @OP_1 is not null
begin
set @Value_2=(select YearBalance from @temp_year where fathernum2=@param_2)
set @Total_2=@Total_2-isnull(@Value_2,0)
end

if @OP_2<>''
begin
set @Value_2=(select YearBalance from @temp_year where fathernum2=@param_3)
set @Total_2=@Total_2-isnull(@Value_2,0)
end

if @OP_3<>''
if @param_4=158 --改变符号
begin
set @Value_2=(select YearBalance from @temp_year where fathernum2=@param_4)
set @Total_2=@Total_2+isnull(@Value_2,0)
end
else
begin
set @Value_2=(select YearBalance from @temp_year where fathernum2=@param_4)
set @Total_2=@Total_2-isnull(@Value_2,0)
END

if @OP_4<>''
begin
set @Value_2=(select YearBalance from @temp_year where fathernum2=@param_5)
set @Total_2=@Total_2-isnull(@Value_2,0)
end

if @OP_5<>''
begin
set @Value_2=(select YearBalance from @temp_year where fathernum2=@param_6)
set @Total_2=@Total_2-isnull(@Value_2,0)
end

if @OP_6<>''
begin
set @Value_2=(select YearBalance from @temp_year where fathernum2=@param_7)
set @Total_2=@Total_2-isnull(@Value_2,0)
end

if @OP_7<>''
begin
set @Value_2=(select YearBalance from @temp_year where fathernum2=@param_8)
set @Total_2=@Total_2-isnull(@Value_2,0)
end

if @OP_8<>''
begin
set @Value_2=(select YearBalance from @temp_year where fathernum2=@param_9)
set @Total_2=@Total_2-isnull(@Value_2,0)
end

if @OP_9<>''
begin
set @Value_2=(select YearBalance from @temp_year where fathernum2=@param_10)
set @Total_2=@Total_2-isnull(@Value_2,0)
end

if @OP_10<>''
begin
set @Value_2=(select YearBalance from @temp_year where fathernum2=@param_11)
set @Total_2=@Total_2-isnull(@Value_2,0)
end

if @OP_11<>''
begin
set @Value_2=(select YearBalance from @temp_year where fathernum2=@param_12)
set @Total_2=@Total_2-isnull(@Value_2,0)
end

if @OP_12<>''
begin
set @Value_2=(select YearBalance from @temp_year where fathernum2=@param_13)
set @Total_2=@Total_2-isnull(@Value_2,0)
end

if @OP_13<>''
begin
set @Value_2=(select YearBalance from @temp_year where fathernum2=@param_14)
set @Total_2=@Total_2-isnull(@Value_2,0)
end

if @OP_14<>''
begin
set @Value_2=(select YearBalance from @temp_year where fathernum2=@param_15)
set @Total_2=@Total_2-isnull(@Value_2,0)
end

if @OP_15<>''
begin
set @Value_2=(select YearBalance from @temp_year where fathernum2=@param_16)
set @Total_2=@Total_2-isnull(@Value_2,0)
end

if @OP_16<>''
begin
set @Value_2=(select YearBalance from @temp_year where fathernum2=@param_17)
set @Total_2=@Total_2-isnull(@Value_2,0)
end

if @OP_17<>''
begin
set @Value_2=(select YearBalance from @temp_year where fathernum2=@param_18)
set @Total_2=@Total_2-isnull(@Value_2,0)
end

if @OP_18<>''
begin
set @Value_2=(select YearBalance from @temp_year where fathernum2=@param_19)
set @Total_2=@Total_2-isnull(@Value_2,0)
end

if @OP_19<>''
begin
set @Value_2=(select YearBalance from @temp_year where fathernum2=@param_20)
set @Total_2=@Total_2-isnull(@Value_2,0)
end

update t0 set t0.YearBalance =@Total_2 from @temp_year T0 where fathernum2=@ID

fetch next from cur into @ID,@Name,@param_1,@param_2, @param_3, @param_4, @param_5,@param_6,@param_7,@param_8
,@param_9, @param_10, @param_11, @param_12, @param_13, @param_14, @param_15, @param_16
,@param_17, @param_18, @param_19, @param_20,@op_1,@OP_2,@OP_3,@OP_4,@OP_5,@OP_6,@OP_7,@OP_8
,@OP_9,@op_10,@op_11,@op_12,@op_13,@op_14,@op_15,@op_16,@op_17,@op_18,@op_19
end
close cur
deallocate cur

insert into @temp
select T1.FatherNum2,t1.fathername2,t1.catid3,t1.name3,t1.month,t1.序号,t1.YearBalance
,convert(numeric(15,4),(select t5.Yearbalance/(select t4.YEARbalance from @temp_year t4 where t4.fathernum2=1 and t5.month=t4.month)
from @temp_year t5
where t1.序号=t5.序号 and t5.month=t1.month))*100 as 百分比2
from @temp_year t1

delete from @temp_year --清除历史表

set @i=@i+1

set @datefirst=dateadd(month,1,@datefirst) --将时间设为First day of next month
set @datelast=dateadd(day,-1,dateadd(month,1,@datefirst)) --last day of next month

end
select * from @temp
end

...全文