ADO.net Got speed ?!?!

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I recently upgraded from VB6 to VB.net and decided that i
would like to update a program i use a lot. The Program
Connects to a M$ access database (access 2000). It reads
in server logs and parses the infomation using the Split
command and a select statement to pick out appropriate
words. It then inserts each 'record' (each line in the
server log contains 1 record) into the database.

Under VB6 i used DAO (of course) and my program was fine.
The searching was Fast, the data retreival was fast, the
input FAST. It took less than 2 seconds to parse a single
log file and insert the whole thing into the database. I
timed it. To parse 62,398 records it took 6 minutes 23
seconds, Give or take 10 seconds.

Now i switched to ADO.net (thinking new = better :oops:)
I use Datasets to populate a Datagrid (easy and fast). I
use dataadapters to insert the info to the database by
updating the datset and using the .update command.

Now i guess my question is why does it take my program 38
minutes 54 seconds (give or take 1 minute) to parse
62,398 records? ??!?!!?!?!?!?

When i insert the data i dont fill any datasets
(something which could if in a loop add vital seconds to
each loop) i dont do anything other than add the data
then update.

Now i cant for the Life of me imagine that the M$
programmers Butchered the SPlit Code. So in my opinion i
rule that out. So im afriad the only thing left is the
data access....

Sample Code::
-----------------
PlayerDA.InsertCommand = New OleDbCommand("INSERT
INTO Players ([Date], [Time], Name, WonID, IPAddress)
Values(?,?,?,?,?)", DBConnection)
PlayerDA.InsertCommand.CommandType =
CommandType.Text

PlayerDA.InsertCommand.Parameters.Add("@Date",
OleDbType.Char, 50, "Date")
PlayerDA.InsertCommand.Parameters.Add("@Time",
OleDbType.Char, 50, "Time")
PlayerDA.InsertCommand.Parameters.Add("@Name",
OleDbType.Char, 50, "Name")
PlayerDA.InsertCommand.Parameters.Add("@WonID",
OleDbType.Char, 50, "WonID")
PlayerDA.InsertCommand.Parameters.Add
("@IPAddress", OleDbType.Char, 50, "IPAddress")

DBConnection.Open()

Dim newRow As DataRow = DataSet1.Tables
("Players").NewRow()
newRow("Date") = ""
newRow("Time") = ""
newRow("Name") = ""
newRow("WonID") = ""
newRow("IPAddress") = ""
DataSet1.Tables("players").Rows.Add(newRow)

PlayerDA.Update(DataSet1, "Players")
DBConnection.Close()
----------------

Insert blank... is it that ^^^ ? or is it my Split code?

Sample Code:
------------------------------
Do Until LineCount = NumberOfLinesInLogFile
UnformattedLogLine = LogFile(LineCount).Word
UnformattedLogLineSplit = Split
(UnformattedLogLine, " ")
UnFormattedLogLineNumOfValues = UBound
(UnformattedLogLineSplit)

'Formatting the Info
Do Until GettingParsedValues >
UnFormattedLogLineNumOfValues
Select Case GettingParsedValues
Case 0
Case 1
Case 2
Temp = UnformattedLogLineSplit
(GettingParsedValues)
ParsedArray(0) = Temp.Trim
(TrimValues.ToCharArray())
Case 3
Temp = UnformattedLogLineSplit
(GettingParsedValues)
ParsedArray(1) = Temp.Trim
(TrimValues.ToCharArray())
Case 4
GettingName = Split
(UnformattedLogLine, "NAME:")
GettingNameUpper = UBound
(GettingName)
If GettingNameUpper > 1 Then
GettingNameFoundToSplit =
GettingName(1)
GettingName2 = Split
(GettingNameFoundToSplit, ",")
Name = Trim(GettingName2(0))
ParsedArray(2) = Name
Else
GettingName = Split
(UnformattedLogLine, " ")
Name = Trim(GettingName(4))
ParsedArray(2) = Name
End If
Case 5
Case 6
Case 7
GettingWonID = Split
(UnformattedLogLine, ":")
gettingwonidupper = UBound
(GettingWonID)
If gettingwonidupper > 3 Then
GettingWonIDFoundtoSplit =
GettingWonID(5)
GettingWonID2 = Split
(GettingWonIDFoundtoSplit, ",")
WonID = Trim(GettingWonID2(0))
ParsedArray(3) = WonID
Else
GettingWonID = Split
(UnformattedLogLine, ",")
WonID = Trim(GettingWonID(2))
ParsedArray(3) = WonID
End If
Case 8
GettingIP = Split
(UnformattedLogLine, "IP:")
GettingIPFoundToSplit = GettingIP
(1)
GettingIP2 = Split
(GettingIPFoundToSplit, ":")
IPAddress = Trim(GettingIP2(0))
ParsedArray(4) = IPAddress
Case Else
End Select
GettingParsedValues = GettingParsedValues
+ 1
Loop

IncrementLineCount()
IncrementRow()
GettingParsedValues = 0
InsertingParsedValues = 0
Col = 0
Insert_to_database()

Loop

--------------------

I really would like some insight from anyone :) I will be
absolutely speachless to think my split code is causing
this kind of performance degredation especially when its
IDENTICAL to the previous version (under vb6).
So im pretty sure its my Database code. This is my first
ADO.net so be kind ;)
 
wait - are you calling an insert command on the database for every row?
In ADO.NET you shouldn't do this if you want to optimize perf on a large
number of inserts.
Instead, add all the rows to the dataset, and then call Update().

It ought to be quite a bit faster.


Mike said:
I recently upgraded from VB6 to VB.net and decided that i
would like to update a program i use a lot. The Program
Connects to a M$ access database (access 2000). It reads
in server logs and parses the infomation using the Split
command and a select statement to pick out appropriate
words. It then inserts each 'record' (each line in the
server log contains 1 record) into the database.

Under VB6 i used DAO (of course) and my program was fine.
The searching was Fast, the data retreival was fast, the
input FAST. It took less than 2 seconds to parse a single
log file and insert the whole thing into the database. I
timed it. To parse 62,398 records it took 6 minutes 23
seconds, Give or take 10 seconds.

Now i switched to ADO.net (thinking new = better :oops:)
I use Datasets to populate a Datagrid (easy and fast). I
use dataadapters to insert the info to the database by
updating the datset and using the .update command.

Now i guess my question is why does it take my program 38
minutes 54 seconds (give or take 1 minute) to parse
62,398 records? ??!?!!?!?!?!?

When i insert the data i dont fill any datasets
(something which could if in a loop add vital seconds to
each loop) i dont do anything other than add the data
then update.

Now i cant for the Life of me imagine that the M$
programmers Butchered the SPlit Code. So in my opinion i
rule that out. So im afriad the only thing left is the
data access....

Sample Code::
-----------------
PlayerDA.InsertCommand = New OleDbCommand("INSERT
INTO Players ([Date], [Time], Name, WonID, IPAddress)
Values(?,?,?,?,?)", DBConnection)
PlayerDA.InsertCommand.CommandType =
CommandType.Text

PlayerDA.InsertCommand.Parameters.Add("@Date",
OleDbType.Char, 50, "Date")
PlayerDA.InsertCommand.Parameters.Add("@Time",
OleDbType.Char, 50, "Time")
PlayerDA.InsertCommand.Parameters.Add("@Name",
OleDbType.Char, 50, "Name")
PlayerDA.InsertCommand.Parameters.Add("@WonID",
OleDbType.Char, 50, "WonID")
PlayerDA.InsertCommand.Parameters.Add
("@IPAddress", OleDbType.Char, 50, "IPAddress")

DBConnection.Open()

Dim newRow As DataRow = DataSet1.Tables
("Players").NewRow()
newRow("Date") = ""
newRow("Time") = ""
newRow("Name") = ""
newRow("WonID") = ""
newRow("IPAddress") = ""
DataSet1.Tables("players").Rows.Add(newRow)

PlayerDA.Update(DataSet1, "Players")
DBConnection.Close()
----------------

Insert blank... is it that ^^^ ? or is it my Split code?

Sample Code:
------------------------------
Do Until LineCount = NumberOfLinesInLogFile
UnformattedLogLine = LogFile(LineCount).Word
UnformattedLogLineSplit = Split
(UnformattedLogLine, " ")
UnFormattedLogLineNumOfValues = UBound
(UnformattedLogLineSplit)

'Formatting the Info
Do Until GettingParsedValues >
UnFormattedLogLineNumOfValues
Select Case GettingParsedValues
Case 0
Case 1
Case 2
Temp = UnformattedLogLineSplit
(GettingParsedValues)
ParsedArray(0) = Temp.Trim
(TrimValues.ToCharArray())
Case 3
Temp = UnformattedLogLineSplit
(GettingParsedValues)
ParsedArray(1) = Temp.Trim
(TrimValues.ToCharArray())
Case 4
GettingName = Split
(UnformattedLogLine, "NAME:")
GettingNameUpper = UBound
(GettingName)
If GettingNameUpper > 1 Then
GettingNameFoundToSplit =
GettingName(1)
GettingName2 = Split
(GettingNameFoundToSplit, ",")
Name = Trim(GettingName2(0))
ParsedArray(2) = Name
Else
GettingName = Split
(UnformattedLogLine, " ")
Name = Trim(GettingName(4))
ParsedArray(2) = Name
End If
Case 5
Case 6
Case 7
GettingWonID = Split
(UnformattedLogLine, ":")
gettingwonidupper = UBound
(GettingWonID)
If gettingwonidupper > 3 Then
GettingWonIDFoundtoSplit =
GettingWonID(5)
GettingWonID2 = Split
(GettingWonIDFoundtoSplit, ",")
WonID = Trim(GettingWonID2(0))
ParsedArray(3) = WonID
Else
GettingWonID = Split
(UnformattedLogLine, ",")
WonID = Trim(GettingWonID(2))
ParsedArray(3) = WonID
End If
Case 8
GettingIP = Split
(UnformattedLogLine, "IP:")
GettingIPFoundToSplit = GettingIP
(1)
GettingIP2 = Split
(GettingIPFoundToSplit, ":")
IPAddress = Trim(GettingIP2(0))
ParsedArray(4) = IPAddress
Case Else
End Select
GettingParsedValues = GettingParsedValues
+ 1
Loop

IncrementLineCount()
IncrementRow()
GettingParsedValues = 0
InsertingParsedValues = 0
Col = 0
Insert_to_database()

Loop

--------------------

I really would like some insight from anyone :) I will be
absolutely speachless to think my split code is causing
this kind of performance degredation especially when its
IDENTICAL to the previous version (under vb6).
So im pretty sure its my Database code. This is my first
ADO.net so be kind ;)
Click to expand...
 
Ah yeah thanks peeps. I also found that simply opening
and closing the database took what seemed like an age.

I commented all the insert code (with ') and uncommendted
DBconenction.open and .close just to see what would
happen.
It took about 20 seconds per file (each file contains
roughly 1000 records) simply to open and close. I havent
checked on what you said but the more i learn about the
ado.net technique i can see it makes obvious sense ;)

The adding the open before the whole process and the
close after the whole process has made the waiting time
drop dramaitcally. This should just about nail it down to
the previous time thanks for your help :)
 
Back
Top