Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2008
    Posts
    5

    Unanswered: Serious problem with DAO performance in VBA

    I use VBA DAO to update my access db, but it's extremely slow. The following code took over half an hour to run which I think does not make sense. Does anyone know what might be the problem? Thank you.

    The table 'hist' has 100K rows.


    Sub da()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim fld As DAO.Field

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("hist", dbOpenDynaset)

    With rst
    Do While Not .EOF
    .Edit
    .Fields("tester") = Left(rst.Fields("ID"), 12)
    .Update
    .MoveNext
    Loop
    End With

    rst.Close
    dbs.Close
    Set rst = Nothing
    Set dbs = Nothing

    End Sub

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Are the 100K rows stored in SQL Server or native Access tables... if native access tables, where are those tables, stored on an overloaded file server? I often find that performance issues are more to do with the environment rather than within Access or its coding.

    However, I'd remove a couple of little things from that code (greyed out):

    Sub da()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim fld As DAO.Field

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("hist", dbOpenDynaset)

    With rst
    Do While Not .EOF
    .Edit
    .Fields("tester") = Left(rst.Fields("ID"), 12)
    .Update
    .MoveNext
    Loop
    End With

    rst.Close
    dbs.Close
    Set rst = Nothing
    Set dbs = Nothing

    End Sub
    I'd also consider removing the DAO. from those Dim statements and see what effect it has on performance.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by StarTrekker
    I'd also consider removing the DAO. from those Dim statements and see what effect it has on performance.
    Keep them - it actually speeds stuff up and can result in problems if ADO is referenced too.

    Why do this with a cursor? Set based all the way surely?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Are you just updating every record to set the id to the first 12 characters of the id field?

    If so just execute this against the connection instead of using a loop.
    Code:
    UPDATE <tablename>
    SET    id = Left(id, 12)
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    i.e.:
    Code:
    currentdb.execute "UPDATE hist
    SET tester =  Left(ID, 12)", dbfailonerror
    Or, like, derive this at run time.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Damn - forgottened to refresh
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    This is probably not the problem but I wanted to throw "indexing" fields in the table out there to possibly help performance. It usually helps speed of code like you have but again, it's most likely not causing the extremely slow updating process which StarTrekker, Georgev, and Pootle Flump have better suggestions on addressing.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i really **HATE** the subject of this post.

    i have a 87k-record VBA/DAO/JET db that searches Outlook for update mails and UPDATEes/INSERTs to the db: according to the log, today's run involved three mails containing 3180 updates and 509 inserts.
    the code handles create late bind Outlook automation, mails selection/validation (sender, subject and attachment name) attached .CSV validation (field count/name/sequence), import to temp, UPDATE/INSERT to main tables, re-subject and delete handled mail, and logs the whole can of beans to a text file...
    ...the log tells me that today the whole process took < 3 seconds.

    DAO/JET is SLOW ????????? bah!

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    True izyrider. I used DAO (and ADO) to update millions of records (looping through the records) and wouldn't say that DAO itself is slow (although I did get a little bit better performance using ADO). My guess is that there's something else causing the slowness of the original post.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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