This discussion is archived
3 Replies Latest reply: Apr 12, 2013 3:04 AM by 992711 RSS

.Net inserting performance

992711 Newbie
Currently Being Moderated
Hello, first post.

I am using .net 4 C#. I am using ODP.net 11g.

Within my code I am using an OracleDataAdapter with an updatebatchsize of 22. Anything larger than 22 seems to be worse performing when bechmarked over a 1 minute timed run.

The program parses a flat file which results in 134000 rows by 30 columns. The oracle data table is empty so the adapter is perfoming insert statements only.
With the settings shown above the parsing and filling of the dataset from .net takes seconds. The dataadapter.update takes 17 minutes.

I have also tried creating my own "insert all, into" multiple statements and perming an executeNonQuery() but this performs worse than above when trying to do 500 updates per run.

I have a colleague who uses a tool from Information Builders called data migrator. This can achieve for twice the rows and half the columns an upload for insert only of 4 seconds with an updatebatch size of 10000.

We are both in the UK talking to an oracle database based in Holland.

I have googled lots and tried different settings out but cannot seem to replicate anywhere near this performance using .net. Am I missing something.
  • 1. Re: .Net inserting performance
    Tridus Journeyer
    Currently Being Moderated
    Most likely the best way to speed this up is going to be to use OracleBulkCopy to do a batch insert. For large quantities of data like this, it tends to be a lot faster.

    Documentation is here: http://docs.oracle.com/cd/E20434_01/doc/win.112/e23174/OracleBulkCopyClass.htm#CHDGJBBJ

    In a quick search I didn't find any good example code, but I'm sure some is out there.
  • 2. Re: .Net inserting performance
    636190 Explorer
    Currently Being Moderated
    Howdy...
    Maybe this will help ...
           public static int BulkInsertsToTableViaDataSet(string _connectionString, string _sqlSelect,int _maxarraybindcount, ref DataSet _dataSet)
            {
                int rtn_insert_count = 0;
                int MAX_ARRAYBINDCOUNT = _maxarraybindcount;
                OracleConnection oc = new OracleConnection(_connectionString);
                OracleCommand cm = new OracleCommand(_sqlSelect, oc);
                OracleDataAdapter da = new OracleDataAdapter(cm);
                OracleCommandBuilder cb = new OracleCommandBuilder(da);
                OracleCommand theInsertCommand = cb.GetInsertCommand();
                //
                // build the generic storage for the field values based upon the insert command
                //                     COLUMNS x ROWS
                object[][] holder = new object[theInsertCommand.Parameters.Count][];
                for (int x = 0; x < theInsertCommand.Parameters.Count; x++)
                {
                    holder[x] = new object[MAX_ARRAYBINDCOUNT];
                }
                int total_reads = 0;
                int current_reads = 0; // the row counter
                foreach (DataRow d in _dataSet.Tables[0].Rows)
                {
                    total_reads = total_reads + 1;
                    // populate the column array for this row
                    for (int c = 0; c < theInsertCommand.Parameters.Count; c++)
                    {
                        holder[c][current_reads] = d[c];
                    }
                    current_reads = current_reads + 1;
                    if (current_reads == MAX_ARRAYBINDCOUNT)
                    {
                        int num_inserted = ArrayBoundInsertHelper(ref theInsertCommand, ref holder, current_reads);
                        rtn_insert_count = rtn_insert_count + num_inserted;
                        current_reads = 0;
                    }
                }
                //
                // if anything left we insert them now
                //
                if (current_reads > 0)
                {
                    int num_inserted = ArrayBoundInsertHelper(ref theInsertCommand, ref holder, current_reads);
                    rtn_insert_count = rtn_insert_count + num_inserted;
                }
                theInsertCommand.Dispose();
                cb.Dispose();
                da.Dispose();
                cm.Dispose();
                oc.Dispose();
                return rtn_insert_count;
            }
            private static int ArrayBoundInsertHelper(ref OracleCommand _theInsertCommand, ref object[][] _holder, int _boundCount)
            {
                int rtn_val = 0;
                try
                {
                    _theInsertCommand.ArrayBindCount = _boundCount;
                    for (int i = 0; i < _theInsertCommand.Parameters.Count; i++)
                    {
                        _theInsertCommand.Parameters.Value = _holder[i];
    _theInsertCommand.Parameters[i].Direction = ParameterDirection.Input;
    }
    _theInsertCommand.Connection.Open();
    rtn_val = _theInsertCommand.ExecuteNonQuery();
    }
    catch (OracleException _oraEx)
    {
    throw (_oraEx); // Actually rethrow
    }
    catch (System.Exception _sysEx)
    {
    throw (_sysEx); // Actually rethrow
    }
    finally
    {
    if (_theInsertCommand.Connection != null && _theInsertCommand.Connection.State == ConnectionState.Open)
    _theInsertCommand.Connection.Close();
    }
    return rtn_val;
    }
    I dealt with this issue sometime back. This works well for me ...
    r,
    dennis                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 3. Re: .Net inserting performance
    992711 Newbie
    Currently Being Moderated
    Thanks worked a treat....

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points