sql2005存储过程执行的时间问题
我做了一个存储过程，作用是根据将已经生产的货物，按照制单上面走货先后顺序，详细到每一个货期、颜色、尺码来推数。
比如，第一、二货期都有红色中码，一个是2000，一个是3000，已经生产了红色中码2500，那么就会先满足第一货期的红色中码，然后剩下500就给第二货期。如果生产了5500，那么多出的数量全部放在最后一个货期。
因为代码太长了，所以不放出来，我遇上的问题就是有时候执行的时间是13分钟，有时候是5分钟，有时候是9分钟......如果是上班时间执行，时间大都很少，只有深夜执行才会有13分钟，但是有时候也会不到这么多时间。
我想请问，抛开系统、硬件、病毒这些原因，到底还有什么会导致执行时间不足的？因为上班的时候，数据时即是产生，这个时候执行的时间特别少，结果自然很多数据都推不上了。
[解决办法]
--说明：TempTable这个表，自己先创建一下
--Insert into #ee (PO,ShipDate,Color,Sizx,Qty,ProcessID,CuttingQty,Qtyx) 这好几个语句可以合成一个，想办法
--不要用游标,试着去SQL实现
--总之，这个存储过程写的非常不好。
ALTER Procedure [dbo].[ShipDateQtyPush_CuttingQty] @PO varchar(12), @ShipDate varchar(10)
as
declare @sql varchar(8000)
if exists (select * from tempdb.dbo.sysobjects where name='#bb' ) drop table #bb
if exists (select * from tempdb.dbo.sysobjects where name='##cc' ) drop table ##cc
if exists (select * from tempdb.dbo.sysobjects where name='##dd' ) drop table ##dd
if exists (select * from tempdb.dbo.sysobjects where name='#gg' ) drop table #gg
--
Create Table #ProcessQty
(
id [int] identity (1,1) not null, --10
PO [varchar] (20),
Color [varchar] (50),
Sizx [varchar] (20),
Qty int,
ProcessID [varchar] (3)
)
DECLARE @begin DATETIME
SET @begin = GETDATE()
Insert into #ProcessQty (PO,ProcessID,Color,Sizx,Qty)
select
Left(jo_master.cust_so_ref,CharIndex('-',jo_master.cust_so_ref)-1) PONO,[fg_rfid]..rfid_transaction_table.major,
[fg_rfid]..jo_finished_goods.colour Color,[fg_rfid]..jo_finished_goods.size,Sum([fg_rfid]..rfid_transaction_table.Qty) Qty
from
[fg_rfid]..rfid_transaction_table with (nolock) left outer join [fg_rfid]..sp_ord_master AS jo_master with (nolock)
ON jo_master.key_seq = rfid_transaction_table.jo_key_seq AND jo_master.trans_type = 'JR' left outer join
[fg_rfid]..jo_finished_goods with (nolock) ON [fg_rfid]..jo_finished_goods.key_seq = rfid_transaction_table.jo_sku_key_seq
where
DateDiff(day,work_date,GetDate()) in (0) and
(ISNULL(RTRIM(rfid_transaction_table.major), '') in ('226','326','328','330','331','345','371','382','396','397','425','900')) and
jo_master.cust_so_ref like '%'+@PO+'%'
Group by
jo_master.cust_so_ref,[fg_rfid]..rfid_transaction_table.major,[fg_rfid]..jo_finished_goods.colour,[fg_rfid]..jo_finished_goods.size
INSERT INTO TempTable SELECT 'Insert into #ProcessQty', DATEDIFF(SECOND, @begin, GETDATE())--第1个地方
SET @begin = GETDATE()
--
Create Table #ee
(
id [int] identity (1,1) not null,
PO varchar(10),
Color varchar(30),
Sizx varchar(10),
ShipDate varchar(10),
Qty int,
CuttingQty int,
Qtyx int,
ProcessID varchar(3)
)
Insert into --100
#ee (PO,ShipDate,Color,Sizx,Qty,ProcessID,CuttingQty,Qtyx)
Select
PO,Convert(varchar(10),ShipDate,120) ShipDate,Color,Sizx,Sum(Qty) Qty,Cast('226' as Varchar(3)) ProcessID,
Sum(Isnull(CuttingQty,0)) CuttingQty,Sum(Isnull(Qty226,0)) Qty226
from
ShipDateQtyPush with (nolock)
where
PO=@PO
Group by
PO,Convert(varchar(10),ShipDate,120),Color,Sizx,Qty
Insert into --100
#ee (PO,ShipDate,Color,Sizx,Qty,ProcessID,CuttingQty,Qtyx)
Select
PO,Convert(varchar(10),ShipDate,120) ShipDate,Color,Sizx,Sum(Qty) Qty,Cast('371' as Varchar(3)) ProcessID,
Sum(Isnull(CuttingQty,0)) CuttingQty,Sum(Isnull(Qty371,0)) Qty371
from
ShipDateQtyPush with (nolock)
where
PO=@PO
Group by
PO,Convert(varchar(10),ShipDate,120),Color,Sizx,Qty
Insert into
#ee (PO,ShipDate,Color,Sizx,Qty,ProcessID,CuttingQty,Qtyx)
Select
PO,Convert(varchar(10),ShipDate,120) ShipDate,Color,Sizx,Sum(Qty) Qty,Cast('382' as Varchar(3)) ProcessID,
Sum(Isnull(CuttingQty,0)) CuttingQty,Sum(Isnull(Qty382,0)) Qty382
from
ShipDateQtyPush with (nolock)
where
PO=@PO
Group by
PO,Convert(varchar(10),ShipDate,120),Color,Sizx,Qty
Insert into
#ee (PO,ShipDate,Color,Sizx,Qty,ProcessID,CuttingQty,Qtyx) --126
Select
PO,Convert(varchar(10),ShipDate,120) ShipDate,Color,Sizx,Sum(Qty) Qty,Cast('425' as Varchar(3)) ProcessID,
Sum(Isnull(CuttingQty,0)) CuttingQty,Sum(Isnull(Qty425,0)) Qty425
from
ShipDateQtyPush with (nolock)
where
PO=@PO
Group by
PO,Convert(varchar(10),ShipDate,120),Color,Sizx,Qty
Insert into
#ee (PO,ShipDate,Color,Sizx,Qty,ProcessID,CuttingQty,Qtyx)
Select
PO,Convert(varchar(10),ShipDate,120) ShipDate,Color,Sizx,Sum(Qty) Qty,Cast('345' as Varchar(3)) ProcessID,
Sum(Isnull(CuttingQty,0)) CuttingQty,Sum(Isnull(Qty345,0)) Qty345
from
ShipDateQtyPush with (nolock)
where
PO=@PO
Group by
PO,Convert(varchar(10),ShipDate,120),Color,Sizx,Qty
Insert into
#ee (PO,ShipDate,Color,Sizx,Qty,ProcessID,CuttingQty,Qtyx)
Select
PO,Convert(varchar(10),ShipDate,120) ShipDate,Color,Sizx,Sum(Qty) Qty,Cast('397' as Varchar(3)) ProcessID,
Sum(Isnull(CuttingQty,0)) CuttingQty,Sum(Isnull(Qty397,0)) Qty397
from
ShipDateQtyPush with (nolock)
where
PO=@PO
Group by
PO,Convert(varchar(10),ShipDate,120),Color,Sizx,Qty --160
Insert into
#ee (PO,ShipDate,Color,Sizx,Qty,ProcessID,CuttingQty,Qtyx)
Select
PO,Convert(varchar(10),ShipDate,120) ShipDate,Color,Sizx,Sum(Qty) Qty,Cast('396' as Varchar(3)) ProcessID,
Sum(Isnull(CuttingQty,0)) CuttingQty,Sum(Isnull(Qty396,0)) Qty396
from
ShipDateQtyPush with (nolock)
where
PO=@PO
Group by
PO,Convert(varchar(10),ShipDate,120),Color,Sizx,Qty
Insert into
#ee (PO,ShipDate,Color,Sizx,Qty,ProcessID,CuttingQty,Qtyx)
Select
PO,Convert(varchar(10),ShipDate,120) ShipDate,Color,Sizx,Sum(Qty) Qty,Cast('326' as Varchar(3)) ProcessID,
Sum(Isnull(CuttingQty,0)) CuttingQty,Sum(Isnull(Qty326,0)) Qty326
from
ShipDateQtyPush with (nolock)
where
PO=@PO
Group by
PO,Convert(varchar(10),ShipDate,120),Color,Sizx,Qty
Insert into
#ee (PO,ShipDate,Color,Sizx,Qty,ProcessID,CuttingQty,Qtyx)
Select
PO,Convert(varchar(10),ShipDate,120) ShipDate,Color,Sizx,Sum(Qty) Qty,Cast('328' as Varchar(3)) ProcessID,
Sum(Isnull(CuttingQty,0)) CuttingQty,Sum(Isnull(Qty328,0)) Qty328
from
ShipDateQtyPush with (nolock)
where
PO=@PO
Group by
PO,Convert(varchar(10),ShipDate,120),Color,Sizx,Qty
Insert into
#ee (PO,ShipDate,Color,Sizx,Qty,ProcessID,CuttingQty,Qtyx)
Select --200
PO,Convert(varchar(10),ShipDate,120) ShipDate,Color,Sizx,Sum(Qty) Qty,Cast('330' as Varchar(3)) ProcessID,
Sum(Isnull(CuttingQty,0)) CuttingQty,Sum(Isnull(Qty330,0)) Qty330
from
ShipDateQtyPush with (nolock)
where
PO=@PO
Group by
PO,Convert(varchar(10),ShipDate,120),Color,Sizx,Qty
Insert into
#ee (PO,ShipDate,Color,Sizx,Qty,ProcessID,CuttingQty,Qtyx)
Select
PO,Convert(varchar(10),ShipDate,120) ShipDate,Color,Sizx,Sum(Qty) Qty,Cast('331' as Varchar(3)) ProcessID,
Sum(Isnull(CuttingQty,0)) CuttingQty,Sum(Isnull(Qty331,0)) Qty331
from
ShipDateQtyPush with (nolock)
where
PO=@PO
Group by
PO,Convert(varchar(10),ShipDate,120),Color,Sizx,Qty
INSERT INTO TempTable SELECT 'step2', DATEDIFF(SECOND, @begin, GETDATE())--第2个地方
SET @begin = GETDATE()
Declare @POb Varchar(10)
Declare @Colorb Varchar(50)
Declare @Sizxb Varchar(10)
Declare @Qtyb Varchar(10)
Declare @IDa Int
Declare @POa Varchar(10)
Declare @Colora Varchar(50)
Declare @Sizxa Varchar(10)
Declare @Qtya Int
Declare @CuttingQtya Int
Declare @if Int
Declare Cur_b Cursor for
Select PO,Color,Sizx,Qty from #ProcessQty where ProcessID='900'
Open Cur_b
Fetch Cur_b into @POb,@Colorb,@Sizxb,@Qtyb
while @@Fetch_Status=0
begin
Set @if=0
Declare Cur_a Cursor for
Select id,PO,Color,Sizx,Qty,CuttingQty from #ee with (nolock) where PO=@POb and Color=@Colorb and Sizx=@Sizxb and ProcessID='900' order by ShipDate
Open Cur_a
Fetch Cur_a into @IDa,@POa,@Colora,@Sizxa,@Qtya,@CuttingQtya
while @@Fetch_Status=0
begin
if @Qtyb>0
begin
if @Qtya>=@CuttingQtya+@Qtyb
begin
Update #ee Set CuttingQty=@CuttingQtya+@Qtyb where id=@IDa
Set @Qtyb=0
end
else if @Qtya<@CuttingQtya+@Qtyb
begin
Update #ee Set CuttingQty=@Qtya where id=@IDa
Set @Qtyb=@Qtyb-(@Qtya-@CuttingQtya)
end
Set @if=1
end
Fetch Cur_a into @IDa,@POa,@Colora,@Sizxa,@Qtya,@CuttingQtya
end
if (@Qtyb>0) and (@if=1)
begin
Update #ee Set CuttingQty=CuttingQty+@Qtyb where id=@IDa
Set @Qtyb=0
end
Close Cur_a
DealLocate Cur_a
Fetch Cur_b into @POb,@Colorb,@Sizxb,@Qtyb
end
Close Cur_b
DealLocate Cur_b
INSERT INTO TempTable SELECT 'step2', DATEDIFF(SECOND, @begin, GETDATE())--第3个地方
SET @begin = GETDATE()
