X
xej
Unregistered / Unconfirmed
GUEST, unregistred user!
本人在sql server后台写了一个报表的存储过程,但在2万条数据的情况下运行的时间却将近几十分钟。请教各位如何优化和少用哪些命令或其它的原因能提高运行速度。存储过程部分代码如下:<br> if @billormoney=1<br> begin<br> insert into #temp(wh_billofdocument_id,wh_warehouse_id,wh_warehousein_id,bs_product_id,<br> bs_customer_id ,bs_personnel_id ,bs_department_id,bs_curency_id,sign,<br> salespromotion_sign , largess_sign , detailremark , masterremark,<br> code , billcode , billlistdate , listdate , price , quantity ,amount,<br> rate)<br> select a.wh_billofdocument_id,c.wh_warehouse_id,c.wh_warehousein_id,<br> c.bs_product_id,b.bs_customer_id,b.bs_personnel_id,b.bs_department_id,b.bs_curency_id,b.sign,<br> c.salespromotion_sign,c.largess_sign,c.remark as detailremark,b.remark as masterremark,<br> b.code,b.billcode, b.listdate as billlistdate,c.listdate,c.price,<br> case when b.sign=8 then isnull(c.outquantity,0)-isnull(c.quantity,0)<br> else isnull(c.quantity,0)-isnull(c.outquantity,0)<br> end as quantity,<br> dbo.f_get_Amount(a.wh_billofdocument_id) as amount,<br> case when abs(dbo.f_get_Account(a.wh_billofdocument_id,1))<>0 <br> and (abs(dbo.f_get_Account(a.wh_billofdocument_id,0))>=abs(dbo.f_get_Account(a.wh_billofdocument_id,1))) then cast( 1 as decimal(18,4))<br> when abs(dbo.f_get_Account(a.wh_billofdocument_id,1))<>0 and (abs(dbo.f_get_Account(a.wh_billofdocument_id,0))<abs(dbo.f_get_Account(a.wh_billofdocument_id,1)))<br> then cast((abs(dbo.f_get_Account(a.wh_billofdocument_id,0)))/(abs(dbo.f_get_Account(a.wh_billofdocument_id,1))) as decimal(18,4))<br> else 0<br> end as rate<br> from t_wh_billofdocumentpayment_detail as a <br> left join t_wh_billofdocument as b on a.wh_billofdocument_id=b.id<br> left join t_wh_billofdocument_detail as c on c.wh_billofdocument_id=b.id<br> where ((@auditing is null) or (@auditing=0 and b.auditing is null) or (@auditing=1 and b.auditing is not null))<br> and (b.cancellation is null) and (b.sign in (2,3,5,6,7,8,10,11,14,15,18,20))<br> order by b.id<br> end<br> else<br> begin<br> insert into #temp(wh_billofdocument_id,wh_warehouse_id,wh_warehousein_id,bs_product_id,<br> bs_customer_id ,bs_personnel_id ,bs_department_id,bs_curency_id,sign,<br> salespromotion_sign , largess_sign , detailremark , masterremark,<br> code , billcode , billlistdate , listdate , price , quantity ,amount,<br> rate)<br> select d.wh_billofdocument_id,d.wh_warehouse_id,d.wh_warehousein_id,<br> d.bs_product_id,bs_customer_id,bs_personnel_id,bs_department_id,bs_curency_id,sign,<br> d.salespromotion_sign,d.largess_sign,d.remark as detailremark,pd.remark as masterremark,<br> pd.code,pd.billcode, pd.listdate as billlistdate,d.listdate,d.price,<br> case when pd.sign=8 then isnull(outquantity,0)-isnull(quantity,0)<br> else isnull(d.quantity,0)-isnull(d.outquantity,0)<br> end as quantity,<br> dbo.f_get_Amount(d.wh_billofdocument_id) as amount,<br> case when abs(dbo.f_get_Account(d.wh_billofdocument_id,1))<>0 <br> and (abs(dbo.f_get_Account(d.wh_billofdocument_id,0))>=abs(dbo.f_get_Account(d.wh_billofdocument_id,1))) then cast( 1 as decimal(18,4))<br> when abs(dbo.f_get_Account(d.wh_billofdocument_id,1))<>0 and (abs(dbo.f_get_Account(d.wh_billofdocument_id,0))<abs(dbo.f_get_Account(d.wh_billofdocument_id,1)))<br> then cast((abs(dbo.f_get_Account(d.wh_billofdocument_id,0)))/(abs(dbo.f_get_Account(d.wh_billofdocument_id,1))) as decimal(18,4))<br> else 0<br> end as rate<br> from t_wh_billofdocument_detail as d <br> left join t_wh_billofdocument as pd on d.wh_billofdocument_id=pd.id<br> where ((@auditing is null) or (@auditing=0 and pd.auditing is null) or (@auditing=1 and pd.auditing is not null))<br> and (pd.cancellation is null) and (pd.sign in (2,3,5,6,7,8,10,11,14,15,18,20))<br> order by pd.id<br> end<br> end<br> declare @quantity decimal(18,4),@amount decimal(18,4),@rowid int<br> while (exists (select * from #temp)) <br> begin <br> select @rowid=rowid from (select Top 1 rowid,quantity from #temp order by rowid) as p <br> if @rowid>1<br> select @quantity=isnull(surplusquantity,0),@amount=isnull(surplusamount,0) from #const_price where rowid=@rowid-1 <br> set @quantity=isnull(@quantity,0)<br> set @amount=isnull(@amount,0) <br> insert into #const_price(rowid,wh_billofdocument_id,wh_warehouse_id,bs_product_id,wh_warehousein_id,bs_customer_id,bs_personnel_id,bs_department_id,bs_curency_id,sign,salespromotion_sign,largess_sign,detailremark,masterremark,code,billcode,billlistdate,listdate,price,quantity,amount,rate,percentage,billtype,constprice,constamount,surplusamount,surplusquantity) <br> select @rowid,wh_billofdocument_id,wh_warehouse_id,bs_product_id,wh_warehousein_id,bs_customer_id,<br> bs_personnel_id,bs_department_id,bs_curency_id,sign,salespromotion_sign,largess_sign,<br> detailremark,masterremark,code,billcode,billlistdate,listdate,price,quantity,amount,rate,<br> case when ((dbo.f_get_billofdocument_quantity(wh_billofdocument_id))<>0 <br> and (quantity)>=abs(dbo.f_get_billofdocument_quantity(wh_billofdocument_id))) then cast( 1 as decimal(18,4))<br> when abs(dbo.f_get_billofdocument_quantity(wh_billofdocument_id))<>0 <br> and (abs(quantity)<abs(dbo.f_get_billofdocument_quantity(wh_billofdocument_id)))<br> then cast(abs(quantity/dbo.f_get_billofdocument_quantity(wh_billofdocument_id)) as decimal(18,4))<br> else 0<br> end as percentage,<br> case<br> when (sign=7 and quantity>=0) then '单个盘点(盘赢)'<br> when (sign=8 and quantity>=0) then '全面盘点(盘赢)'<br> when (sign=20 and quantity>=0) then '自动盘点(盘赢)'<br> when (sign=7 and quantity<0) then '单个盘点(盘亏)'<br> when (sign=8 and quantity<0) then '全面盘点(盘亏)'<br> when (sign=20 and quantity<0) then '自动盘点(盘亏)'<br> when (sign=2 ) then '商品进仓'<br> when (sign=5 ) then '商品调入'<br> when (sign=15) then '销售退货'<br> when (sign=3 ) then '商品出仓'<br> when (sign=6 ) then '商品调出'<br> when (sign=14) then '商品报损'<br> when (sign=18) then '采购退货'<br> when (sign=10) then '业务销售'<br> when (sign=11) then '门市销售'<br> end as billtype,<br> case <br> when (sign in (10,11,15)and @quantity<>0) then abs(cast((@amount/@quantity) as decimal(18,4)))<br> when (sign in (10,11,15)and @quantity=0) then 0<br> else price <br> end as constprice,<br> case <br> when (sign in (10,11,15)and @quantity<>0) then abs(cast((@amount/@quantity) as decimal(18,4)))*quantity<br> when (sign in (10,11,15)and @quantity=0) then 0<br> else price*quantity end as constamount,<br> case <br> when (sign in (10,11)and @quantity<>0) then cast((@amount-amount) as decimal(18,4))<br> else cast((@amount+amount) as decimal(18,4))<br> end as surplusamount,<br> case <br> when sign in (2,5,15, 7, 8,20) then (@quantity+quantity) <br> when sign in (3,6,10,11,14,18) then (@quantity-quantity) <br> end as surplusquantity<br> from #temp <br> where rowid=@rowid<br> delete #temp where rowid=@rowid<br> end