优化存储过程
下面是对单据进行审核时执行的存储过程。以前运行起来速度还可以 ，也不会出什么错，大概一分钟就可以完成。
现在出现了如下问题：
1、存储过程执行时间长达5-15分钟；
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
[解决办法]
1、存储过程执行时间长达5-15分钟；
--------------------
是不是事务写的太长了?
2、经常报错或执行不了。比如退货时没有修改成负数，还是正数。
------------------------
代码逻辑有错误呗
[解决办法]
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
--运行一次从正数改成负数,再运行一次该回正数......
