Fastest way to Insert?!!!???

[MrTouya]

Hi Guys,

I have tried everything to get my inserts as fast as possible. I cannot use ExecuteArray or OracleLoader because I have to be aware of when there is an insert error due to a malformed line. My code is the following.

--------
Using InsertCommand As OracleCommand = MyConnection.CreateCommand
InsertCommand.CommandText = "INSERT INTO co.coredata (guid, eventcode, sunday, monday, tuesday, wednesday, thursday, friday, saturday, agencydata, image, checknum, noexception, weeknumber, loadyear, loaddate, rownumber) VALUES (:guid, :eventcode, :sunday, :monday, :tuesday, :wednesday, :thursday, :friday, :saturday, :agencydata, :image, :checknum,:noexception, :weeknumber, :loadyear, :loaddate, :rownumber)"

InsertCommand.Prepare()

For LineNumber = 0 To LineCount - 1
Lines(LineNumber) = Lines(LineNumber).Replace("""", "")
Lines(LineNumber) = Lines(LineNumber).Replace(" ", "") '
Dim Fullstr() As String = Split(Lines(LineNumber), ",", , CompareMethod.Text)

InsertCommand.Parameters.Add("guid", OracleDbType.VarChar, 64).Value = System.Guid.NewGuid
InsertCommand.Parameters.Add("eventCode",OracleDbType.VarChar, .Value = Fullstr(0)
InsertCommand.Parameters.Add("sunday", OracleDbType.VarChar, .Value = Fullstr(1)
InsertCommand.Parameters.Add("monday", OracleDbType.VarChar, .Value = Fullstr(2)
InsertCommand.Parameters.Add("tuesday", OracleDbType.VarChar, .Value = Fullstr(3)
InsertCommand.Parameters.Add("wednesday", OracleDbType.VarChar, .Value = Fullstr(4)
InsertCommand.Parameters.Add("thursday", OracleDbType.VarChar, .Value = Fullstr(5)
InsertCommand.Parameters.Add("friday", OracleDbType.VarChar, .Value = Fullstr(6)
InsertCommand.Parameters.Add("saturday", OracleDbType.VarChar, .Value = Fullstr(7)
InsertCommand.Parameters.Add("agencydata", OracleDbType.VarChar, .Value = Fullstr(8)
InsertCommand.Parameters.Add("image", OracleDbType.VarChar, 120).Value = Fullstr(9)
InsertCommand.Parameters.Add("checknum", OracleDbType.VarChar, 50).Value = Fullstr(10)
InsertCommand.Parameters.Add("noexception", OracleDbType.VarChar, 50).Value = Fullstr(11)
InsertCommand.Parameters.Add("weeknumber", OracleDbType.Char, 2).Value = WeekNum
InsertCommand.Parameters.Add("loadyear", OracleDbType.Char, 4).Value = Year
InsertCommand.Parameters.Add("loaddate", OracleDbType.Date).Value = String.Format("{0:dd-MMM-yyyy}", cbLoadDate.Value)
InsertCommand.Parameters.Add("rownumber", OracleDbType.Integer).Value = LineNumber

InsertCommand.ExecuteNonQuery()

Next LineNumber
End Using ------

My delima is when I use a straight insert in MS SQL Server(without binding) the above code executes at least 10x faster than when I am inserting into Oracle. I HATE SQL SERVER and I don't want to have to use it again. PS> Each load contains about 100,000 lines.

CAN SOMEONE HELP ME OUT THERE????

Thanks,

Stephane

[Paul]

ExecuteArray method has an error in implementation. You can receive error message for each row using OracleException.Errors list. We will fix this in the next build.
In your example you added to many OracleParameter objects in the loop and did not clear Parameters collection in each iteration. Do not modify Parameters collection after calling OracleCommand.Prepare. This clears statement preparation. Try to do the following.

Code: Select all

Using InsertCommand As OracleCommand = MyConnection.CreateCommand 
InsertCommand.CommandText = "INSERT INTO co.coredata (guid, eventcode, sunday, monday, tuesday, wednesday, thursday, friday, saturday, agencydata, image, checknum, noexception, weeknumber, loadyear, loaddate, rownumber) VALUES (:guid, :eventcode, :sunday, :monday, :tuesday, :wednesday, :thursday, :friday, :saturday, :agencydata, :image, :checknum,:noexception, :weeknumber, :loadyear, :loaddate, :rownumber)" 

InsertCommand.Parameters.Add("guid", OracleDbType.VarChar, 64)
InsertCommand.Parameters.Add("eventCode",OracleDbType.VarChar)
InsertCommand.Parameters.Add("sunday", OracleDbType.VarChar)
InsertCommand.Parameters.Add("monday", OracleDbType.VarChar)
InsertCommand.Parameters.Add("tuesday", OracleDbType.VarChar)
InsertCommand.Parameters.Add("wednesday", OracleDbType.VarChar)
InsertCommand.Parameters.Add("thursday", OracleDbType.VarChar)
InsertCommand.Parameters.Add("friday", OracleDbType.VarChar)
InsertCommand.Parameters.Add("saturday", OracleDbType.VarChar)
InsertCommand.Parameters.Add("agencydata", OracleDbType.VarChar)
InsertCommand.Parameters.Add("image", OracleDbType.VarChar, 120)
InsertCommand.Parameters.Add("checknum", OracleDbType.VarChar, 50)
InsertCommand.Parameters.Add("noexception", OracleDbType.VarChar, 50)
InsertCommand.Parameters.Add("weeknumber", OracleDbType.Char, 2)
InsertCommand.Parameters.Add("loadyear", OracleDbType.Char, 4)
InsertCommand.Parameters.Add("loaddate", OracleDbType.Date)
InsertCommand.Parameters.Add("rownumber", OracleDbType.Integer)

InsertCommand.Prepare() 

For LineNumber = 0 To LineCount - 1 
Lines(LineNumber) = Lines(LineNumber).Replace("""", "") 
Lines(LineNumber) = Lines(LineNumber).Replace(" ", "") ' 
Dim Fullstr() As String = Split(Lines(LineNumber), ",", , CompareMethod.Text) 

InsertCommand.Parameters("guid").Value = System.Guid.NewGuid 
InsertCommand.Parameters("eventCode").Value = Fullstr(0) 
InsertCommand.Parameters("sunday").Value = Fullstr(1) 
InsertCommand.Parameters("monday").Value = Fullstr(2) 
InsertCommand.Parameters("tuesday").Value = Fullstr(3) 
InsertCommand.Parameters("wednesday").Value = Fullstr(4) 
InsertCommand.Parameters("thursday").Value = Fullstr(5) 
InsertCommand.Parameters("friday").Value = Fullstr(6) 
InsertCommand.Parameters("saturday").Value = Fullstr(7) 
InsertCommand.Parameters("agencydata").Value = Fullstr( 
InsertCommand.Parameters("image").Value = Fullstr(9) 
InsertCommand.Parameters("checknum").Value = Fullstr(10) 
InsertCommand.Parameters("noexception").Value = Fullstr(11) 
InsertCommand.Parameters("weeknumber").Value = WeekNum 
InsertCommand.Parameters("loadyear").Value = Year 
InsertCommand.Parameters("loaddate").Value = String.Format("{0:dd-MMM-yyyy}", cbLoadDate.Value) 
InsertCommand.Parameters("rownumber").Value = LineNumber 

InsertCommand.ExecuteNonQuery() 

Next LineNumber 
End Using ------ 

[MrTouya]

Thanks Paul!

That was it. It is much faster. Not as fast as executearray, but good enough for now. When is the new build with the new ExecuteArray supposed to be released?

Stephane

[Alexey]

Hopefully, next week.