如何提高存储过程批量插入及更新数据的性能

[月球探测器]

有一个存储过程专门用来从另外一个数据结果表(tab_result)中提取记录并插入到记录详细表(tab_detail)中,如果原来的记录已存在tab_detail中,则更新该记录.
举例说明:
数据结果表tab_result记录如下:
create tab_result
( sn number,--自动增长的数值
callid number, -- callid可能有重复的记录
tdata number(12),
xdata varchar2(10)
);
alter table tab_result add index idx_result on (sn);

sn callid tdata xdata
-----------------------------
1 101 223 xxxs
2 232 232 sdfd
3 351 323 232x
4 101 343 3dfdf

记录详细表(tab_detail)结构如下:
create table tab_detail
( callid number, --来源于tab_result中的callid
sdata1 number(12),-- sdata1 = tab_result.tdata+tab_result.callid
sdata2 number(12),--sdata2 = tab_result.tdata * 100 +tab_result.callid
xxdata varchar2(10) --xxdata = tab_result.xdata+tab_result.tdata
);
alter table tab_detail add index idx_callid on tab_detail(callid);

现在tab_result中有1000W条记录,需要根据一些变换后插入或更新到tab_detail中去.
目前我是用一个大的循环来实现的,循环里面用游标来获取每一行数据再判断tab_detail中时候已存在callid的记录,不存在,则直接插入;已存在则更新到该条callid记录中.但是执行的效率太低,每秒生成100条左右的tab_detail记录.大家帮忙看看有没有好的办法可以优化一下,提高Insert和Update的性能??

[月球探测器]

部分代码如下,请大家帮忙看看如何提高数据的处理性能:
do loop
step := 5000;
vmax := 0;
v_sql := 'select sn,callid,tdata,xdata from tab_result where
sn > '||vmax||' and sn <= '||vmax||' + 6000';
get_cursor is for v_sql;
open get_cursor;
fetch into v_sn,v_callid,v_tdata,v_xdata;
exit get_cursor%nofound;
begin
v_ss := 'select callid from tab_detail where callid='||v_callid;
execute immediate v_ss into v_num;
begin
if v_num is not null then
v_sql2 := ' update tab_detail set sdata1 = v_tdata+v_callid,
sdata2 = v_data*100 + '||v_callid||', xdata= '||v_xdata||'+ '||v_tdata||' where callid = '||v_callid;
end if;
exception
if nodatafound
v_sql2 := 'insert into tab_detail(callid,sdata1,sdata2,xxdata) select '||v_callid||',b.tdata+b.callid ,b.tdata * 100 + b.callid ,b.xdata+b.tdata from tab_result b';
end;
execute immediate v_sql2;
if v_totalnum >= 6000 then
commit;
v_totalnum := 0;
vmax := vmax + step;
end if;
end;
end loop

[月球探测器]

哦,那个Insert语句有点错误,正确的应该如下:
v_sql2 := 'insert into tab_detail(callid,sdata1,sdata2,xxdata) select '||v_callid||',b.tdata+b.callid ,b.tdata * 100 + b.callid ,b.xdata+b.tdata from tab_result b where b.sn ='||v_sn;

[hebo2005]

用merge会快很多，能够不用游标的就不要用游标

[月球探测器]

我测试过,用merge时还没有分别用insert和update速度快,特别是数据量大的时候.

[月球探测器]

不用游标,那又怎么取数据行的记录呢?

[hebo2005]

merge into tab_detail a
using tab_result b
on a.callid=b.callid
WHEN MATCHED THEN
         UPDATE
            SET a.sdata1=b.tdata+b.cellid
 WHEN NOT MATCHED THEN
         INSERT
         VALUES (值)

[hebo2005]

[Quote=引用 4 楼 joseph10086 的回复:]
我测试过,用merge时还没有分别用insert和update速度快,特别是数据量大的时候.
[/Quote]
怎么会呢，你不用MERGE每次要多个判断语句

我们这有个存储过程，原来用游标实现的，要跑近4小时，我用MERGE实现只要1分钟不到

[hebo2005]

估计你还是用游标来写MERGE的吧，用游标性能是会下降很多的

[月球探测器]

[Quote=引用 8 楼 hebo2005 的回复:]
估计你还是用游标来写MERGE的吧，用游标性能是会下降很多的
[/Quote]
对,我是用游标来写merge的.就是先从tab_result中获取每行数据,组装后,再用merge来Insert或update的.确实速度不快.
如果不用游标,我怎么才能从tab_result中获取每行数据呢?因为tab_result表中的数据是实时增加的.

[月球探测器]

[Quote=引用 8 楼 hebo2005 的回复:]
估计你还是用游标来写MERGE的吧，用游标性能是会下降很多的
[/Quote]
我是用游标取的,没办法主要是其中的xdata的值需要根据callid的值不同而组成不同的值.我前面的代码有的错误,丢了下面彩色部分的代码;
do loop
step := 5000;
vmax := 0;
v_sql := 'select sn,callid,tdata,xdata from tab_result where
sn > '||vmax||' and sn <= '||vmax||' + 6000';
get_cursor is for v_sql;
open get_cursor;
fetch into v_sn,v_callid,v_tdata,v_xdata;
exit get_cursor%nofound;
begin
if v_callid = 101 then
xdata := xdata + 100;
elsif v_callid = 232 then
xdata := xdata + 2500;
elsif v_callid = 351 then
xdata := xdata + 3600;
else
return;
end if;

v_ss := 'select callid from tab_detail where callid='||v_callid;
execute immediate v_ss into v_num;
begin
if v_num is not null then
v_sql2 := ' update tab_detail set sdata1 = v_tdata+v_callid,
sdata2 = v_data*100 + '||v_callid||', xdata= '||v_xdata||'+ '||v_tdata||' where callid = '||v_callid;
end if;
exception
if nodatafound
v_sql2 := 'insert into tab_detail(callid,sdata1,sdata2,xxdata) select '||v_callid||',b.tdata+b.callid ,b.tdata * 100 + b.callid ,b.xdata+b.tdata from tab_result b where b.sn ='||v_sn;

end;
execute immediate v_sql2;
if v_totalnum >= 6000 then
commit;
v_totalnum := 0;
vmax := vmax + step;
end if;
end;
end loop

[月球探测器]

我今天改了下代码,用块来读取,不用游标了,另外也改用merge来进行insert和update.虽然速度很快,但是记录都是重复记录,不知道哪里出错了.请帮忙看看,是哪里的问题.
declare
type my_record is table of tab_result%rowtype;
v_callid number;

begin
v_sql := 'select sn,callid,tdata,xdata from tab_result;
execute immedate v_sql bulk collect into my_record;

FOR i IN my_record.FIRST .. my_record.LAST LOOP
v_callid:= my_record(i).callid;
ls_sql := 'merge into tab_detail a using tab_result b on (a.callid = '||v_callid||')
when MATCHED THEN
UPDATE
SET a.sdata1=b.tdata+b.cellid
WHEN NOT MATCHED THEN
INSERT
VALUES (值)';
execute immediate ls_sql;
commit;
End LOOP;end;

[hebo2005]

这个简单啊
case when callid=101 then xdata + 100

[月球探测器]

[Quote=引用 7 楼 hebo2005 的回复:]
引用 4 楼 joseph10086 的回复:

我测试过,用merge时还没有分别用insert和update速度快,特别是数据量大的时候.

怎么会呢，你不用MERGE每次要多个判断语句

我们这有个存储过程，原来用游标实现的，要跑近4小时，我用MERGE实现只要1分钟不到
[/Quote]
你们这个东东是怎么弄的?怎么可以改为不用游标取数据呢?

[hebo2005]

[Quote=引用 15 楼 joseph10086 的回复:]
引用 7 楼 hebo2005 的回复:
引用 4 楼 joseph10086 的回复:

我测试过,用merge时还没有分别用insert和update速度快,特别是数据量大的时候.

怎么会呢，你不用MERGE每次要多个判断语句

我们这有个存储过程，原来用游标实现的，要跑近4小时，我用MERGE实现只要1分钟不到

你们这个东东是怎么弄的?怎么可以改为不用游标取数据呢?
[/Quote]
主要是原来设计的人的逻辑有问题，他用游标实现的功能，我用一句SQL就能跑出来，虽然SQL写的比较复杂点，单条SQL差不多就要跑近一分钟，实际最终的结果大概也就插入2000条纪录不到，但他原来的写法，每条纪录估计要UPDATE至少几百次，这个量就比较恐怖了
算下来，一个存储过程要几百万次写
而我们的服务器是RAID5，写慢
所以我用SQL写出来，虽然SQL跑得慢，却是一次性写进去

[月球探测器]

呵呵,看来还是算法问题.但我的这个问题比这个复杂些,不同的v_callid对应的Insert或Update SQL语句不一样.而且要同时可以检测数据库中时候已存在callid为这个值的记录(已存在则直接更新相关字段,不存在则直接插入).所以说不用游标好像是不能实现了.我现在在考虑采用外部程序辅助实现:采用Java的多线程方式来调用这个存储过程,Java的多线程可以并发操作,这样的操作应该是比较快的.目前还在考虑中.

[月球探测器]

哎,改成上面的方式使用merge优化效果不明显啊,甚至觉得比原来还慢.楼上的XD,能否给个使用Merge方式的批量操作数据的例子?

[hebo2005]

你上面的方式是用隐性游标

[顾问Peng]

1000条1000条的插入吧~

[sleepzzzzz]

看来设计很重要啊,不要为了写代码而只顾实能现就行,不然最终烦死的不是你自己就是你的后来者.