Import data, performance

Import data, performance

Postby kamen » Wed 25 Nov 2015 12:46

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.
kamen
 
Posts: 6
Joined: Wed 25 Nov 2015 12:27

Re: Import data, performance

Postby Shalex » Wed 25 Nov 2015 15:43

The fastest way to load data into Oracle is usage of the OracleLoader class: https://www.devart.com/dotconnect/oracle/docs/?Devart.Data.Oracle~Devart.Data.Oracle.OracleLoader.html.
Shalex
Devart Team
 
Posts: 7705
Joined: Thu 14 Aug 2008 12:44

Re: Import data, performance

Postby kamen » Thu 26 Nov 2015 13:40

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?
kamen
 
Posts: 6
Joined: Wed 25 Nov 2015 12:27

Re: Import data, performance

Postby Pinturiccio » Fri 27 Nov 2015 14:28

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/oracle/docs/?ArrayBind.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;
Pinturiccio
Devart Team
 
Posts: 1997
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle