3 Replies Latest reply: Apr 12, 2013 5:04 AM by 992711 RSS

    .Net inserting performance

    992711
      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
          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
            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
              Thanks worked a treat....