批量处理数据38分钟优化到8秒-CSDN博客

开发提报一个存储过程，处理400W条数据几乎跑不动，10W条数据跑了38分钟，晕死。开始优化

原过程

CREATE OR REPLACE PROCEDURE p_fp_wlfp_yjj_10 is

cursor cur_wlfp_yjj is

with wp as (select a.uuid,

a.djxh,

a.fpzl_dm,

a.fp_dm,

a.fs,

a.fpqshm,

a.fpzzhm,

a.fpkjqk_dm,

a.kpqsrq,

a.kpjzrq,

a.kpje,

a.se,

a.lrr_dm,

a.swjg_dm,

a.sjgsdq

from hx_fp.FP_WLFP_KJXX_CYH10 a

where a.FPKJQK_DM = '10'

and a.sfyyj='N'

-- and a.swjg_dm = ?

--and a.sjgsdq like ?

)

select wp.* from wp order by wp.djxh,wp.fpzl_dm,wp.fp_dm,wp.fpqshm;

type resultset is table of cur_wlfp_yjj%rowtype;

r resultset;

--外层大循环的次数

j number;

temp_djxh hx_fp.FP_WLFP_KJXX_CYH10.DJXH%TYPE;

temp_fpzl_dm hx_fp.FP_WLFP_KJXX_CYH10.fpzl_dm%type;

temp_fp_dm hx_fp.FP_WLFP_KJXX_CYH10.fp_dm%type;

temp_fs hx_fp.FP_WLFP_KJXX_CYH10.fs%type;

temp_fpqshm hx_fp.FP_WLFP_KJXX_CYH10.fpqshm%type;

temp_fpzzhm hx_fp.FP_WLFP_KJXX_CYH10.fpzzhm%type;

--temp_fpkjqk_dm hx_fp.FP_WLFP_KJXX_CYH10.fpkjqk_dm%type;

temp_kpqsrq hx_fp.FP_WLFP_KJXX_CYH10.kpqsrq%type;

temp_kpjzrq hx_fp.FP_WLFP_KJXX_CYH10.kpjzrq%type;

temp_kpje hx_fp.FP_WLFP_KJXX_CYH10.kpje%type;

temp_se hx_fp.FP_WLFP_KJXX_CYH10.se%type;

temp_lrr_dm hx_fp.FP_WLFP_KJXX_CYH10.lrr_dm%type;

temp_swjg_dm hx_fp.FP_WLFP_KJXX_CYH10.swjg_dm%type;

temp_sjgsdq hx_fp.FP_WLFP_KJXX_CYH10.sjgsdq%type ;

BEGIN

temp_djxh := 0;

j:=0;

open cur_wlfp_yjj;

loop

exit when cur_wlfp_yjj%notfound;

fetch cur_wlfp_yjj bulk collect

into r limit 1000;

j:=j+1;

for i in 1 .. r.count loop

update hx_fp.FP_WLFP_KJXX_CYH10 b set b.SFYYJ='X' where b.UUID=r(i).uuid;

if(i=1 and j=1) then

temp_djxh:=r(i).djxh;

temp_fpzl_dm:=r(i).fpzl_dm;

temp_fp_dm:=r(i).fp_dm;

temp_fs:=r(i).fs;

temp_fpqshm:=r(i).fpqshm;

temp_fpzzhm:=r(i).fpzzhm;

temp_kpqsrq:=r(i).kpqsrq;

temp_kpjzrq:=r(i).kpjzrq;

temp_kpje:=r(i).kpje;

temp_se:=r(i).se;

temp_lrr_dm:=r(i).lrr_dm;

temp_swjg_dm:=r(i).swjg_dm;

temp_sjgsdq:=r(i).sjgsdq;

else if(r(i).djxh=temp_djxh and temp_fpzl_dm=r(i).fpzl_dm and temp_fp_dm=r(i).fp_dm and

temp_fpzzhm+1=r(i).fpqshm) then

temp_fpzzhm :=r(i).fpzzhm;

temp_fs :=temp_fs+r(i).fs;

temp_kpjzrq:=r(i).kpjzrq;

temp_kpje :=temp_kpje+r(i).kpje;

temp_se:= temp_se+r(i).se;

temp_lrr_dm:=r(i).lrr_dm;

temp_swjg_dm:=r(i).swjg_dm;

temp_sjgsdq:=r(i).sjgsdq;

else

insert into hx_fp.FP_WLFP_KJXX_CYH10 (UUID, DJXH, FPZL_DM, FP_DM, FS, FPQSHM, FPZZHM,

FPKJQK_DM, KPQSRQ, KPJZRQ, KPJE, SE, SWJG_DM, LRR_DM, LRRQ, XGR_DM, XGRQ, SJGSDQ, SJGSRQ,

SFYYJ, KPSCSJ)

values (sys_guid(), temp_djxh, temp_fpzl_dm, temp_fp_dm, temp_fs, temp_fpqshm, temp_fpzzhm,

'10', temp_kpqsrq, temp_kpjzrq, temp_kpje,temp_se, temp_swjg_dm,

'88888888888',sysdate,null,null,temp_sjgsdq, sysdate+31, 'N', temp_kpjzrq);

commit;

temp_djxh:=r(i).djxh;

temp_fpzl_dm:=r(i).fpzl_dm;

temp_fp_dm:=r(i).fp_dm;

temp_fs:=r(i).fs;

temp_fpqshm:=r(i).fpqshm;

temp_fpzzhm:=r(i).fpzzhm;

temp_kpqsrq:=r(i).kpqsrq;

temp_kpjzrq:=r(i).kpjzrq;

temp_kpje:=r(i).kpje;

temp_se:=r(i).se;

temp_lrr_dm:=r(i).lrr_dm;

temp_swjg_dm:=r(i).swjg_dm;

temp_sjgsdq:=r(i).sjgsdq;

end if;

end loop;

insert into hx_fp.FP_WLFP_KJXX_CYH10 (UUID, DJXH, FPZL_DM, FP_DM, FS, FPQSHM, FPZZHM,

FPKJQK_DM, KPQSRQ, KPJZRQ, KPJE, SE, SWJG_DM, LRR_DM, LRRQ, XGR_DM, XGRQ, SJGSDQ, SJGSRQ,

SFYYJ, KPSCSJ)

values (sys_guid(), temp_djxh, temp_fpzl_dm, temp_fp_dm, temp_fs, temp_fpqshm, temp_fpzzhm,

'10', temp_kpqsrq, temp_kpjzrq, temp_kpje,temp_se,temp_swjg_dm,

'88888888888',sysdate,null,null,temp_sjgsdq, sysdate+31, 'N', temp_kpjzrq);

commit;

close cur_wlfp_yjj;

end;

看了老半天，头都晕了才看明白，原来是按照条件发票收尾相连，汇总一个结果。

想了半天，最好的办法还是先排序，然后放到集合中去处理，先累加相同的，再删掉前一个已经累加过的票证。最后在集合中处理完了一次性提交处理完的集合结果集。按照该想法处理数据，10W条记录仅仅花费8秒，400W条数据2分半，效果不错。

修改后：

CREATE OR REPLACE PROCEDURE p_fp_wlfp_yjj_30 is

cursor cur_wlfp_yjj is with wp as(

select a.uuid,

a.djxh,

a.fpzl_dm,

a.fp_dm,

a.fs,

a.fpqshm,

a.fpzzhm,

a.fpkjqk_dm,

a.kpqsrq,

a.kpjzrq,

a.kpje,

a.se,

a.lrr_dm,

a.swjg_dm,

a.sjgsdq

from hx_fp.FP_WLFP_KJXX_CYH30 a

where a.FPKJQK_DM = '10'

and a.sfyyj = 'N'

-- and a.swjg_dm = ?

--and a.sjgsdq like ?

)

select wp.*

from wp

order by wp.djxh, wp.fpzl_dm, wp.fp_dm, wp.fpqshm;

type resultset is table of cur_wlfp_yjj%rowtype;

r resultset;

--外层大循环的次数

j pls_integer;

x pls_integer;

BEGIN

open cur_wlfp_yjj;

loop

fetch cur_wlfp_yjj bulk collect

into r limit 100000;

forall i in 1 .. r.count

update hx_fp.FP_WLFP_KJXX_CYH30 b

set b.SFYYJ = 'X'

where b.UUID = r(i).uuid;

r.extend;

x := r.count;

for i in 1 .. x loop

j := r.next(i);

exit when j is null;

if (r(i).djxh = r(j).djxh and r(i).fpzl_dm = r(j).fpzl_dm and r(i)

.fp_dm = r(j).fp_dm and r(i).fpzzhm + 1 = r(j).fpqshm) then

r(j).fpqshm := r(i).fpqshm;

r(j).fs := r(i).fs + r(j).fs;

r(j).kpje := r(j).kpje + r(i).kpje;

r(j).se := r(j).se + r(i).se;

r.delete(i);

end if;

end loop;

exit when cur_wlfp_yjj%notfound;

close cur_wlfp_yjj;

end loop;

x := r.last;

r.delete(x);

dbms_output.put_line(r.limit||'..'||r.count);

for i in r.first .. r.last loop

if r.exists(i) then

insert into hx_fp.FP_WLFP_KJXX_CYH30

(UUID,

DJXH,

FPZL_DM,

FP_DM,

FS,

FPQSHM,

FPZZHM,

FPKJQK_DM,

KPQSRQ,

KPJZRQ,

KPJE,

SE,

SWJG_DM,

LRR_DM,

LRRQ,

XGR_DM,

XGRQ,

SJGSDQ,

SJGSRQ,

SFYYJ,

KPSCSJ)

values

(sys_guid(),

r(i).djxh,

r(i).fpzl_dm,

r(i).fp_dm,

r(i).fs,

r(i).fpqshm,

r(i).fpzzhm,

'10',

r(i).kpqsrq,

r(i).kpjzrq,

r(i).kpje,

r(i).se,

r(i).swjg_dm,

'88888888888',

sysdate,

null,

r(i).sjgsdq,

sysdate + 31,

'N',

r(i).kpjzrq);

commit;

end if;

end loop;

end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23371754/viewspace-757244/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23371754/viewspace-757244/