Fastest way to Insert?!!!???

Fastest way to Insert?!!!???

Postby MrTouya » Wed 10 May 2006 00:42

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, 8).Value = Fullstr(0)
InsertCommand.Parameters.Add("sunday", OracleDbType.VarChar, 8).Value = Fullstr(1)
InsertCommand.Parameters.Add("monday", OracleDbType.VarChar, 8).Value = Fullstr(2)
InsertCommand.Parameters.Add("tuesday", OracleDbType.VarChar, 8).Value = Fullstr(3)
InsertCommand.Parameters.Add("wednesday", OracleDbType.VarChar, 8).Value = Fullstr(4)
InsertCommand.Parameters.Add("thursday", OracleDbType.VarChar, 8).Value = Fullstr(5)
InsertCommand.Parameters.Add("friday", OracleDbType.VarChar, 8).Value = Fullstr(6)
InsertCommand.Parameters.Add("saturday", OracleDbType.VarChar, 8).Value = Fullstr(7)
InsertCommand.Parameters.Add("agencydata", OracleDbType.VarChar, 8).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
MrTouya
 
Posts: 3
Joined: Mon 08 May 2006 23:26

Postby Paul » Wed 10 May 2006 09:18

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 ------
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Postby MrTouya » Wed 10 May 2006 15:33

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
MrTouya
 
Posts: 3
Joined: Mon 08 May 2006 23:26

Postby Alexey » Tue 16 May 2006 08:25

Hopefully, next week.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for Oracle