P
Paul Engel
I have an application that reads through a delimited file, breaks the values
into an array and then checks to see if the record already exists in the
database. If it does, the record is updated. If not, the record is added.
Both of these actions are based on the values of the associated array
values.
I have this written in VB6 using DAO 3.51. I have just rewritten it in VB
2005. The program works against an Access 97 database with 73,700 records. I
am running this on a sample file with 201 lines. 2 are updates, 199 are new.
When I run it VB6, it takes under a second to run. I have included below the
VB6 code for the update procedure.
In VB 2005, I have written it two ways to try to get better performance. In
both cases, I have a Class called WDABase, which encapsulates the creation
of all the ADO.Net objects. The first was to use SQL statements. I do a
SELECT to generate a DataReader. If it has rows, I do the update via an
UPDATE SQL command. Otherwise, I send an INSERT SQL command. The process
takes 21 seconds on the same sample data. The good news is that it at least
updates the database properly. In this case, I am using very similar logic
to the VB6 DAO code, but it is taking over 21X as long to run. (This code is
listed below, as well.)
Finally, thinking I could shave time off the 21 seconds, I decided to use a
DataSet/DataView scenario...load the entire table into the DataView, conduct
FindRows, then either update or add records. This, too is slow, at 18
seconds. BUT, in debug mode, before calling the AcceptChanges(), I check the
value in my objDataView1's Tables(0).Rows.Count, it says that there are
73,899, which is RIGHT...but when the program ends, my Access database still
only has 73,700.
Anyone have any ideas on either the performance OR the inability of the
DataSet/DataView version to update the values in my database?
Thank,
Paul
-----------------VB6 Code------------------------
Public Sub subUpdateSubscriber(ByVal arrFieldsIn)
Dim strSQLStatement As String
Dim intFieldCounter As Integer
On Error GoTo subUpdateSubscriber_Error
'If the Account number is already in the base database, don't add it
strSQLStatement = "SELECT SubscriberID, SSN, MemberName,
GroupNumber, GroupName " & _
"FROM tblSubscriber " & _
"Where SubscriberID = """ & arrFieldsIn(0) & """" & _
"and GroupNumber = """ & arrFieldsIn(3) & """"
Set myRSMerge = myDB.OpenRecordset(strSQLStatement, dbOpenDynaset)
With myRSMerge
If .BOF And .EOF Then
'Add the new record
.AddNew
.Fields("SubscriberID") = arrFieldsIn(0)
.Fields("SSN") = arrFieldsIn(1)
.Fields("MemberName") = UCase(arrFieldsIn(2))
.Fields("GroupNumber") = arrFieldsIn(3)
.Fields("GroupName") = UCase(arrFieldsIn(4))
.Update
Else
'Update the record
.Edit
.Fields("SSN") = arrFieldsIn(1)
.Fields("MemberName") = UCase(arrFieldsIn(2))
.Fields("GroupName") = UCase(arrFieldsIn(4))
.Update
End If
End With
' Close the test recordset
myRSMerge.Close
Set myRSMerge = Nothing
On Error GoTo 0
Exit Sub
------------End VB6 Code---------------
----------------VB 2005 Using SQL Statements------------------
'Initialize a new instance of the data access base clase
Using objAccessDB As New WDABase
Try
objAccessDB.OpenConnection()
'Get all Subscribers in a DataReader object
objAccessDB.SQL = "SELECT SubscriberID, GroupNumber " & _
"FROM tblSubscriber " & _
"Where SubscriberID = """ & Values(0) & """" & _
" and GroupNumber = """ & Values(3) & """"
objAccessDB.InitializeCommand()
objAccessDB.DataReader = objAccessDB.Command.ExecuteReader
'See if any data exists before continuting
If objAccessDB.DataReader.HasRows Then
objAccessDB.DataReader.Close()
objAccessDB.DataReader = Nothing
'Execute the UPDATE statement on existing record
objAccessDB.SQL = "UPDATE tblSubscriber SET SSN = """ &
Values(1) & _
""", MemberName = """ & Values(2).ToUpper & """,
GroupName = """ & _
Values(4).ToUpper & """ " & _
"Where SubscriberID = """ & Values(0) & """" & _
" and GroupNumber = """ & Values(3) & """"
objAccessDB.Command = Nothing
objAccessDB.InitializeCommand()
objAccessDB.Command.ExecuteNonQuery()
Else
objAccessDB.DataReader.Close()
objAccessDB.DataReader = Nothing
objAccessDB.Command = Nothing
'Execute and Insert statement to add
objAccessDB.SQL = "INSERT INTO tblSubscriber" & vbCrLf &
"(SubscriberID, " & _
"SSN, MemberName, GroupNumber, GroupName) " & vbCrLf
& _
"VALUES" & vbCrLf & "(""" & Values(0) & """, """ & _
Values(1) & """, """ & _
Values(2).ToUpper & """, """ & _
Values(3) & """, """ & _
Values(4).ToUpper & """)"
objAccessDB.InitializeCommand()
objAccessDB.Command.ExecuteNonQuery()
End If
objAccessDB.Command = Nothing
'Close the database connection
objAccessDB.CloseConnection()
Catch ExceptionErr As Exception
MessageBox.Show(ExceptionErr.Message)
End Try
End Using
----------------End if VB 2005 using SQL Statement Code-----------------
----------------Begin VB 2005 using DataSet------------------------------
'******Since I am calling the procedure to load
Private Sub UpdateMergeDB(ByRef Values As String())
Try
'Find this item's row(s)
Dim SearchValues As Object() = {Values(0), Values(3)}
Dim FoundRows As DataRowView() =
objDBView1.FindRows(SearchValues)
If FoundRows.Length = 0 Then
'There were no hits, add this row
Dim NewRow As DataRowView = objDBView1.AddNew
NewRow(0) = Values(0)
NewRow(1) = Values(1)
NewRow(2) = Values(2).ToUpper
NewRow(3) = Values(4)
NewRow(4) = Values(4).ToUpper
NewRow.EndEdit()
Else
'There were hits, loop them and modify the values
For Each RowToUpdate As DataRowView In FoundRows
RowToUpdate.BeginEdit()
RowToUpdate(1) = Values(1)
RowToUpdate(2) = Values(2).ToUpper
RowToUpdate(4) = Values(4).ToUpper
RowToUpdate.EndEdit()
Next
End If
Catch ExceptionErr As Exception
MessageBox.Show(ExceptionErr.Message)
End Try
End Sub
' AFTER THIS...in the calling procedure, I attempt to update the dataset:
'Clear Dataview
objDataSet.AcceptChanges()
objDBView1 = Nothing
objDBView2 = Nothing
objAccessDB.CloseConnection()
into an array and then checks to see if the record already exists in the
database. If it does, the record is updated. If not, the record is added.
Both of these actions are based on the values of the associated array
values.
I have this written in VB6 using DAO 3.51. I have just rewritten it in VB
2005. The program works against an Access 97 database with 73,700 records. I
am running this on a sample file with 201 lines. 2 are updates, 199 are new.
When I run it VB6, it takes under a second to run. I have included below the
VB6 code for the update procedure.
In VB 2005, I have written it two ways to try to get better performance. In
both cases, I have a Class called WDABase, which encapsulates the creation
of all the ADO.Net objects. The first was to use SQL statements. I do a
SELECT to generate a DataReader. If it has rows, I do the update via an
UPDATE SQL command. Otherwise, I send an INSERT SQL command. The process
takes 21 seconds on the same sample data. The good news is that it at least
updates the database properly. In this case, I am using very similar logic
to the VB6 DAO code, but it is taking over 21X as long to run. (This code is
listed below, as well.)
Finally, thinking I could shave time off the 21 seconds, I decided to use a
DataSet/DataView scenario...load the entire table into the DataView, conduct
FindRows, then either update or add records. This, too is slow, at 18
seconds. BUT, in debug mode, before calling the AcceptChanges(), I check the
value in my objDataView1's Tables(0).Rows.Count, it says that there are
73,899, which is RIGHT...but when the program ends, my Access database still
only has 73,700.
Anyone have any ideas on either the performance OR the inability of the
DataSet/DataView version to update the values in my database?
Thank,
Paul
-----------------VB6 Code------------------------
Public Sub subUpdateSubscriber(ByVal arrFieldsIn)
Dim strSQLStatement As String
Dim intFieldCounter As Integer
On Error GoTo subUpdateSubscriber_Error
'If the Account number is already in the base database, don't add it
strSQLStatement = "SELECT SubscriberID, SSN, MemberName,
GroupNumber, GroupName " & _
"FROM tblSubscriber " & _
"Where SubscriberID = """ & arrFieldsIn(0) & """" & _
"and GroupNumber = """ & arrFieldsIn(3) & """"
Set myRSMerge = myDB.OpenRecordset(strSQLStatement, dbOpenDynaset)
With myRSMerge
If .BOF And .EOF Then
'Add the new record
.AddNew
.Fields("SubscriberID") = arrFieldsIn(0)
.Fields("SSN") = arrFieldsIn(1)
.Fields("MemberName") = UCase(arrFieldsIn(2))
.Fields("GroupNumber") = arrFieldsIn(3)
.Fields("GroupName") = UCase(arrFieldsIn(4))
.Update
Else
'Update the record
.Edit
.Fields("SSN") = arrFieldsIn(1)
.Fields("MemberName") = UCase(arrFieldsIn(2))
.Fields("GroupName") = UCase(arrFieldsIn(4))
.Update
End If
End With
' Close the test recordset
myRSMerge.Close
Set myRSMerge = Nothing
On Error GoTo 0
Exit Sub
------------End VB6 Code---------------
----------------VB 2005 Using SQL Statements------------------
'Initialize a new instance of the data access base clase
Using objAccessDB As New WDABase
Try
objAccessDB.OpenConnection()
'Get all Subscribers in a DataReader object
objAccessDB.SQL = "SELECT SubscriberID, GroupNumber " & _
"FROM tblSubscriber " & _
"Where SubscriberID = """ & Values(0) & """" & _
" and GroupNumber = """ & Values(3) & """"
objAccessDB.InitializeCommand()
objAccessDB.DataReader = objAccessDB.Command.ExecuteReader
'See if any data exists before continuting
If objAccessDB.DataReader.HasRows Then
objAccessDB.DataReader.Close()
objAccessDB.DataReader = Nothing
'Execute the UPDATE statement on existing record
objAccessDB.SQL = "UPDATE tblSubscriber SET SSN = """ &
Values(1) & _
""", MemberName = """ & Values(2).ToUpper & """,
GroupName = """ & _
Values(4).ToUpper & """ " & _
"Where SubscriberID = """ & Values(0) & """" & _
" and GroupNumber = """ & Values(3) & """"
objAccessDB.Command = Nothing
objAccessDB.InitializeCommand()
objAccessDB.Command.ExecuteNonQuery()
Else
objAccessDB.DataReader.Close()
objAccessDB.DataReader = Nothing
objAccessDB.Command = Nothing
'Execute and Insert statement to add
objAccessDB.SQL = "INSERT INTO tblSubscriber" & vbCrLf &
"(SubscriberID, " & _
"SSN, MemberName, GroupNumber, GroupName) " & vbCrLf
& _
"VALUES" & vbCrLf & "(""" & Values(0) & """, """ & _
Values(1) & """, """ & _
Values(2).ToUpper & """, """ & _
Values(3) & """, """ & _
Values(4).ToUpper & """)"
objAccessDB.InitializeCommand()
objAccessDB.Command.ExecuteNonQuery()
End If
objAccessDB.Command = Nothing
'Close the database connection
objAccessDB.CloseConnection()
Catch ExceptionErr As Exception
MessageBox.Show(ExceptionErr.Message)
End Try
End Using
----------------End if VB 2005 using SQL Statement Code-----------------
----------------Begin VB 2005 using DataSet------------------------------
'******Since I am calling the procedure to load
Private Sub UpdateMergeDB(ByRef Values As String())
Try
'Find this item's row(s)
Dim SearchValues As Object() = {Values(0), Values(3)}
Dim FoundRows As DataRowView() =
objDBView1.FindRows(SearchValues)
If FoundRows.Length = 0 Then
'There were no hits, add this row
Dim NewRow As DataRowView = objDBView1.AddNew
NewRow(0) = Values(0)
NewRow(1) = Values(1)
NewRow(2) = Values(2).ToUpper
NewRow(3) = Values(4)
NewRow(4) = Values(4).ToUpper
NewRow.EndEdit()
Else
'There were hits, loop them and modify the values
For Each RowToUpdate As DataRowView In FoundRows
RowToUpdate.BeginEdit()
RowToUpdate(1) = Values(1)
RowToUpdate(2) = Values(2).ToUpper
RowToUpdate(4) = Values(4).ToUpper
RowToUpdate.EndEdit()
Next
End If
Catch ExceptionErr As Exception
MessageBox.Show(ExceptionErr.Message)
End Try
End Sub
' AFTER THIS...in the calling procedure, I attempt to update the dataset:
'Clear Dataview
objDataSet.AcceptChanges()
objDBView1 = Nothing
objDBView2 = Nothing
objAccessDB.CloseConnection()