Optimize insert procedure (my procedure takes 1h30 to insert 100'000 rows)

Question

I have a c# procedure that inserts data into an Oracle Database. I tried to use bulk insert but the performance a awfully bad and I don't know why. It takes approximately 1h30 to insert more than 100'000 rows !

Here is my insert procedure :

internal void InsertPublications(DateTime aDate, List<Model.Publication> aPublications, Action<int> aProgress, Action<int> aCallBack)
{
    List<List<Model.Publication>> _Publications = Split(aPublications, 128);

    int _InsertedCount = 0;

    var _Worker = new BackgroundWorker() { WorkerReportsProgress = true };

    _Worker.DoWork += (_Sender, _Args) =>
    {
        var           _TableName = string.Format("SRC_PUBLICATION_{0}", aDate.ToString("yyyyMMdd"));
        OracleCommand _Command;

        this.Connect();

        try
        {
            this.CreateOrReplaceTable(_TableName, SQL_CREATE_PULICATIONS_TABLE);

            foreach (var _PublicationSub in _Publications)
            {
                if (!DlgParamPublication.alive)
                {
                    break;
                }

                _Command = this.Connection.CreateCommand();
                _Command.CommandText = string.Format(SQL_INSERT_PULICATIONS, _TableName);
                _Command.CommandType = CommandType.Text;
                _Command.BindByName = true;
                _Command.ArrayBindCount = _PublicationSub.Count;

                _Command.Parameters.Add(":ANNEE", OracleDbType.NVarchar2, _PublicationSub.Select(_Item => _Item.Year).ToArray(), ParameterDirection.Input);
                _Command.Parameters.Add(":IDE_PUBLICATION", OracleDbType.NVarchar2, _PublicationSub.Select(_Item => _Item.Pid).ToArray(), ParameterDirection.Input);
                _Command.Parameters.Add(":IDE_TYPE_PUBLICATION", OracleDbType.NVarchar2, _PublicationSub.Select(_Item => _Item.IdePublicationType.ToString().ToLower()).ToArray(), ParameterDirection.Input);
                _Command.Parameters.Add(":IDE_TYPE_TRAVAIL", OracleDbType.NVarchar2, _PublicationSub.Select(_Item => _Item.IdeTypeOfWork.ToString().ToLower()).ToArray(), ParameterDirection.Input);
                _Command.Parameters.Add(":IDE_NIVEAU_ELEMENT_ORG", OracleDbType.NVarchar2, _PublicationSub.Select(_Item => _Item.UserRefType.ToString().ToLower()).ToArray(), ParameterDirection.Input);
                _Command.Parameters.Add(":IDE_ELEMENT_ORG", OracleDbType.NVarchar2, _PublicationSub.Select(_Item => _Item.UserRefValue).ToArray(), ParameterDirection.Input);
                _Command.Parameters.Add(":AUTEUR", OracleDbType.NClob, _PublicationSub.Select(_Item => _Item.Author).ToArray(), ParameterDirection.Input);

                _InsertedCount += _Command.ExecuteNonQuery();

                _Worker.ReportProgress(0, _InsertedCount);
            }
        catch (Exception _Exc)
        {
            ViewModel.DlgParamPublication.Logger.LogException("INSERT PUBLICATIONS", _Exc, false);
        }
        finally
        {
            this.Disconnect();
        }
    };

    _Worker.ProgressChanged    += (_Sender, _Args) => { aProgress((int)_Args.UserState); };
    _Worker.RunWorkerCompleted += (_Sender, _Args) => { aCallBack(_InsertedCount); };

    // Start background thread
    if (DlgParamPublication.alive)
    {
        _Worker.RunWorkerAsync();
    }
}

Any idea why it's taking so long ?

UPDATE

Does anyone has another suggestion ? I wasn't been able to resolve the problem.

http://www.akadia.com/services/ora_bulk_insert.html – Mikey Apr 23 '12 at 11:53 — Mikey, Apr 23 '12 at 11:53

Marc · Answer 1 · 2012-04-23T12:22:47.287

The idea is to send arrays of values for each parameter in a single stored proc call. You're effectively sending an array of a single value n times, where n is the length of _Publications.

Remove the outer loop and replace your _PublicationSub.Select's with _Publications.Select.

You also need to change your bind count _Command.ArrayBindCount = _Publications.Count;

To make it easier, this is what I believe will work:

try
        {
            this.CreateOrReplaceTable(_TableName, SQL_CREATE_PULICATIONS_TABLE);


            if (!DlgParamPublication.alive)
            {
                break;
            }

            _Command = this.Connection.CreateCommand();
            _Command.CommandText = string.Format(SQL_INSERT_PULICATIONS, _TableName);
            _Command.CommandType = CommandType.Text;
            _Command.BindByName = true;
            _Command.ArrayBindCount = _Publications.Count;

            _Command.Parameters.Add(":ANNEE", OracleDbType.NVarchar2, _Publications.Select(_Item => _Item.Year).ToArray(), ParameterDirection.Input);
            _Command.Parameters.Add(":IDE_PUBLICATION", OracleDbType.NVarchar2, _Publications.Select(_Item => _Item.Pid).ToArray(), ParameterDirection.Input);
            _Command.Parameters.Add(":IDE_TYPE_PUBLICATION", OracleDbType.NVarchar2, _Publications.Select(_Item => _Item.IdePublicationType.ToString().ToLower()).ToArray(), ParameterDirection.Input);
            _Command.Parameters.Add(":IDE_TYPE_TRAVAIL", OracleDbType.NVarchar2, _Publications.Select(_Item => _Item.IdeTypeOfWork.ToString().ToLower()).ToArray(), ParameterDirection.Input);
            _Command.Parameters.Add(":IDE_NIVEAU_ELEMENT_ORG", OracleDbType.NVarchar2, _Publications.Select(_Item => _Item.UserRefType.ToString().ToLower()).ToArray(), ParameterDirection.Input);
            _Command.Parameters.Add(":IDE_ELEMENT_ORG", OracleDbType.NVarchar2, _Publications.Select(_Item => _Item.UserRefValue).ToArray(), ParameterDirection.Input);
            _Command.Parameters.Add(":AUTEUR", OracleDbType.NClob, _Publications.Select(_Item => _Item.Author).ToArray(), ParameterDirection.Input);

            _InsertedCount += _Command.ExecuteNonQuery();

            _Worker.ReportProgress(0, _InsertedCount);
        }
    catch (Exception _Exc)
    {
        ViewModel.DlgParamPublication.Logger.LogException("INSERT PUBLICATIONS", _Exc, false);
    }
    finally
    {
        this.Disconnect();
    }

You will probably want to remove your progress for the background worker as this is a one-shot, 0-100% in a single call, deal.

What do you mean by "Remove the outer loop" ? This line: foreach (var _PublicationSub in _Publications) ? — krlzlx, Apr 23 '12 at 12:19
@krlzlx check the edit. I'm effectively applying my directions to your posted code. — Marc, Apr 23 '12 at 12:23
I tried your solution. Seems not to work. Nothing is added to the database. Does it has something to do with the background worker ? — krlzlx, Apr 23 '12 at 12:59
I tried to remove the background worker. The application freezes after 3 minutes and nothing was added to the database. — krlzlx, Apr 23 '12 at 13:14
It appears to freeze because you're then doing everything on the UI thread. It really depends on how big the items are that you are adding (when converted to strings and sent as parameters). It may make sense for you to batch 50k at a time. — Marc, Apr 23 '12 at 13:27
To give you an idea of the size of the items : the size of the XML containing all the items (~130'000 items) = 116Mo. I tried to split the collection of Publications with batch of 30'000 instead of 128 with no gain of performance. — krlzlx, Apr 23 '12 at 14:07

score 1 · Answer 2 · answered Apr 23 '12 at 12:05

I will try to create the OracleCommand and all the OracleParameter outside the foreach loop.
Inside the loop, I will add the values for the current iteration

_Command = this.Connection.CreateCommand();             
_Command.CommandText = string.Format(SQL_INSERT_PULICATIONS, _TableName);             _Command.CommandType = CommandType.Text;             
_Command.BindByName = true;             
_Command.Parameters.Add(":ANNEE", OracleDbType.NVarchar2, "", ParameterDirection.Input);             _Command.Parameters.Add(":IDE_PUBLICATION", OracleDbType.NVarchar2, "", ParameterDirection.Input);             
.....

foreach (var _PublicationSub in _Publications)             
{ 
     _Command.Parameters[":ANNEE"].Value = _PublicationSub.Select(_Item => _Item.Year).ToArray();
     ....
}

@krlzlx well then you could try other tricks like disabling indexes, but given the amount of data, you should try to use bulk copy [see this question](http://stackoverflow.com/questions/343299/bulk-insert-to-oracle-using-net) — Steve, Apr 23 '12 at 13:52

score 1 · Answer 3 · answered Jan 26 '13 at 04:20

1

I don't know for sure, but the problem may be BindByName. I suggest numeric binding like in this example.

answered Jan 26 '13 at 04:20

Arturo Hernandez

2,749
3
28
36

Optimize insert procedure (my procedure takes 1h30 to insert 100'000 rows)

UPDATE

3 Answers3