客户在大量加载数据是遇到性能问题,检查后发现客户采用的是单条插入单条提交这种最缓慢的方式,为了给客户说明优化效果,现场做了几个代码。
最简单的优化方式莫过于减少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 |
这个例子明确说明了单条提交、批量提交以及数值插入的性能差异,很多时候只是口头上的描述,客户不会有太深的印象,而如果通过这种例子来展示性能的差别,结果一目了然,比再多的描述都管用得多。