使用.NET批量插入Oracle

使用.NET对Oracle进行批量插入的最快方法是什么？我需要使用.NET将大约160K记录传输到Oracle.目前,我正在使用insert语句并执行160K次.完成大约需要25分钟.源数据存储在DataTable中,作为从另一个数据库(MySQL)查询的结果,

有没有更好的方法来做到这一点？

编辑:我目前正在使用System.Data.OracleClient,但愿意接受使用其他提供商(ODP.NET,DevArt等)的解决方案.

1> Damian..：

---

我在ODP.NET中使用Array Binding在15秒左右的时间内加载了50,000条记录

它通过重复调用您指定的存储过程(并且您可以在其中执行更新/插入/删除)来工作,但它会将多个参数值从.NET传递到数据库.

您可以为每个参数指定一个值数组,而不是为存储过程的每个参数指定单个值.

Oracle一次性将参数数组从.NET传递到数据库,然后使用您指定的参数值重复调用您指定的存储过程.

http://www.oracle.com/technetwork/issue-archive/2009/09-sep/o59odpnet-085168.html

/达米安

---

该链接似乎不再起作用.我在oracle论坛中搜索数组绑定,它具有相同的非功能链接.有新位置吗？

---

我已经更新了帖子,以包含一个来自Oracle示例的工作链接.

2> 小智..：

---

我最近发现了一个专门用于批量插入(ODP.NET)的类.Oracle.DataAccess.Client.OracleBulkCopy!它需要一个数据表作为参数,然后你调用WriteTOServer方法......它非常快速有效,祝你好运!

3> 小智..：

---

Rob Stevenson-Legget的解决方案很慢,因为他没有绑定他的值,但他使用了string.Format().

当您要求Oracle执行sql语句时,它首先计算此语句的has值.之后,它会在哈希表中查看它是否已经知道此语句.如果它已经知道它的语句,它可以从这个哈希表中检索它的执行路径,并且非常快地执行这个语句,因为Oracle之前已经执行过这个语句.这称为库缓存,如果不绑定sql语句,它将无法正常工作.

例如,不要这样做:

int n;

    for (n = 0; n < 100000; n ++)
    {
      mycommand.CommandText = String.Format("INSERT INTO [MyTable] ([MyId]) VALUES({0})", n + 1);
      mycommand.ExecuteNonQuery();
    }

但是:

      OracleParameter myparam = new OracleParameter();
      int n;

      mycommand.CommandText = "INSERT INTO [MyTable] ([MyId]) VALUES(?)";
      mycommand.Parameters.Add(myparam);

      for (n = 0; n < 100000; n ++)
      {
        myparam.Value = n + 1;
        mycommand.ExecuteNonQuery();
      }

不使用参数也可以导致sql注入.

4> Berend Engel..：

---

SQL Server的SQLBulkCopy速度非常快.不幸的是,我发现OracleBulkCopy要慢得多.还有问题:

如果您打算使用OracleBulkCopy,则必须非常确定输入数据是否干净.如果发生主键冲突,则会引发ORA-26026并且它似乎无法恢复.尝试重建索引没有帮助,并且表上的任何后续插入都失败,也是正常插入.

即使数据干净,我发现OracleBulkCopy有时会卡在WriteToServer中.问题似乎取决于批量大小.在我的测试数据中,当我重复时,问题会发生在我测试中的完全相同的位置.使用更大或更小的批量大小,问题不会发生.我发现在较大的批量大小上速度更不规则,这指出了与内存管理相关的问题.

实际上,如果要填充具有小记录但很多行的表,则System.Data.OracleClient.OracleDataAdapter比OracleBulkCopy更快.您需要调整批处理大小,OracleDataAdapter的最佳BatchSize小于OracleBulkCopy.

我在带有x86可执行文件和32位ODP.Net客户端2.112.1.0的Windows 7计算机上运行测试..OracleDataAdapter是System.Data.OracleClient 2.0.0.0的一部分.我的测试集大约是600,000行,记录大小是max.102个字节(平均大小43个字符).数据源是一个25 MB的文本文件,逐行读取为流.

在我的测试中,我将输入数据表构建为固定的表大小,然后使用OracleBulkCopy或OracleDataAdapter将数据块复制到服务器.我在OracleBulkCopy中将BatchSize保留为0(以便将当前表内容复制为一个批处理)并将其设置为OracleDataAdapter中的表大小(同样应在内部创建单个批处理).最佳成绩:

OracleBulkCopy:表大小= 500,总持续时间4'22"

OracleDataAdapter:表大小= 100,总持续时间3'03"

为了比较:

SqlBulkCopy:表大小= 1000,总持续时间0'15"

SqlDataAdapter:表大小= 1000,总持续时间8'05"

相同的客户端机器,测试服务器是SQL Server 2008 R2.对于SQL Server,批量复制显然是最好的方法.它不仅总体上最快,而且服务器负载也低于使用数据适配器时的服务器负载.遗憾的是,OracleBulkCopy没有提供相同的体验 - BulkCopy API比DataAdapter更容易使用.