DAO/VB6 Outperforms ADO.NET in VB 2005

  • Thread starter Thread starter Paul Engel
  • Start date Start date
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()
 
For an Access/Jet application, DAO is almost always going to be more
performant. It's optimized for and runs inside the Jet engine. Once
you move data operations outside of Jet, you're working with
additional API layers (OleDb + VB code). Plus, you're layering
DataSets on top of that. And then you're using formatting functions in
your VB SQL statements (which need to execute for each and every row).
All of those things are going to bog down performance. No matter what
database engine you are using, performing data operations only on the
server is going to give you the best performance.

--Mary
 
I totally agree with Mary (as usual). Comparing Access/JET's ISAM DLL-based
DBMS with SQL Server's service-based approach is like comparing a bicycle
with a 3/4 ton truck. If you want to deliver pizza around town, the bike can
do just fine (if there aren't too many hills).

This has been discussed a thousand times over the last decade or so--we've
always given (about) the same answer. DAO and JET are fast but it's fragile.
JET does not scale, it does not log each operation, it is not as secure as
SQL Server (or SS Compact), it is no longer being updated or improved or
fixed by MS. While it's in wide use, many (if not most) serious
organizations are spending a lot of money purging it from their systems.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
 
Paul,

Before you make your statement I would in your case verify my program.

Is that opening and closing of the datareadere necessary for every record
Am I sure I am using the commands right. I am almost sure you have a wrong
idea of accept changes.
What it does is setting all rowstates to unchanged, what means that it does
not do any update anymore.
So what you see is probably your original database, because nothing is
changed. And than is 18 seconds a lot of time to do that process.

Cor
 
Bill,

It seems that Paul is working on a heavy traffic city to deliver his pizza
because his Dao bike seems fine. Why do you want him to use a truck in that
city.

(Although I think as well that he better can switch, it is just your sample)

Cor
 
I agree. The work I am doing is against small, portable databases that are
used by our imaging capture vendor. They are designed to be transient and
backward compatible. Once the work is delivered, it is absorbed into an
enterprise SQL Server instance.

It looks like my best bet, even though I like using the new tools, is to
just reference the old COM DAO 3.51 in my spiffy, new .Net software and do
the work using that tool. I have no problem with that.

Thanks for all the input. (Sorry for the delay...I've been on vacation.)

Regards,
Paul
 
I have accepted that I need to just reference the DAO COM object in my
project. I know this is the ADO.NET list, but maybe someone can tell me why
this is happening.



I have set the reference and declared my variables:

Dim objAccessDB As DAO.Database

Dim objAccessRS As DAO.Recordset

Dim objDBEngine As New DAO.DBEngine



I set my Database object:

objAccessDB = objDBEngine.OpenDatabase(My.Settings.DataSource)



I populate my Recordset object:

strSQLStatement = "SELECT GroupNumber, GroupName " & _

"FROM tblGroups " & _

"Where GroupNumber = """ & Values(3) & """"



objAccessRS = objAccessDB.OpenRecordset(strSQLStatement, 2)
'dbOpenDynaset



Then I attempt to update or add records:

With objAccessRS

If .BOF And .EOF Then

.AddNew()

.Fields("GroupNumber") = Values(3) *****ERROR
IN IDE*******

.Fields("GroupName") = UCase(Values(4)) *****ERROR
IN IDE*******

.Update()



Else

.Edit()

.Fields("GroupName") = UCase(Values(4)) *****ERROR
IN IDE*******

.Update()

End If

End With



On the lines above w/ the ERROR IN IDE message (typed by me), I am getting
the error highlighting. The error is:

Property 'Item' is 'ReadOnly'



Any idea why the IDE would be protesting this? I have not tried to run this
w/ the errors...so I'm just going by the error highlighting to indicate that
something is wrong.



Paul
 
This has been resolved. Turns out the .Net implementation does not support
the default of the DB.Fields() collection's ".Value". It must be explicitly
included. DUH!
 
Back
Top