Efficiently inserting multiple records in oracle db using Oracle client

Question

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.Transaction = 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; 
}

}

in C#, use throw and not throw ex or your StackTrace will be messed up: http://stackoverflow.com/questions/178456/what-is-the-proper-way-to-re-throw-an-exception-in-c — Davide Piras, Oct 03 '11 at 17:21
This is Sql Server based.(url) But may give you some keywords to search "set based". https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters I'm not sure oracle has this concept. with sql server, I've also sent xml to a stored procedure, parsed ("shredded") the xml there, and did bulk INSERT. but IIRC, the default behavior of dataadapters is RBAR (row by agonizing row) — granadaCoder, Aug 04 '20 at 18:04

score 0 · Answer 1 · answered Jun 13 '12 at 10:41

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.

score 0 · Answer 2 · answered Sep 04 '12 at 17:03

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.

score 0 · Answer 3 · answered Aug 04 '20 at 18:13

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/

And using some mad internet search skills, I found this : https://www.c-sharpcorner.com/article/two-ways-to-insert-bulk-data-into-oracle-database-using-c-sharp/ which uses (as my answer points out) : "using Oracle.ManagedDataAccess.Client;" — granadaCoder, Aug 04 '20 at 18:18

score 0 · Answer 4 · answered Oct 04 '11 at 14:41

0

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

answered Oct 04 '11 at 14:41

Klas Lindbäck

33,105
5
57
82

Efficiently inserting multiple records in oracle db using Oracle client

4 Answers4