Inserts in Oracle table with array binding and CLOB is very slow

Viewed 309

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);
                }
            }

        }
1 Answers

I could understand the problem and the test gave even worse results.

Data insert clob: 16,7964261
Data insert Varchar: 0,0375037

I looked into the Oracle Support forum and found the tech note Performance Problems Using Clob and Blobs (Doc ID 401589.1), which might describe your problem although it applies to the unmanaged version of the driver.
According to this tech note you should bind clob data as varchar2 and blob data as byte[], if the data size is less than 32K for clob and less than 64K for blob.

Related