ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 批量加载性能案例

批量加载性能案例

原创 Linux操作系统 作者:yangtingkun 时间:2011-12-22 23:49:15 0 删除 编辑

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

 

 

最简单的优化方式莫过于减少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

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

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-713816/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10386671