DevArt Bulk Data Insertion into an Oracle Database

There are some possibilities to bulk insert data. For my tests I inserted 1’500 rows containing some strings.

  • Use the Context: 50 seconds
  • Use ArrayBinding: 43 seconds
  • Use the OracleLoader class: 14 seconds

It seems that the OracleLoader is the fastes way to do this.

ArrayBinding:

        private void TestArrayBindingInsert(Context context) {
            const string connectionString = "DATA SOURCE=(DESCRIPTION=
(ADDRESS= (PROTOCOL=TCP) (HOST=...) ...";
            var connection = new OracleConnection(connectionString);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText =  "INSERT INTO TESTTABLE (TEXT1, TEXT2) VALUES (:p1, :p2)";

            command.Parameters.Add("p1", OracleDbType.VarChar);
            command.Parameters.Add("p2", OracleDbType.Clob);
            command.Parameters["p1"].Value = _texts.ToArray();
            command.Parameters["p2"].Value =  _texts.ToArray();
            command.Prepare();
            command.ExecuteArray(protocolIds.Length);
            command.Dispose();
            connection.Close();
        }

OracleDataLoader:

        private void TestOracleLoaderInsert(MainModelEntities context, long
userId, long writeGroupId) {
            string[] texts1 = _texts.ToArray();
            string[] texts2 = _texts.ToArray();

            const string connectionString = "DATA SOURCE=(DESCRIPTION=
(ADDRESS= (PROTOCOL=TCP) (HOST=...) ...";

            var connection = new OracleConnection(connectionString);
            connection.Open();

            OracleLoader loader = new OracleLoader {
                Connection = connection,
                TableName = "TestTable"
            };

            loader.CreateColumns();
            loader.Open();

            for (int i = 0; i < texts1.Length; i++) {
                loader.SetValue("TEXT1", actionTextsEn[i]);
                loader.SetValue("TEXT2", actionTextsFr[i]);
                loader.NextRow();
            }
            loader.Close();
            connection.Close();
        }

Leave a comment

Your email address will not be published. Required fields are marked *