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

2008-12-10 09:32:19 992 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

PostgreSQL 中如何处理数据的批量更新和事务日志管理？数据的批量更新和事务日志管理是 PostgreSQL 中两个重要的方面，它们相互关联，共同保证了数据库的正常运行和数据的可靠性。在实际应用中，我们需要根据具体的业务需求和数据特点，选择合适的方法和策略来进行处理。同时，不断地优化和调整，以适应不断变化的业务环境和数据量的增长。希望通过以上的介绍，能够让您对 PostgreSQL 中的数据批量更新和事务日志管理有更深入的了解和认识。在数据库的世界里，每一个操作都需要精心策划和谨慎执行，只有这样，才能让我们的数据“舞蹈”得更加优美和准确。阅读详情

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

...全文

Python实现支付宝转账接口由于工作需要使用python开发一个自动转账接口，记录一下开发过程。首先需要在蚂蚁金服上申请开通开发者账户，有了开发者账户就可以使用沙箱进行开发了。在开发之前我们需要在沙箱应用中填写密钥，密钥的获取可以使用阿里提供的工具包自动生成。前期准备工作完成了，接下来是编写代码部分。主要用到了python-alipay-sdk库，使用pip安装即可，如果安装的过程中遇到问题推荐使用Anaconda... 阅读详情

月球探测器

Oracle 17,133 成员 55,258 内容

+ 关注 + 加入社区

切换为时间正序

sleepzzzzz 2009-02-10

打赏
举报

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

顾问Peng 2009-02-09

打赏
举报

1000条1000条的插入吧~

hebo2005 2008-12-14

打赏
举报

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

月球探测器 2008-12-14

打赏
举报

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

月球探测器 2008-12-14

打赏
举报

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

hebo2005 2008-12-14

打赏
举报

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

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

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

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

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

月球探测器 2008-12-14

打赏
举报

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

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

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

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

hebo2005 2008-12-11

打赏
举报

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

月球探测器 2008-12-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;

月球探测器 2008-12-10

打赏
举报

[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

月球探测器 2008-12-10

打赏
举报

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

hebo2005 2008-12-10

打赏
举报

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

hebo2005 2008-12-10

打赏
举报

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

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

hebo2005 2008-12-10

打赏
举报

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

月球探测器 2008-12-10

打赏
举报

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

月球探测器 2008-12-10

打赏
举报

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

hebo2005 2008-12-10

打赏
举报

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

月球探测器 2008-12-10

打赏
举报

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

月球探测器 2008-12-10

打赏
举报

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

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

相关推荐