Bulk Insert to Oracle using .NET

Question

What is the fastest way to do Bulk insert to Oracle using .NET? I need to transfer about 160K records using .NET to Oracle. Currently, I'm using insert statement and execute it 160K times.It takes about 25 minutes to complete. The source data is stored in a DataTable, as a result of query from another database (MySQL),

Is there any better way to do this?

EDIT : I'm currently using System.Data.OracleClient, but willing to accept solutions using another provider (ODP.NET, DevArt, etc..)

Answer 1

You should not commit every insert because committing takes a lot of time.

Which provider do you use to connect your .NET app to the oracle database? Do you use ODP.NET or the Devart provider (aka as corelab provider) or do you use the Microsoft provider for Oracle (System.Data.OracleClient)?

Answer 2

A really fast way to solve this problem is to make a database link from the Oracle database to the MySQL database. You can create database links to non-Oracle databases. After you have created the database link you can retrieve your data from the MySQL database with a ... create table mydata as select * from ... statement. This is called heterogeneous connectivity. This way you don't have to do anything in your .net application to move the data.

Another way is to use ODP.NET. In ODP.NET you can use the OracleBulkCopy-class.

But I don't think that inserting 160k records in an Oracle table with System.Data.OracleClient should take 25 minutes. I think you commit too many times. And do you bind your values to the insert statement with parameters or do you concatenate your values. Binding is much faster.

Answer 3

The solution of Rob Stevenson-Legget is slow because he doesn't bind his values but he uses string.Format( ).

When you ask Oracle to execute a sql statement it starts with calculating the has value of this statement. After that it looks in a hash table whether it already knows this statement. If it already knows it statement it can retrieve its execution path from this hash table and execute this statement really fast because Oracle has executed this statement before. This is called the library cache and it doesn't work properly if you don't bind your sql statements.

For example don't do:

int n;

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

but do:

      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();
      }

Not using parameters can also cause sql injection.

Answer 4

You can use the .Net bulk load API (SQLBulkCopy). This Stackoverflow posting has a code sample of its usage. It's to SQL Server, but uploading to Oracle using SqlBulkCopy shouldn't be radically different.

Answer 5

I'm loading 50,000 records in 15 or so seconds using Array Binding in ODP.NET

It works by repeatedly invoking a stored procedure you specify (and in which you can do updates/inserts/deletes), but it passes the multiple parameter values from .NET to the database in bulk.

Instead of specifying a single value for each parameter to the stored procedure you specify an array of values for each parameter.

Oracle passes the parameter arrays from .NET to the database in one go, and then repeatedly invokes the stored procedure you specify using the parameter values you specified.

http://www.oracle.com/technology/sample_code/tech/windows/odpnet/howto/arraybind/index.html

/Damian

Answer 6

Oracle says (http://www.oracle.com/technology/products/database/utilities/htdocs/sql_loader_overview.html)

SQL*Loader is the primary method for quickly populating Oracle tables with data from external files

My experience is that their loader loads their tables faster than anything else.

Answer 7

To follow up on Theo's suggestion with my findings (apologies - I don't currently have enough reputation to post this as a comment)

First, this is how to use several named parameters:

String commandString = "INSERT INTO Users (Name, Desk, UpdateTime) VALUES (:Name, :Desk, :UpdateTime)";
using (OracleCommand command = new OracleCommand(commandString, _connection, _transaction))
{
    command.Parameters.Add("Name", OracleType.VarChar, 50).Value = strategy;
    command.Parameters.Add("Desk", OracleType.VarChar, 50).Value = deskName ?? OracleString.Null;
    command.Parameters.Add("UpdateTime", OracleType.DateTime).Value = updated;
    command.ExecuteNonQuery();
}

However, I saw no variation in speed between:

• constructing a new commandString for each row (String.Format)
• constructing a now parameterized commandString for each row
• using a single commandString and changing the parameters

I'm using System.Data.OracleClient, deleting and inserting 2500 rows inside a transaction

Answer 8

I recently discovered a specialized class that's awesome for a bulk insert (ODP.NET). Oracle.DataAccess.Client.OracleBulkCopy! It takes a datatable as a parameter, then you call WriteTOServer method...it is very fast and effective, good luck!!