批量加载性能

在大量加载数据是遇到性能问题，检查后发现客户采用的是单条插入单条提交这种最缓慢的方式，为了给客户说明优化效果，现场做了几个代码。

最简单的优化方式莫过于减少COMMIT频度，而最优化的方式是采用批量插入的方式，简单的测试代码如下：

SQL> CREATE TABLE T_INSERT (ID NUMBER, NAME VARCHAR2(30));
Table created.
SQL> SET TIMING ON
SQL> BEGIN
2 FOR I IN 1..100000 LOOP
3 INSERT INTO T_INSERT VALUES (I, 'A' || I);
4 COMMIT;
5 END LOOP;
6 END;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.22

 

SQL> BEGIN
2 FOR I IN 1..100000 LOOP
3 INSERT INTO T_INSERT VALUES (I, 'A' || I);
4 COMMIT;
5 END LOOP;
6 END;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.51

 

SQL> BEGIN
2 FOR I IN 1..100000 LOOP
3 INSERT INTO T_INSERT VALUES (I, 'A' || I);
4 IF MOD(I, 1000) = 0 THEN
5 COMMIT;
6 END IF;
7 END LOOP;
8 COMMIT;
9 END;
10 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.01

 

SQL> BEGIN
2 FOR I IN 1..100000 LOOP
3 INSERT INTO T_INSERT VALUES (I, 'A' || I);
4 IF MOD(I, 1000) = 0 THEN
5 COMMIT;
6 END IF;
7 END LOOP;
8 COMMIT;
9 END;
10 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.64

 

SQL> DECLARE
2 TYPE T_NUM IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 TYPE T_VAR IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
4 V_NUM T_NUM;
5 V_VAR T_VAR;
6 BEGIN
7 FOR I IN 1..100000 LOOP
8 V_NUM(I) := I;
9 V_VAR(I) := 'A' || I;
10 END LOOP;
11 FORALL I IN 1..100000
12 INSERT INTO T_INSERT VALUES (V_NUM(I), V_VAR(I));
13 COMMIT;
14 END;
15 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.37

 

SQL> DECLARE
2 TYPE T_NUM IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 TYPE T_VAR IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
4 V_NUM T_NUM;
5 V_VAR T_VAR;
6 BEGIN
7 FOR I IN 1..100000 LOOP
8 V_NUM(I) := I;
9 V_VAR(I) := 'A' || I;
10 END LOOP;
11 FORALL I IN 1..100000
12 INSERT INTO T_INSERT VALUES (V_NUM(I), V_VAR(I));
13 COMMIT;
14 END;
15 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.50

 

这个例子明确说明了单条提交、批量提交以及数值插入的性能差异，很多时候只是口头上的描述，客户不会有太深的印象，而如果通过这种例子来展示性能的差别，结果一目了然，比再多的描述都管用得多。