Help Optomizing Bulk Inserts into Oracle Using VB.NET

Question

I am inserting a block of 5000 records at a time, one block right after the other. The commands are created in a seperate DLL and can call anyone of 4 different stored procedures. Is there a bulk insert method that might speed this up. Currrently it takes about 1.5 MS per record and would like to get this down to about .7 MS.

Thanks,

Dave

 Shared Sub WriteCMSMessagesToDatabase(ByVal myFacility As FacilityType, ByVal Commands As List(Of System.Data.OracleClient.OracleCommand))

    Dim oracleConnection As New OracleConnection
    Dim _Cmd As New OracleCommand

    Try
        Dim aStopWatch As New Stopwatch
        Using oracleConnection

            aStopWatch.Start()
            oracleConnection.ConnectionString = myFacility.ConnectionString
            _Cmd.Connection = oracleConnection
            _Cmd.CommandType = CommandType.StoredProcedure
            oracleConnection.Open()

            _Cmd.Transaction = oracleConnection.BeginTransaction

            For Each aCmd As OracleCommand In Commands
                _Cmd.CommandText = aCmd.CommandText
                _Cmd.Parameters.Clear()

                For Each aParam As OracleParameter In aCmd.Parameters
                    Dim prm As New OracleParameter
                    prm = CType(DirectCast(aParam, ICloneable).Clone, OracleParameter)
                    _Cmd.Parameters.Add(prm)
                Next

                _Cmd.ExecuteNonQuery()
            Next

            _Cmd.Transaction.Commit()
            aStopWatch.Stop()

        End Using

    Catch ex As Exception



    End Try


End Sub

Answer 1

If you are using the Oracle ODP.NET client, you can insert multiple records in a single database round trip by binding an array of values to each parameter in your query as shown here and here. I'm not sure if the System.Data.OracleClient classes support this functionality though so this may not be an option if it doesn't and you can't change provider.

Answer 2

As lee says, use array bulk binds. If you cannot do this you can fake it by creating a stored procedure that takes large, concatenated versions of your arguments

i.e. If you have 1000 rows of 3 columns to insert, you can create a stored procedure:

PROCEDURE bulk_ins(
  col1 VARCHAR2,
  cal2 VARCHAR2,
  col3 VARCHAR2
)
IS BEGIN
  FOR i in 0..20 LOOP
    INSERT INTO t (SUBSTR(col1, (20*i)+1, 20), SUBSTR(col2, (10*i)+1, 10), SUBSTR(col3, (30*i)+1, 30)); 
  END LOOP;
END;

And then in .net build your parameters by making them multiples:

StringBuilder s1, s2, s3;
for(int i = 0; i<50; i++){
  s1.AppendFormat("{0:20}", col1value[i]);
  s2.AppendFormat("{0:10}", col2value[i]);
  s3.AppendFormat("{0:30}", col3value[i]);
}

And then call the proc. Note you may have to delete spaces off values, but thisway is nearly as fast as bulk binding.. The slow part is getting data into an oracle db. Transfer it in in bulk