Oracle批处理insert性能比较

github地址

建表语句

create table TESTBATCH
(
  userid      NUMBER not null,
  username    VARCHAR2(4000),
  age         NUMBER,
  phone       NUMBER,
  currencyone VARCHAR2(4000),
  countone    NUMBER,
  currencytwo VARCHAR2(4000),
  counttwo    NUMBER,
  createtime  DATE,
  updatetime  DATE
);
alter table TESTBATCH
  add primary key (USERID);

插入测试数据

• 准备了20万数据，只测试了1万数据的批量insert

--1、声明ylm_manager
create or replace procedure ylm_manager(startIndex in number,
                                        endIndex   in number) as
begin
  DBMS_OUTPUT.ENABLE(buffer_size => null);
  for p_userid in startIndex .. endIndex loop
    insert into testbatch
      (userid, username, age, phone,currencyone,countone,currencytwo,counttwo,createtime,updatetime)
    values
      (p_userid, '四年级学生森下下士'||p_userid, 18, 110120，'科威特第纳尔',987654321,'津巴布韦',123456789,sysdate,sysdate);
    DBMS_OUTPUT.PUT_LINE('userid is ' || p_userid);
  end loop;
end ylm_manager;
--2、调用
call  ylm_manager(100000,200000);

实体类TestBatch.java

public class TestBatch {
    private Integer userId;
    private String userName;
    private Integer age;
    private Long phone;
    private String currencyOne;
    private Long countOne;
    private String currencyTwo;
    private Long countTwo;
    private String createTime;
    private String updateTime;
    
    ...get and set...

mapper

<insert id="batchInsertOne" parameterType="com.diy.sigmund.mybatisoracle.entity.TestBatch">
    <foreach collection="list" item="item" index="index" open="begin" close="end;">
      INSERT INTO testbatch
      (userid, username, age, phone,currencyone,countone,currencytwo,counttwo,createtime,updatetime)
      VALUES
      (#{item.userId,jdbcType=NUMERIC}, #{item.userName,jdbcType=VARCHAR},
      #{item.age,jdbcType=NUMERIC},
      #{item.phone,jdbcType=NUMERIC},
      #{item.currencyOne,jdbcType=VARCHAR},
      #{item.countOne,jdbcType=NUMERIC},
      #{item.currencyTwo,jdbcType=VARCHAR},
      #{item.countTwo,jdbcType=NUMERIC},
      to_date(#{item.createTime,jdbcType=VARCHAR},'YYYY-MM-DD HH24:MI:SS'),
      sysdate);
    </foreach>
  </insert>

  <insert id="batchInsertTwo" parameterType="com.diy.sigmund.mybatisoracle.entity.TestBatch">
    begin
    INSERT INTO testbatch
    (userid, username, age, phone,currencyone,countone,currencytwo,counttwo,createtime,updatetime)
    <foreach collection="list" item="item" separator="union all">
      SELECT #{item.userId,jdbcType=NUMERIC}, #{item.userName,jdbcType=VARCHAR},
      #{item.age,jdbcType=NUMERIC},
      #{item.phone,jdbcType=NUMERIC},
      #{item.currencyOne,jdbcType=VARCHAR},
      #{item.countOne,jdbcType=NUMERIC},
      #{item.currencyTwo,jdbcType=VARCHAR},
      #{item.countTwo,jdbcType=NUMERIC},
      to_date(#{item.createTime,jdbcType=VARCHAR},'YYYY-MM-DD HH24:MI:SS'),
      sysdate
      FROM dual
    </foreach>
    ;end;
  </insert>

• 本地单元测试，重启了电脑，8g内存，启动了虚拟机，启动了oracle

耗时batchInsertOne升序

批量处理条数	时间ms	时间ms	时间ms	时间ms	时间ms	时间ms	时间ms	时间ms	时间ms	时间ms	平均时间
400	2634	1337	1279	808	788	824	784	800	1315	772	1134.1
600	2417	1288	1399	818	792	851	1632	767	768	827	1155.9
700	2474	1344	2165	972	822	800	789	845	770	755	1173.6
500	2506	1312	1842	794	792	785	804	1319	856	739	1174.9
300	2277	2453	1406	807	821	801	836	795	990	734	1192
85	3669	1744	876	944	901	898	1197	881	869	1048	1302.7
800	3308	2086	1172	1098	802	787	1035	774	787	1186	1303.5
200	4154	1497	1464	821	973	820	841	804	1350	787	1351.1
70	3101	1732	1523	918	938	995	895	902	1918	897	1381.9
100	4028	2277	1695	1026	856	895	919	957	797	880	1433
50	4562	1755	974	1073	963	963	985	1188	939	1078	1448
900	3107	1929	1098	984	804	794	799	773	1103	3854	1524.5
30	4641	2666	1077	1059	1198	1086	1035	1034	1291	1146	1623.3
10	6766	5364	4588	1498	1621	1476	1702	2047	1440	1696	2819.8
5	5798	6214	8148	2618	2348	2101	2207	2094	1972	7097	4059.7

耗时batchInsertTwo升序

批量处理条数	时间ms	时间ms	时间ms	时间ms	时间ms	时间ms	时间ms	时间ms	时间ms	时间ms	平均时间
900	487	459	468	454	534	489	478	471	471	476	478.7
300	526	469	607	505	486	488	495	480	480	497	503.3
200	572	507	498	874	499	508	502	506	505	513	548.4
800	481	465	608	543	468	481	475	729	465	859	557.4
600	524	749	461	466	504	1274	471	461	631	453	599.4
100	962	556	554	550	559	725	580	563	551	574	617.4
85	586	569	576	574	576	592	1022	573	574	576	621.8
700	616	458	470	1688	824	462	470	465	485	437	637.5
50	729	653	665	721	657	653	659	659	647	640	668.3
500	488	467	474	568	491	1008	1740	457	472	594	675.9
400	503	1000	822	475	486	554	479	472	1894	461	714.6
70	671	671	595	834	600	595	614	2447	595	676	829.8
30	815	761	866	731	752	756	718	751	1532	742	842.4
10	1803	1196	1176	1150	1186	1142	1147	1147	1150	1184	1228.1
5	2161	2215	1762	1724	1928	1748	1778	1732	1754	1751	1855.3

结论

1. 使用union all的方式要明显快于普通insert into的方式

2. 900的批量处理条数最快