Oracle批量导入100万条数据不到1秒

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了Oracle批量导入100万条数据不到1秒，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

.Net程序中可以通过ODP调用特性，对Oracle数据库进行操作，今天来讲一下数据批量插入的功能，所用技术不高不深，相信很多朋友都接触过，小弟班门弄斧了，呵呵。这篇文章是上篇文章的续集，因为上一次试验的征集结果没有突破4秒的方法，所以这次继续挑战与挖掘新方法，虽然是Oracle，但仍具有一定收藏意义。

上一次文章中提及的试验： SqlServer批量导入C#100万条数据仅4秒附源码http://www.jb51.cc/article/p-faxczxmd-bpr.html

这个试验是针对sql SERVER数据库的，宿主环境也是.Net，有兴趣的朋友可以将这两个试验对比一下，为日后工作批量导数提供支持。

另外，一些朋友对上次试验环境有些异议，认为应该对数据库和服务器做优化或设置，以体现试验最终的时间结果。这个固然会影响试验的时间结果，但考虑到在试验环境中，对数据库优化的标准与优化程度不便统一与定量，试验结果也不易说明其影响源，所以这次试验依然以标准数据库建库后的配置为主，试验所在服务器硬件环境与上次试验保持一致。实验目的在于挖掘、对比宿主程序中的数据批量操作方法。

有新方法提升性能时间指标的朋友，欢迎互相切磋，互相提高，嘴上功夫就免了。。。

好了正文开始。

● 普通肉垫式

什么叫批量插入呢，就是一次性插入一批数据，我们可以把这批数据理解为一个大的数组，而这些全部只通过一个sql来实现，而在传统方式下，需要调用很多次的sql才可以完成，这就是著名的“数组绑定”的功能。我们先来看一下传统方式下，插入多行记录的操作方式：

代码 
  1.    
  2.    
  3.     
  4.     //
  5.     
  6.     设置一个数据库的连接串， 
  7.     
  8.      
  9.     
  10.      
  11.     
  12.     string
  13.     
  14.      connectStr 
  15.     
  16.     =
  17.     
  18.      
  19.     
  20.     "
  21.     
  22.     User Id=scott;Password=tiger;Data Source=
  23.     
  24.     "
  25.     
  26.     ; OracleConnection conn 
  27.     
  28.     =
  29.     
  30.      
  31.     
  32.     new
  33.     
  34.      OracleConnection(connectStr); OracleCommand command 
  35.     
  36.     =
  37.     
  38.      
  39.     
  40.     new
  41.     
  42.      OracleCommand(); command.Connection 
  43.     
  44.     =
  45.     
  46.      conn; conn.Open(); Stopwatch sw 
  47.     
  48.     =
  49.     
  50.      
  51.     
  52.     new
  53.     
  54.      Stopwatch(); sw.Start(); 
  55.     
  56.     通过循环写入大量的数据，这种方法显然是肉垫 
  57.     
  58.     for
  59.     
  60.      (
  61.     
  62.     int
  63.     
  64.      i 
  65.     
  66.     =
  67.     
  68.      
  69.     
  70.     0
  71.     
  72.     ; i 
  73.     
  74.     <
  75.     
  76.      recc; i
  77.     
  78.     ++
  79.     
  80.     ) { 
  81.     
  82.     string
  83.     
  84.      sql 
  85.     
  86.     =
  87.     
  88.      
  89.     
  90.     insert into dept values(
  91.     
  92.     "
  93.     
  94.      
  95.     
  96.     +
  97.     
  98.      i.ToString() 
  99.     
  100.     +
  101.     
  102.      
  103.     
  104.     ,
  105.     
  106.     )
  107.     
  108.     "
  109.     
  110.     ; command.CommandText 
  111.     
  112.     =
  113.     
  114.      sql; command.ExecuteNonQuery(); } sw.Stop(); System.Diagnostics.Debug.WriteLine(
  115.     
  116.     普通插入:
  117.     
  118.     "
  119.     
  120.      
  121.     
  122.     +
  123.     
  124.      recc.ToString() 
  125.     
  126.     +
  127.     
  128.      
  129.     
  130.     所占时间:
  131.     
  132.     "
  133.     
  134.      
  135.     
  136.     +
  137.     
  138.      sw.ElapsedMilliseconds.ToString()); 
  139.    
  140.

我们先准备好程序，但是先不做时间的测定，因为在后面我们会用多次循环的方式来计算所占用的时间。

● 使用ODP特性

看上面的程序，大家都很熟悉，因为它没有用到任何ODP的特性，而紧接着我们就要来介绍一个神奇的程序了，我们看一下代码，为了更直观，我把所有的注释及说明直接写在代码里：

  1.    
  2.    
    设置一个数据库的连接串 
  3.     
  4.     new
  5.     
  6.      OracleCommand(); command.Connection 
  7.     
  8.     =
  9.     
  10.      conn; 
  11.     
  12.     到此为止，还都是我们熟悉的代码，下面就要开始喽 
  13.     
  14.     这个参数需要指定每次批插入的记录数 
  15.     
  16.      
  17.     
  18.      command.ArrayBindCount 
  19.     
  20.     =
  21.     
  22.      recc; 
  23.     
  24.     在这个命令行中,用到了参数,参数我们很熟悉,但是这个参数在传值的时候 
  25.     
  26.     用到的是数组,而不是单个的值,这就是它独特的地方 
  27.     
  28.      
  29.     
  30.      command.CommandText 
  31.     
  32.     =
  33.     
  34.      
  35.     
  36.     insert into dept values(:deptno,:deptname,:loc)
  37.     
  38.     "
  39.     
  40.     ; conn.Open(); 
  41.     
  42.     下面定义几个数组,分别表示三个字段,数组的长度由参数直接给出 
  43.     
  44.     int
  45.     
  46.     [] deptNo 
  47.     
  48.     =
  49.     
  50.      
  51.     
  52.     new
  53.     
  54.      
  55.     
  56.     int
  57.     
  58.     [recc]; 
  59.     
  60.     string
  61.     
  62.     [] dname 
  63.     
  64.     =
  65.     
  66.      
  67.     
  68.     string
  69.     
  70.     [recc]; 
  71.     
  72.     string
  73.     
  74.     [] loc 
  75.     
  76.     =
  77.     
  78.      
  79.     
  80.      为了传递参数,不可避免的要使用参数,下面会连续定义三个 
  81.     
  82.      从名称可以直接看出每个参数的含义,不在每个解释了 
  83.     
  84.      
  85.     
  86.      OracleParameter deptNoParam 
  87.     
  88.     =
  89.     
  90.      
  91.     
  92.     new
  93.     
  94.      OracleParameter(
  95.     
  96.     deptno
  97.     
  98.     "
  99.     
  100.     ,OracleDbType.Int32); deptNoParam.Direction 
  101.     
  102.     =
  103.     
  104.      ParameterDirection.Input; deptNoParam.Value 
  105.     
  106.     =
  107.     
  108.      deptNo; command.Parameters.Add(deptNoParam); OracleParameter deptNameParam 
  109.     
  110.     =
  111.     
  112.      
  113.     
  114.     deptname
  115.     
  116.     =
  117.     
  118.      ParameterDirection.Input; deptNameParam.Value 
  119.     
  120.     =
  121.     
  122.      dname; command.Parameters.Add(deptNameParam); OracleParameter deptLocParam 
  123.     
  124.     =
  125.     
  126.      
  127.     
  128.     loc
  129.     
  130.     =
  131.     
  132.      ParameterDirection.Input; deptLocParam.Value 
  133.     
  134.     =
  135.     
  136.      loc; command.Parameters.Add(deptLocParam); Stopwatch sw 
  137.     
  138.     =
  139.     
  140.      
  141.     
  142.     在下面的循环中,先把数组定义好,而不是像上面那样直接生成sql 
  143.     
  144.     0
  145.     
  146.     ; i 
  147.     
  148.     <
  149.     
  150.      recc; i
  151.     
  152.     ++
  153.     
  154.     ) { deptNo[i] 
  155.     
  156.     =
  157.     
  158.      i; dname[i] 
  159.     
  160.     =
  161.     
  162.      i.ToString(); loc[i] 
  163.     
  164.     =
  165.     
  166.      i.ToString(); } 
  167.     
  168.     这个调用将把参数数组传进sql,同时写入数据库 
  169.     
  170.      
  171.     
  172.      command.ExecuteNonQuery(); sw.Stop(); System.Diagnostics.Debug.WriteLine(
  173.     
  174.     批量插入:
  175.     
  176.     "
  177.     
  178.      
  179.     
  180.     +
  181.     
  182.     sw.ElapsedMilliseconds.ToString()); 
  183.    
  184.

以上代码略显冗长，但是加上注释后基本也就表达清楚了。

好了，到目前为止，两种方式的插入操作程序已经完成，就剩下对比了。我在主函数处写了一个小函数，循环多次对两个方法进行调用，并且同时记录下时间，对比函数如下：

  1.   
  2.   
  3.    
  4.    for
  5.    
  6.     (
  7.    
  8.    int
  9.    
  10.     i 
  11.    
  12.    =
  13.    
  14.     
  15.    
  16.    1
  17.    
  18.    ; i 
  19.    
  20.    <=
  21.    
  22.     
  23.    
  24.    50
  25.    
  26.    ; i
  27.    
  28.    ++
  29.    
  30.    ) { Truncate(); OrdinaryInsert(i 
  31.    
  32.    *
  33.    
  34.     
  35.    
  36.    1000
  37.    
  38.    ); Truncate(); BatchInsert(i 
  39.    
  40.    *
  41.    
  42.     
  43.    
  44.    1000
  45.    
  46.    ); } 
  47.   
  48.
当数据量达到100万级别时，所用时间依然令人满意，最快一次达到890毫秒，一般为1秒左右。

经过试验，得出一组数据，可以看出两种方式在效率方面惊人的差距（占用时间的单位为毫秒），部分数据如下：

记录数

标准

批处理

1000

1545

29

2000

3514

20

3000

3749

113

4000

5737

40

5000

6820

52

6000

9469

72

7000

10226

69

8000

15280

123

9000

11475

83

10000

14536

121

11000

15705

130

12000

16548

145

13000

18765

125

14000

20393

116

15000

22181

159
因为篇幅原因，不再粘贴全部的数据，但是我们可以看一下由此数据生成的散点图：

其中有些数据有些跳跃，可能和数据库本身有关系，但是大部分数据已经能说明问题了。看了这些数据后，是不是有些心动了？

源程序放了一段时间直接拷贝贴过来了，可能需要调试一下才能跑通，不过不是本质性问题，对了如果要测试别忘记安装Oracle访问组件。

前人的不断努力，后人的不断学习，珍惜时间快速成长，我们要学会站在巨人的肩膀上！！！ http://blog.csdn.net/mrobama

猜你在找的Oracle相关文章

[Oracle]记一次由sequence引发的enq sv-contention等待事件
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...
oracle统计信息的锁定与解锁
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...
【转】RMAN删除过期备份或非过期备份
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...
数据恢复顾问（DRA）
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...
RMAN备份与恢复（一）--认识RMAN
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...
RMAN备份与恢复（二）--RMAN常用操作学习
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...
ORA-00245问题总结
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...
使用RMAN对数据文件进行恢复
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...
Oracle使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问