跪求高手帮忙解决,优化存储过程!
下面是对单据进行审核时执行的存储过程。以前运行起来速度还可以 ，也不会出什么错，大概一分钟就可以完成。
现在出现了如下问题：
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 而且后面还不是临时表，是一张有可能会很大的数据表，这时这句执行起来会很慢.所以首先你可以考虑是否可以替换这个语句.
[解决办法]
这么长的存储过程，没有实际环境测试，是很难优化的。
你可以把各个功能模块的执行时间打印出来，看看是哪部分耗时，针对性优化才行：
declare @start datetime
set @start = getdate()
--执行功能模块
print datediff(ms, @start, getdate())
[解决办法]
1、想办法把历史数据弄出来，使在用数据容量缩小，速度明显提高。
2、用作业先把需要审核的数据先弄出来，然后再审核。
