请教各位如何优化这个存储过程增加运行速度(50分)

X

xej

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

xinjinren

Unregistered / Unconfirmed
GUEST, unregistred user!
你该说明你有什么样的数据,需要进行什么样的操作,得到什么样的结果[:)]
 
H

hityou

Unregistered / Unconfirmed
GUEST, unregistred user!
不太清楚业务,不好分析,<br><br>有个建议,select的那几个语句写成几个小的存储过程,使逻辑清楚一些,也不会造成死锁一类的问题.
 
D

deardai

Unregistered / Unconfirmed
GUEST, unregistred user!
沒仔細看,從書上抄一段話給你<br>(索引列一旦使用了函数,索引就会变无效, &nbsp;索引列不要使用NOT由于索引列一旦使用不等于,索引就会变得无效,最后导致速度变慢。不要用NULL如果用了NULL,索引就会无效,变成全表检索,影响处理速度。不要对索引列进行计算如果对索引列进行计算,索引就会无效,也后导致速度变慢。)另外這一句還是看到啦while (exists (select * from #temp)) &nbsp; &nbsp; &nbsp; &nbsp;可以改為while (exists (select top 1 * from #temp))
 

散漫人

Unregistered / Unconfirmed
GUEST, unregistred user!
呵呵,估计有毅力看完整个代码的人不多
 
顶部