存储过程花了两个小时才计算完，有没有改善的好办法,80分相送!!!!!!!!!

[scarkelvin]

存储过程如下:

CREATE Procedure P_SalesAnalyse_R
@XHDMStart varchar(32),@XHDMEnd varchar(32),@ifJHD bit
as
Set xact_abort on
Begin Tran

Declare @LSD_XHDM Varchar(32)
Declare @LSD_KDRQ datetime
Declare @LSD_QTY int
Declare @SalesDate DateTime

if @ifJHD = 1 --有进货单
Begin
Delete From Temp_SalesAnalyse
Insert Into Temp_SalesAnalyse(XHDM,XHDM_1, Z_SP_KDRQ, Z_WP_KDRQ, Z_LJJH_QTY)
Select XHDM,XHDM_1,KDRQ_D, KDRQ, isNull(QTY,0) from JHDQTY_GROUP

--建临时表，存储首批下单日期，及累计下单数量

Create Table #ZSDDQTY(
XHDM Varchar(32),
KDRQ_SP Datetime,
KDRQ_WP Datetime,
QTY Int
)

Insert Into #ZSDDQTY(XHDM, KDRQ_SP, KDRQ_WP,QTY)
Select XHDM, KDRQ_D, KDRQ, QTY From ZSDDQTY_GROUP

Update Temp_SalesAnalyse Set
D_SP_KDRQ = #ZSDDQTY.KDRQ_SP,
D_WP_KDRQ = #ZSDDQTY.KDRQ_WP,
D_LJ_QTY = isNull (#ZSDDQTY.QTY,0)
From Temp_SalesAnalyse,#ZSDDQTY
Where Temp_SalesAnalyse.XHDM = #ZSDDQTY.XHDM


Create Table #DBDQTY(
XHDM Varchar(32),
QTY int
)

Insert into #DBDQTY(XHDM, QTY) Select XHDM,QTY From DBDQTY_GROUP

Update Temp_SalesAnalyse set
F_QTY = isNull (#DBDQTY.QTY,0)
From #DBDQTY,Temp_SalesAnalyse
Where #DBDQTY.XHDM = Temp_SalesAnalyse.XHDM


Create Table #CKTZQTY(
XHDM Varchar(32),
QTY int
)

Insert into #CKTZQTY(XHDM,QTY) Select XHDM,QTY From CKTZDQTY_GROUP

Update Temp_SalesAnalyse set
F_QTY = isNull (F_QTY,0) + isNull (#CKTZQTY.QTY,0)
From #CKTZQTY, Temp_SalesAnalyse
Where #CKTZQTY.XHDM = Temp_SalesAnalyse.XHDM


Create Table #DXDQTY(
XHDM Varchar(32),
QTY int
)

Insert into #DXDQTY(XHDM,QTY) Select XHDM,QTY From DXDQTY_GROUP

Update Temp_SalesAnalyse set
X_QTY = isNull (#DXDQTY.QTY,0)
From #DXDQTY, Temp_SalesAnalyse
Where #DXDQTY.XHDM = Temp_SalesAnalyse.XHDM


Create Table #KHTZQTY(
XHDM Varchar(32),
QTY int
)

Insert into #KHTZQTY(XHDM,QTY) Select XHDM,QTY From KHTZDQTY_GROUP

Update Temp_SalesAnalyse set
X_QTY = isNull (X_QTY,0) + isNull (#KHTZQTY.QTY,0)
From #KHTZQTY, Temp_SalesAnalyse
Where #KHTZQTY.XHDM = Temp_SalesAnalyse.XHDM


Create Table #LSDQTY(
XHDM Varchar(32),
QTY int
)

Insert into #LSDQTY (XHDM,QTY) Select XHDM,QTY From LSDQTY_GROUP

Update Temp_SalesAnalyse Set
LJXS_QTY = isNull (QTY,0)
From #LSDQTY, Temp_SalesAnalyse
Where #LSDQTY.XHDM = Temp_SalesAnalyse.XHDM


--总公司库存，分公司库存，销售点库存

Update Temp_SalesAnalyse set
Z_KC_QTY = isNull (Z_LJJH_QTY,0) - isNull (F_QTY,0),
F_KC_QTY = isNull (F_QTY,0) - isNull ( X_QTY,0),
X_KC_QTY = isNull (X_QTY,0) - isNull (LJXS_QTY,0)


--首批下单双数，及追加下单双数

Create Table #ZSDDSPQTY(
XHDM Varchar(32),
QTY int
)

Insert into #ZSDDSPQTY(XHDM,QTY)
Select ZSDDQTY.XHDM+'-'+ZSDDQTY.WGDM, sum(ZSDDQTY.QTY) From ZSDDQTY,#ZSDDQTY
Where ZSDDQTY.XHDM+'-'+ZSDDQTY.WGDM = #ZSDDQTY.XHDM
and ZSDDQTY.DDXDRQ = #ZSDDQTY.KDRQ_SP
Group By ZSDDQTY.XHDM, ZSDDQTY.WGDM

Update Temp_SalesAnalyse Set
D_SP_QTY = isNull (#ZSDDSPQTY.QTY,0)
From Temp_salesAnalyse,#ZSDDSPQTY
Where #ZSDDSPQTY.XHDM = Temp_SalesAnalyse.XHDM

Update Temp_SalesAnalyse Set
D_ZJ_QTY = isNull (D_LJ_QTY,0) - isNull (D_SP_QTY,0)


--首批进货双数，及追加进货

Create Table #JHDSPQTY(
XHDM Varchar(32),
QTY int
)

Insert Into #JHDSPQTY(XHDM,QTY)
Select JHDQTY.XHDM+'-'+JHDQTY.WGDM, sum(JHDQTY.QTY)
From JHDQTY,JHDQTY_GROUP
Where JHDQTY.XHDM+'-'+JHDQTY.WGDM = JHDQTY_GROUP.XHDM
and JHDQTY.KDRQ = JHDQTY_GROUP.KDRQ_D
Group By JHDQTY.XHDM,JHDQTY.WGDM

Update Temp_SalesAnalyse set
Z_SP_QTY = isNull (#JHDSPQTY.QTY,0)
From Temp_SalesAnalyse,#JHDSPQTY
Where #JHDSPQTY.XHDM = Temp_SalesAnalyse.XHDM

Update Temp_SalesAnalyse set
Z_ZJ_QTY = isNull (Z_LJJH_QTY,0) - isNull (Z_SP_QTY,0)


--计划库存数量

/*有问题的代码
Update Temp_SalesAnalyse set
JHKC_QTY = isNull (JHKC_QTY,0) +
isNull ((Select sum(SPJHKC) From BMKCSPB
Where BMKCSPB.XHDM+'-'+BMKCSPB.WGDM = Temp_SalesAnalyse.XHDM
Group By BMKCSPB.XHDM,BMKCSPB.WGDM), 0)

Update Temp_SalesAnalyse set
JHKC_QTY = isNull (JHKC_QTY,0) +
isNull ((Select sum(ZTSPJHKC) From BMZTSPB
Where BMZTSPB.XHDM+'-'+BMZTSPB.WGDM = Temp_SalesAnalyse.XHDM
Group by BMZTSPB.XHDM,BMZTSPB.WGDM),0)

Update Temp_SalesAnalyse set
JHKC_QTY = isNull (JHKC_QTY,0) +
isNull ((Select isNull(sum(JHJXSPSL),0) + isNull(sum(JHDXSPSL),0) From KHKCB
Where KHKCB.XHDM+'-'+KHKCB.WGDM = Temp_SalesAnalyse.XHDM
Group By KHKCB.XHDM,KHKCB.WGDM),0)
*/

--统计产品销售情况

Create table #LSDXSQK(
XHDM varchar(32),
W1_QTY int,
W2_QTY int,
W3_QTY int,
W4_QTY int,
W5_QTY int,
W6_QTY int,
W7_QTY int,
W8_QTY int,
last_QTY int)

Declare cur_lsd cursor for Select XHDM,KDRQ,QTY from LSDQTYBYWEEK_GROUP
Open cur_lsd
Fetch next from cur_lsd into @LSD_XHDM,@LSD_KDRQ,@LSD_QTY
While @@Fetch_Status = 0
Begin
Select @SalesDate = KDRQ_D From LSDQTY_GROUP
Where XHDM = @LSD_XHDM

if (DateDiff(Day,@LSD_KDRQ,GetDate())>=1)
and (DateDiff(Day,@LSD_KDRQ,GetDate())<=8)
Begin
Insert into #LSDXSQK(XHDM,last_QTY)
Values (@LSD_XHDM,@LSD_QTY)
End

if (DateDiff(Day,@SalesDate,@LSD_KDRQ)>=0)
and (DateDiff(Day,@SalesDate,@LSD_KDRQ)<=6)
Begin
Insert into #LSDXSQK(XHDM,W1_QTY)
Values (@LSD_XHDM,@LSD_QTY)
End Else if (DateDiff(Day,@SalesDate,@LSD_KDRQ)>6)
and (DateDiff(Day,@SalesDate,@LSD_KDRQ)<=13)
Begin
Insert into #LSDXSQK(XHDM,W2_QTY)
Values (@LSD_XHDM,@LSD_QTY)
End Else if (DateDiff(Day,@SalesDate,@LSD_KDRQ)>13)
and (DateDiff(Day,@SalesDate,@LSD_KDRQ)<=20)
Begin
Insert into #LSDXSQK(XHDM,W3_QTY)
Values (@LSD_XHDM,@LSD_QTY)
End Else if (DateDiff(Day,@SalesDate,@LSD_KDRQ)>20)
and (DateDiff(Day,@SalesDate,@LSD_KDRQ)<=27)
Begin
Insert into #LSDXSQK(XHDM,W4_QTY)
Values (@LSD_XHDM,@LSD_QTY)
End Else if (DateDiff(Day,@SalesDate,@LSD_KDRQ)>27)
and (DateDiff(Day,@SalesDate,@LSD_KDRQ)<=34)
Begin
Insert into #LSDXSQK(XHDM,W5_QTY)
Values (@LSD_XHDM,@LSD_QTY)
End Else if (DateDiff(Day,@SalesDate,@LSD_KDRQ)>34)
and (DateDiff(Day,@SalesDate,@LSD_KDRQ)<=41)
Begin
Insert into #LSDXSQK(XHDM,W6_QTY)
Values (@LSD_XHDM,@LSD_QTY)
End Else if (DateDiff(Day,@SalesDate,@LSD_KDRQ)>41)
and (DateDiff(Day,@SalesDate,@LSD_KDRQ)<=48)
Begin
Insert into #LSDXSQK(XHDM,W7_QTY)
Values (@LSD_XHDM,@LSD_QTY)
End Else if (DateDiff(Day,@SalesDate,@LSD_KDRQ)>48)
and (DateDiff(Day,@SalesDate,@LSD_KDRQ)<=55)
Begin
Insert into #LSDXSQK(XHDM,W8_QTY)
Values (@LSD_XHDM,@LSD_QTY)
End
Fetch next from cur_lsd into @LSD_XHDM,@LSD_KDRQ,@LSD_QTY
End
Close Cur_LSD
Deallocate Cur_LSD


Create table #XSQK_GROUP(
XHDM varchar(32),
W1_QTY int,
W2_QTY int,
W3_QTY int,
W4_QTY int,
W5_QTY int,
W6_QTY int,
W7_QTY int,
W8_QTY int,
last_QTY int
)

Insert into #XSQK_GROUP Select XHDM,
Sum(W1_QTY),
Sum(W2_QTY),
Sum(W3_QTY),
Sum(W4_QTY),
Sum(W5_QTY),
Sum(W6_QTY),
Sum(W7_QTY),
Sum(W8_QTY),
Sum(last_QTY)
From #LSDXSQK
Group By XHDM

Update Temp_SalesAnalyse Set
W8_S_QTY = #XSQK_GROUP.W8_QTY,
W7_S_QTY = #XSQK_GROUP.W7_QTY,
W6_S_QTY = #XSQK_GROUP.W6_QTY,
W5_S_QTY = #XSQK_GROUP.W5_QTY,
W4_S_QTY = #XSQK_GROUP.W4_QTY,
W3_S_QTY = #XSQK_GROUP.W3_QTY,
W2_S_QTY = #XSQK_GROUP.W2_QTY,
W1_S_QTY = #XSQK_GROUP.W1_QTY,
LAST7_S_QTY = #XSQK_GROUP.last_QTY
From Temp_SalesAnalyse,#XSQK_GROUP
Where Temp_SalesAnalyse.XHDM = #XSQK_GROUP.XHDM


End





if @@Error = 0 Commit Tran Else Rollback Tran
GO

[scarkelvin]

提示：

经调式，大部分时间花在了程序中的游标部分，肯请各位给一个好的解决办法

[scarkelvin]

晕~~~~~~~~~~~~~~~~~~~~~~高手哪去了啊？...................

[blueshu]

晕
你比我牛
看花眼了

[飞天林]

游标会占用很多资源的，可以将游标改为用临时表来操作
create table (id int identity(1,1),.....)
将信息插入这个临时表中，
然后取得最小及最大的id(@minid,@maxid)
while (@minid<=@maxid )
begin
select .... from table where id = minid
do something
select @minid=@minid+1
end

[FAICHEN]

我看你的那段也不太清楚
说说需求吧！

[scarkelvin]

请高手们帮帮忙啊.!!!!!!!!!!!!!!!!

[pdbird]

too long !

[koma2003]

太长了，看不下去了

[kelvinlv]

游标改成临时表，且Update的写法有点问题，应先建一个集合