I have the problem that inserting into an Oracle table takes a very long time when using CLOBs or BLOBs. I use array binding (see example below) and the .Net Oracle managed driver. As soon as the OracleDbType Clob or Blob is used the insert speed decreases massively. If I change the type to Varchar2 it runs very fast. Unfortunately this is not the solution because we have strings that can become very long.
It looks like as soon as I use the OracleDbType Clob, single inserts are executed.
Do I miss a setting or is it a bug or a documented behavior?
Insert of 1000 records (see example):
Data insert clob: 4,3272996
Data insert Varchar: 0,0231472
Example:
Create table with lob:
CREATE TABLE EXAMPLE_DATA
(
ID NUMBER,
DATA CLOB
);
Unittest
[Fact]
public void ExampleArrayBinding()
{
Stopwatch s = new Stopwatch();
using (var connection = new OracleConnection(conString))
{
connection.Open();
using (OracleCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "INSERT INTO EXAMPLE_DATA VALUES(:id, :data)";
// Add parameters to command parameters collection
cmd.Parameters.Add("id", OracleDbType.Int16);
cmd.Parameters.Add("data", OracleDbType.Clob);
// Set parameters values
cmd.Parameters["id"].Value = Enumerable.Range(1, 1000).ToArray();
cmd.Parameters["data"].Value = Enumerable.Range(1, 1000).Select(x => $"a{x}").ToArray();
cmd.ArrayBindCount = 1000;
//clob Parameter
this.TruncateTable("EXAMPLE_DATA", connection);
s.Start();
cmd.ExecuteNonQuery();
s.Stop();
this.output.WriteLine("Data insert clob: " + s.Elapsed.TotalSeconds);
//varchar2 Parameter
this.TruncateTable("EXAMPLE_DATA", connection);
s.Restart();
cmd.Parameters["data"].OracleDbType = OracleDbType.Varchar2;
cmd.ExecuteNonQuery();
s.Stop();
this.output.WriteLine("Data insert Varchar: " + s.Elapsed.TotalSeconds);
}
}
}