Efficiently inserting multiple records in oracle db using Oracle client

I've C#-WCF/Winforms application.It inserts 450,000 plus records in a staging table in Oracle database using Oracle Client API and a stored proc having a simple insert query. Its taking about 15 minutes to insert records in db and sometimes the records dont get inserted too..giving all sorts of timeout errors at the wcf side. Is there any efficient way of doing these inserts?

Thanks for reading.

Here's my code which does the batch insert:

OracleTransaction tran = null; 
UpdateRowSource oldURS = this.cmd.UpdatedRowSource; 
OracleCommand oldCmd = this.dbAdapter.InsertCommand; 
int oldUBS = this.dbAdapter.UpdateBatchSize; 
try 
{ 
    SetOutputParams(); 
    this.OpenDBConnection(); 
    tran = this.dbConn.BeginTransaction(); 
    this.cmd.Trans开发者_StackOverflow中文版action = tran; 
    this.cmd.UpdatedRowSource = UpdateRowSource.OutputParameters; 
    this.dbAdapter.InsertCommand = this.cmd; 
    this.dbAdapter.UpdateBatchSize = size; 
    this.dbAdapter.Update(data); 
    tran.Commit(); 
    SetOutputParamValues(); 
} 
catch (OracleException ex) 
{ 
    if (tran != null) { 
        tran.Rollback(); 
    } 
    throw; 
} 
finally 
{ 
    this.CloseDBConnection(); 
    this.cmd.Parameters.Clear(); 
    this.cmd.UpdatedRowSource = oldURS; 
    this.dbAdapter.InsertCommand = oldCmd; 
    this.dbAdapter.UpdateBatchSize = oldUBS; 
} 

}

The fastest way to load data into a table is datapump (the impdp utility). Another fast way is SQL*Loader.

If you want to stick with C#, look into bulk operations. My google karma found the following examples

I have used SQL*Loader frequently from C# to bulk load records to stage.

The meat of the code goes like this:

public string RunSqlLdr(string user, string password, string dsn, string fileNameCtl, string fileNameLog)
{
    // Redirect both streams so we can write/read them.
    var cmdProcessInfo = new System.Diagnostics.ProcessStartInfo("cmd.exe")
    {
        RedirectStandardInput = true,
        RedirectStandardOutput = true,
        UseShellExecute = false
    };

    // Start the process. 
    var process = System.Diagnostics.Process.Start(cmdProcessInfo);

    // Issue the sqlldr command and exit.
    process.StandardInput.WriteLine("cd " + _directoryPath);
    process.StandardInput.WriteLine("sqlldr " + user + "/" + password + "@" + dsn + " control=" + fileNameCtl + " log=" + fileNameLog);
    process.StandardInput.WriteLine("exit");

    // Read all the output generated from it.
    var output = process.StandardOutput.ReadToEnd();
    process.Dispose();

    return output;
}

This will return the output from the command line, but you'll also want to check the generated log file for records loaded and error counts etc.

I am inserting a lot of data into an Oracle database that is located in Australia, far away form where I am running the client application in C#.

Here's the summary of how I am doing it. It amazes me how fast I insert hundreds of thousands of records fast using array binding.

This is not the exact code, but you get the idea:

using System.Data.OleDb;

int numRecords = 2;
int[] DISTRIBNO = new int[numRecords];
DISTRIBNO[0] = 100;
DISTRIBNO[1] = 101;

string sql = "INSERT INTO Distributors (distribno) VALUES (:DISTRIBNO)";
cnn = new Oracle.DataAccess.Client.OracleConnection(conString);
cnn.Open();

using (Oracle.DataAccess.Client.OracleCommand cmd = cnn.CreateCommand())
{
    cmd.CommandText = sql;
    cmd.CommandType = CommandType.Text;
    cmd.BindByName = true;
    // To use ArrayBinding, we need to set ArrayBindCount                
    cmd.ArrayBindCount = numRecords;
    cmd.CommandTimeout = 0;

    cmd.Parameters.Add(
              ":DISTRIBNO", 
              Oracle.DataAccess.Client.OracleDbType.Int32, 
              BR_VOLMONTH,
              ParameterDirection.Input);

    cmd.ExecuteNonQuery();
}//using

Carlos Merighe.

You will probably have to abandon "DataAdapter" code to get any real performance.

Here is the dotnet class that seems to be on target.

Oracle.DataAccess.Client.OracleBulkCopy

OracleBulkCopy Class

An OracleBulkCopy object efficiently bulk loads or copies data into an Oracle table from another data source.

https://docs.oracle.com/cd/E11882_01/win.112/e23174/OracleBulkCopyClass.htm#ODPNT7446

https://docs.oracle.com/cd/E85694_01/ODPNT/BulkCopyCtor3.htm

.........

This is from the (oracle owned) ODP.NET.

See

https://www.nuget.org/packages/Oracle.ManagedDataAccess/