帮忙看该存储过程,巨慢,客户不能忍受,如何提高速度?
CREATE PROCEDURE [baogong_qr] /*报工单与入库单审核*/
AS
set nocount on
begin
declare @pici varchar(20)
declare @gx integer
declare @prod_quan integer
declare @bao_shl integer
declare @not_jie integer select @not_jie=count(*)
from product_quan
where jie=0print 0 if @not_jie is null set @not_jie=0 if @not_jie=0 return 0 begin tran tran_bao/*报工验证:非等效工序类*/ update product_quan
set baogong='0' --数量相等
where prod_pici+'/'+convert(varchar,gx)
in(
select a.prod_pici+'/'+convert(varchar,a.gx)
from v_prod_gx_sum a,v_baogong_sum b
where a.prod_pici=b.pici
and a.shl=b.shl)print 1 if @@error<>0 rollback tran tran_bao update product_quan
set baogong='1' --小于报工
where prod_pici+'/'+convert(varchar,gx)
in(
select a.prod_pici+'/'+convert(varchar,a.gx)
from v_prod_gx_sum a,v_baogong_sum b
where a.prod_pici=b.pici
and a.shl<b.shl)print 2 if @@error<>0 rollback tran tran_bao update product_quan
set baogong='2' --大于报工
where prod_pici+'/'+convert(varchar,gx)
in(
select a.prod_pici+'/'+convert(varchar,a.gx)
from v_prod_gx_sum a,v_baogong_sum b
where a.prod_pici=b.pici
and a.shl>b.shl)print 3 if @@error<>0 rollback tran tran_bao update product_quan
set baogong='3'
where prod_pici
not in (select pici from baogong) print 4 if @@error<>0 rollback tran tran_bao/*报工验证:等效工序类*/delete from bg_mid_1insert bg_mid_1
select prod_pici,gx,shl=sum(prod_quan)
from product_quan
where
jie=0
and ((
(prod_id+'.'+convert(varchar,gx)) in
(select (prod_id+'.'+convert(varchar,gx))
from prod_gx where gx in(select zi_gx from gx_deng where fu_gx<>zi_gx)))
or
((prod_id+'.'+convert(varchar,gx)) in
(select (c.prod_id+'.'+convert(varchar,d.fu_gx))
from prod_gx c,gx_deng d
where c.gx in(select zi_gx from gx_deng where fu_gx<>zi_gx)
and c.gx=d.zi_gx
)))
group by prod_pici,gxprint 5 if @@error<>0 rollback tran tran_bao/*工序替换为父工序 */
update bg_mid_1
set gx=b.fu_gx
from bg_mid_1 a,gx_deng b
where a.gx=b.zi_gx print 6 if @@error<>0 rollback tran tran_baodelete from bg_mid
if @@error<>0 rollback tran tran_baoinsert bg_mid
select prod_pici,gx,shl=sum(shl)
from bg_mid_1
group by prod_pici,gx
if @@error<>0 rollback tran tran_baoupdate product_quan
set baogong='0'
from product_quan c,gx_deng d
where c.prod_pici+'/'+convert(varchar,d.fu_gx) --工序替换为父工序
in(
select a.prod_pici+'/'+convert(varchar,a.gx) --合格者
from bg_mid a,v_baogong_sum b
where a.prod_pici=b.pici
and a.shl=b.shl)
and c.gx=d.zi_gx
and
((
(prod_id+'.'+convert(varchar,gx)) in
(select (prod_id+'.'+convert(varchar,gx))
from prod_gx where gx in(select zi_gx from gx_deng where fu_gx<>zi_gx)))
or
((prod_id+'.'+convert(varchar,gx)) in
(select (c.prod_id+'.'+convert(varchar,d.fu_gx))
from prod_gx c,gx_deng d
where c.gx in(select zi_gx from gx_deng where fu_gx<>zi_gx)
and c.gx=d.zi_gx
))) if @@error<>0 rollback tran tran_baoupdate product_quan
set baogong='1'
from product_quan c,gx_deng d --不合格者
where c.prod_pici+'/'+convert(varchar,d.fu_gx) --工序替换为父工序
not in(
select a.prod_pici+'/'+convert(varchar,a.gx) --合格者
from bg_mid a,v_baogong_sum b
where a.prod_pici=b.pici
and a.shl=b.shl)
and c.gx=d.zi_gx
and
((
(prod_id+'.'+convert(varchar,gx)) in
(select (prod_id+'.'+convert(varchar,gx))
from prod_gx where gx in(select zi_gx from gx_deng where fu_gx<>zi_gx)))
or
((prod_id+'.'+convert(varchar,gx)) in
(select (c.prod_id+'.'+convert(varchar,d.fu_gx))
from prod_gx c,gx_deng d
where c.gx in(select zi_gx from gx_deng where fu_gx<>zi_gx)
and c.gx=d.zi_gx
))) if @@error<>0 rollback tran tran_bao commit tran tran_baoreturn 1end
GO
CREATE PROCEDURE [baogong_qr] /*报工单与入库单审核*/
AS
set nocount on
begin
declare @pici varchar(20)
declare @gx integer
declare @prod_quan integer
declare @bao_shl integer
declare @not_jie integer select @not_jie=count(*)
from product_quan
where jie=0print 0 if @not_jie is null set @not_jie=0 if @not_jie=0 return 0 begin tran tran_bao/*报工验证:非等效工序类*/ update product_quan
set baogong='0' --数量相等
where prod_pici+'/'+convert(varchar,gx)
in(
select a.prod_pici+'/'+convert(varchar,a.gx)
from v_prod_gx_sum a,v_baogong_sum b
where a.prod_pici=b.pici
and a.shl=b.shl)print 1 if @@error<>0 rollback tran tran_bao update product_quan
set baogong='1' --小于报工
where prod_pici+'/'+convert(varchar,gx)
in(
select a.prod_pici+'/'+convert(varchar,a.gx)
from v_prod_gx_sum a,v_baogong_sum b
where a.prod_pici=b.pici
and a.shl<b.shl)print 2 if @@error<>0 rollback tran tran_bao update product_quan
set baogong='2' --大于报工
where prod_pici+'/'+convert(varchar,gx)
in(
select a.prod_pici+'/'+convert(varchar,a.gx)
from v_prod_gx_sum a,v_baogong_sum b
where a.prod_pici=b.pici
and a.shl>b.shl)print 3 if @@error<>0 rollback tran tran_bao update product_quan
set baogong='3'
where prod_pici
not in (select pici from baogong) print 4 if @@error<>0 rollback tran tran_bao/*报工验证:等效工序类*/delete from bg_mid_1insert bg_mid_1
select prod_pici,gx,shl=sum(prod_quan)
from product_quan
where
jie=0
and ((
(prod_id+'.'+convert(varchar,gx)) in
(select (prod_id+'.'+convert(varchar,gx))
from prod_gx where gx in(select zi_gx from gx_deng where fu_gx<>zi_gx)))
or
((prod_id+'.'+convert(varchar,gx)) in
(select (c.prod_id+'.'+convert(varchar,d.fu_gx))
from prod_gx c,gx_deng d
where c.gx in(select zi_gx from gx_deng where fu_gx<>zi_gx)
and c.gx=d.zi_gx
)))
group by prod_pici,gxprint 5 if @@error<>0 rollback tran tran_bao/*工序替换为父工序 */
update bg_mid_1
set gx=b.fu_gx
from bg_mid_1 a,gx_deng b
where a.gx=b.zi_gx print 6 if @@error<>0 rollback tran tran_baodelete from bg_mid
if @@error<>0 rollback tran tran_baoinsert bg_mid
select prod_pici,gx,shl=sum(shl)
from bg_mid_1
group by prod_pici,gx
if @@error<>0 rollback tran tran_baoupdate product_quan
set baogong='0'
from product_quan c,gx_deng d
where c.prod_pici+'/'+convert(varchar,d.fu_gx) --工序替换为父工序
in(
select a.prod_pici+'/'+convert(varchar,a.gx) --合格者
from bg_mid a,v_baogong_sum b
where a.prod_pici=b.pici
and a.shl=b.shl)
and c.gx=d.zi_gx
and
((
(prod_id+'.'+convert(varchar,gx)) in
(select (prod_id+'.'+convert(varchar,gx))
from prod_gx where gx in(select zi_gx from gx_deng where fu_gx<>zi_gx)))
or
((prod_id+'.'+convert(varchar,gx)) in
(select (c.prod_id+'.'+convert(varchar,d.fu_gx))
from prod_gx c,gx_deng d
where c.gx in(select zi_gx from gx_deng where fu_gx<>zi_gx)
and c.gx=d.zi_gx
))) if @@error<>0 rollback tran tran_baoupdate product_quan
set baogong='1'
from product_quan c,gx_deng d --不合格者
where c.prod_pici+'/'+convert(varchar,d.fu_gx) --工序替换为父工序
not in(
select a.prod_pici+'/'+convert(varchar,a.gx) --合格者
from bg_mid a,v_baogong_sum b
where a.prod_pici=b.pici
and a.shl=b.shl)
and c.gx=d.zi_gx
and
((
(prod_id+'.'+convert(varchar,gx)) in
(select (prod_id+'.'+convert(varchar,gx))
from prod_gx where gx in(select zi_gx from gx_deng where fu_gx<>zi_gx)))
or
((prod_id+'.'+convert(varchar,gx)) in
(select (c.prod_id+'.'+convert(varchar,d.fu_gx))
from prod_gx c,gx_deng d
where c.gx in(select zi_gx from gx_deng where fu_gx<>zi_gx)
and c.gx=d.zi_gx
))) if @@error<>0 rollback tran tran_bao commit tran tran_baoreturn 1end
GO
where prod_pici+'/'+convert(varchar,gx) in( 这样的计算值在WHERE子句里完全利用不上索引,当初做表时没考虑为了性能设计一些中间结果列保存结算值?