批量加载性能案例

客户在大量加载数据是遇到性能问题,检查后发现客户采用的是单条插入单条提交这种最缓慢的方式,为了给客户说明优化效果,现场做了几个代码。
最简单的优化方式莫过于减少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

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

This entry was posted in ORACLE and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *