Paul Engel
7/2/2007 6:58:00 PM
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
"William (Bill) Vaughn" <billvaRemoveThis@betav.com> wrote in message
news:uQs45tOtHHA.3364@TK2MSFTNGP02.phx.gbl...
>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)
> -----------------------------------------------------------------------------------------------------------------------
>
> "Mary Chipman [MSFT]" <mchip@online.microsoft.com> wrote in message
> news:hbtn73d6ijdrc5kiv9ln2dt9ikv9s376ql@4ax.com...
>> 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
>>
>> On Wed, 20 Jun 2007 13:24:04 -0400, "Paul Engel"
>> <paul.engel@vebridge.com> wrote:
>>
>>>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()
>>>
>
>