Import data, performance

[kamen]

Hello.

We use for import of data array binding. Is there any faster method?

Simplified code - only one column:

Code: Select all

void Import(IDbConnection connection, object[] data)
{
  OracleParameter parameter = new OracleParameter();
  parameter.Value = data;
  parameter.ArrayLength = data.Length;
  string commandText = "INSERT INTO TBL (COL) VALUES (:BIND0)";
  using (IDbCommand command = connection.CreateCommand())
  {
    command.CommandText = commandText;
    command.Parameters.Add(parameter);
    command.ExecuteArray(data.Length);
  }
}

Regards,
Petr.

[Shalex]

The fastest way to load data into Oracle is usage of the OracleLoader class: https://www.devart.com/dotconnect/oracl ... oader.html.

[kamen]

And what about case, when table TBL has a trigger defined on it? I received error: ORA-26086: direct path does not support triggers.

Documentation states, that OracleLoader doesn't support triggers. Then only DML arrays can be used. Right?

[Pinturiccio]

Yes, triggers are not supported in OracleLoader when direct path loading is used. As you supposed, you need to use DML Arrays in your case. For more information, please refer to https://www.devart.com/dotconnect/oracl ... yBind.html

You can also execute the following command:

Code: Select all

ALTER TABLE TableName DISABLE ALL TRIGGERS;

then use OracleLoader with direct path and after this execute the following command:

Code: Select all

ALTER TABLE TableName ENABLE ALL TRIGGERS;