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

Question

有一个存储过程专门用来从另外一个数据结果表(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的性能??

Answer 1

部分代码如下,请大家帮忙看看如何提高数据的处理性能:
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

Answer 2

哦,那个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; 

Answer 3

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

Answer 4

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

Answer 5

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 (值)

Answer 6

怎么会呢，你不用MERGE每次要多个判断语句我们这有个存储过程，原来用游标实现的，要跑近4小时，我用MERGE实现只要1分钟不到

Answer 7

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

Answer 8

对,我是用游标来写merge的.就是先从tab_result中获取每行数据,组装后,再用merge来Insert或update的.确实速度不快.
如果不用游标,我怎么才能从tab_result中获取每行数据呢?因为tab_result表中的数据是实时增加的.

Answer 9

我是用游标取的,没办法主要是其中的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

Answer 10

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

Answer 11

我今天改了下代码,用块来读取,不用游标了,另外也改用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;

Answer 12

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

Answer 13

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

Answer 14

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