Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21

Thread: Performance Access vs SQL-2000

  1. #16
    Join Date
    Feb 2005
    Posts
    14
    Ofcource... DAO in local MSAccess database is the best (and fastest) way to working with recordsets.
    But there is one little moment - IMHO:
    using ADO for msaccess - masochism 'cause all querries in msacces using JetMachine and DAO using jet machine. And I think it's NOT coincidence.
    But when you want to connect to MSSQLServer...all my fellows recommends to use ADO for better data safe.

    PS: there is some fastes ways in MSAccess
    =========
    dim db as database
    set db = currentdb
    db.execute "select * from table"
    db.close
    =========

    is faster in "update, insert, delete and select" than

    =========
    docmd.runsql "select * from table"
    =========

    and a kind of mystery: dbOpenDynaset is faser than dbOpenSnapShot

  2. #17
    Join Date
    Jan 2005
    Location
    Germany
    Posts
    9
    This is what I found out too.

    DB_OPEN_DYNASET is quite a lot faster, than Snapshot.

    The only reason why I thought to change to ADO in MS-Access is because it would be a lot easier to make the application compatible both with MS-Access ans SQL server. For SQL Server DAO does not work at all, as far as I found out.

    This was the reason why I started to investigate in ADo with Access. But the performance is so poor, we cannot do this.

    Strange enough: Microsoft says, ADO is the model to be used in the future, also it should have a better performance than DAO (see http://www.microsoft.com/msj/0299/ado20/ado20.aspx)

    I don't know, what I should believe

  3. #18
    Join Date
    Feb 2005
    Posts
    14
    Are you still beliveing in Microsoft?
    If you want to make an interface in MSAccess to a MSSQLServer, then you should use ADO only for single work with recordset (not in cycle).
    I linked tables throgh the ODBC and worked with DAO<=>MSSQL nice and easy =)
    If you're good in SQL, the better way is to use dynamic querryes with parameters:
    ===============
    Dim Name as string
    Dim SurName as string
    db.execute "insert into TABLE1 select '" & Name & "' as Name, '" & SurName & "' as SurName"
    ===============
    And it would be much faster than work with recordset.
    There is another way - you can make a lots of Stored Procedures in MSSQL, and start them from ACCESS with ADO, it will not hang the client machine, it will hang Server on more than 30 connections =).
    All of it depends on business logic of your project.
    The main problem is to work ONLY with recordset, if the client does't have an ODBC-driver in a client machine, for examle...

  4. #19
    Join Date
    Feb 2005
    Posts
    14

    5 minutes ago I've made a test

    Table OrderNumber contains 50.000 records and linked from MSSQLServer 2000 though the ODBC, client is MSAccess'2000. As you see, there were tested DAO And ADODB.
    DAO - 25 sec.
    ADODB - 35 sec.



    Dim DB As Database
    Set DB = CurrentDb
    Dim RSol As DAO.Recordset
    'Dim RSol As ADOdb.Recordset
    'Set RSol = New ADOdb.Recordset
    SqlStr = " select * " & _
    " from OrderLine " & _
    " order by OrderNumber ASC, OrderType ASC, LineNumber ASC"
    Set RSol = DB.OpenRecordset(SqlStr, dbOpenDynaset, dbSeeChanges, dbPessimistic)
    'RSol.ActiveConnection = GetConnectionStringNow
    'RSol.Source = SqlStr
    'RSol.CursorType = adOpenDynamic
    'RSol.LockType = adLockPessimistic
    'RSol.Open
    'RSol.MoveFirst
    While Not RSol.EOF
    RSol.MoveNext
    Wend
    RSol.Close
    Set RSol = Nothing
    DB.Close


    if recordset creation will be:
    Set RSol = DB.OpenRecordset(SqlStr, dbOpenDynaset)
    then time in DAO will be 9 sec. =)

  5. #20
    Join Date
    Jan 2005
    Location
    Germany
    Posts
    9
    Hi, Veresk,

    thanks for the test. This is exactly what I got in my tests. So we better stick with DAO as long as possible, hoping, they will not make it die...

  6. #21
    Join Date
    Feb 2005
    Posts
    14
    But i've noticed one little problem: both DAO and ADODB do't support adLockPessimistic and automaticly switches to adLockOptimistic, that's bad, and i have to develop a special Lock Class to protect User's data from each other.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •