跪求高手帮忙解决,优化存储过程!

[ldddd]

下面是对单据进行审核时执行的存储过程。以前运行起来速度还可以 ，也不会出什么错，大概一分钟就可以完成。
现在出现了如下问题：
1、存储过程执行时间长达5-15分钟,占用很大的CPU,使其它程序无法继续；
2、经常报错或执行不了。比如退货时没有修改成负数，还是正数。
请各位大虾帮忙解决！看如何优化此存储过程,谢谢！


/*---------------------
入库出库单审核时
更新商品目录表
更新库存表
*/

CREATE PROCEDURE pcargosh(@orderno varchar(50),@orderlb varchar(50),@shrr varchar(50))AS
set nocount on
if (@orderlb='TH')or(@orderlb='SH')
begin
update pbscitems set quantity=-quantity,amount=-amount where orderno=@orderno
update pcargo set quantity=-quantity,subtotal=-subtotal, mysubtal=-mysubtal where orderno=@orderno
end

declare @storename varchar(50)
declare @storeno1 varchar(50)
declare @storemax int
select @storename=storename,@storeno1=storeno1 from pcargo where orderno=@orderno

if @orderlb='PD'
begin
update pbscitems set storename=@storename where orderno=@orderno
select bookid,sum(isnull(quantity,0)) quantity,storename into #tmp1 from pbscitems where orderno=@orderno and lb=@orderlb group by bookid,storename
declare @maxpcb int
select @maxpcb=isnull(max(id),0) from pcb
select identity(int,1,1) id,bookid,quantity,storename into #tmp2 from #tmp1 where bookid not in(select bookid from pcb where storename=@storename)

insert into pcb(id,bookid,storename)select @maxpcb+id,bookid,storename from #tmp2
update pcb set quantity1=quantity1+#tmp1.quantity from #tmp1 where #tmp1.bookid=pcb.bookid and #tmp1.storename=pcb.storename

return 0
end
declare @shr varchar(50)
select @shr=shr from pcargo where orderno=@orderno and lb=@orderlb
if @shr is not null
return 0


update pbscitems set storename=@storename where orderno=@orderno and (storename is null or storename='')

BEGIN TRAN T1
create table #tmptb(bookid int null,sl float null,zje float null,storename varchar(50) null)

insert into #tmptb(bookid,sl,zje,storename)select pbscitems.bookid,sum(isnull(pbscitems.quantity,0)),sum(isnull(pbscitems.quantity*cargo.price,0)),pbscitems.storename from pbscitems,cargo where pbscitems.bookid=cargo.cargo_id and pbscitems.orderno=@orderno group by pbscitems.bookid,pbscitems.storename

/*insert into #tmptb(bookid,sl,zje,storename)select bookid,sum(isnull(quantity,0)),sum(isnull(amount,0)),storename from pbscitems where orderno=@orderno group by bookid,storename*/

select @storemax=isnull(max(id),0) from currentstore
select identity(int,1,1) id,bookid,storename into #tmptb1 from #tmptb where bookid not in(select bookid from currentstore where storename<>@storeno1)
select identity(int,1,1) id,bookid into #tmptb2 from #tmptb where bookid not in(select bookid from currentstore where storename=@storeno1)
update pcargo set hkye=subtotal,fpjf='否' where orderno=@orderno
/*
更新在单数
*/
if (@orderlb='S')or(@orderlb='B')
update orderit set onsave=isnull(orderit.onsave,0)+pbscitems.quantity from pbscitems where pbscitems.orderbh=orderit.bh and orderit.id=pbscitems.orderid and pbscitems.orderno=@orderno

if(@orderlb='B')or(@orderlb='PY')or(@orderlb='TH')or(@orderlb='QC')
begin
/* 更新目录表信息*/
update cargo set quantity=quantity+#tmptb.sl,amount=amount+#tmptb.zje from #tmptb where cargo.cargo_id=#tmptb.bookid
end
if @orderlb='B'
begin
update cargo set onorder=onorder-tmpsl.sl from (select bookid,sum(quantity) sl from pbscitems where orderno=@orderno and orderbh is not null group by bookid) tmpsl where tmpsl.bookid=cargo.cargo_id
update cargo set avgprice=amount/quantity where cargo_id in(select bookid from #tmptb) and quantity<>0
update cargo set avgprice=0 where quantity=0
/*更新库存分布表*/
select identity(int,1,1) id,bookid,storename into #tmptb3 from #tmptb where bookid not in(select bookid from currentstore where storename in(select storename from pbscitems where orderno=@orderno group by storename))

insert into currentstore(id,bookid,storename)select @storemax+id,bookid,storename from #tmptb3
update currentstore set quantity=quantity+#tmptb.sl,amount=amount+#tmptb.zje from #tmptb where currentstore.bookid=#tmptb.bookid and currentstore.storename=#tmptb.storename
update cust set ys=isnull(cust.ys,0)+isnull(pcargo.subtotal,0) from cust,pcargo where orderno=@orderno and cust.custno=pcargo.custno
end
if(@orderlb='S')or(@orderlb='PK')or(@orderlb='SH')
begin
update cargo set quantity=quantity-#tmptb.sl from #tmptb where cargo.cargo_id=#tmptb.bookid
end
if @orderlb='S'
begin
update cargo set onorder1=onorder1-tmpsl.sl from (select bookid,sum(quantity) sl from pbscitems where orderno=@orderno and orderbh is not null group by bookid) tmpsl where tmpsl.bookid=cargo.cargo_id
update cargo set amount=quantity*price where cargo_id in(select bookid from #tmptb)
/*更新库存分布表*/

select identity(int,1,1) id,bookid,storename into #tmptb4 from #tmptb where bookid not in(select bookid from currentstore where storename in(select storename from pbscitems where orderno=@orderno group by storename))
insert into currentstore(id,bookid,storename)select @storemax+id,bookid,storename from #tmptb4
update currentstore set quantity=quantity-#tmptb.sl from #tmptb where currentstore.bookid=#tmptb.bookid and currentstore.storename=#tmptb.storename
update currentstore set currentstore.amount=currentstore.quantity*cargo.price from cargo where cargo.cargo_id=currentstore.bookid and currentstore.bookid in(select bookid from #tmptb)
update cust set ys=isnull(cust.ys,0)+isnull(pcargo.subtotal,0) from cust,pcargo where orderno=@orderno and cust.custno=pcargo.custno
end
if(@orderlb='TB')
begin
/*更新出库数量*/
update currentstore set quantity=quantity-#tmptb.sl from #tmptb where currentstore.bookid=#tmptb.bookid and currentstore.storename=#tmptb.storename
update currentstore set currentstore.amount=currentstore.quantity*cargo.price from cargo where cargo.cargo_id=currentstore.bookid and currentstore.bookid in(select bookid from #tmptb)
/*更新入库数量*/
insert into currentstore(id,bookid,storename)select @storemax+id,bookid,@storeno1 from #tmptb2
update currentstore set quantity=quantity+#tmptb.sl,amount=amount+#tmptb.zje from #tmptb where currentstore.bookid=#tmptb.bookid and currentstore.storename=@storeno1

end
update pcargo set shr=@shrr where orderno=@orderno
if @@error<>0
begin
rollback tran T1
end
else
begin
COMMIT TRAN T1
end
GO

[朴实的草根]

基本上没有人愿意花时间来改~

[朴实的草根]

花了一分种看了下，首先看到有not in 而且后面还不是临时表，是一张有可能会很大的数据表，这时这句执行起来会很慢.所以首先你可以考虑是否可以替换这个语句.

[Limpire]

经常报错或执行不了。比如退货时没有修改成负数，还是正数。
-----------------------------------------
报什么错呢，执行不了又是什么情况呢

[Limpire]

这么长的存储过程，没有实际环境测试，是很难优化的。

你可以把各个功能模块的执行时间打印出来，看看是哪部分耗时，针对性优化才行：
declare @start datetime
set @start = getdate()
--执行功能模块
print datediff(ms, @start, getdate())

[ldddd]

主要原因是表中的数据比较大，一个是cargo表中有大约60万条数据，pcargo（主单表）和pbscitems(明细表)中的数据也有上百万。如果数据量小存储过程执行起来很快。系统用了大概两年了，现在是越来越慢了。请各位大侠出手相助，优化此存储过程。不胜感激！！！

[苏明星]

占座学习下

[qufusongyu]

1、想办法把历史数据弄出来，使在用数据容量缩小，速度明显提高。
2、用作业先把需要审核的数据先弄出来，然后再审核。