[AccessD] Speed up DAO processing

BerichtErwin:

I've used RecordsetClone and Bookmark in DAO to advantage sometimes but don't know about the speed.  Do you know?

Rocky

  ----- Original Message ----- 
  From: Erwin Craps 
  To: Access Developers discussion and problem solving 
  Sent: Wednesday, September 03, 2003 11:45 PM
  Subject: RE: [AccessD] Speed up DAO processing


    OK see the prob...
    I had some speed issues to when comparing 10 or even 100 thousand records.
    There is a thing with findfirst that I believe is a bug in A2K, don't know for A97 or AXP.


    SEEK is not usaeable with linked tables. SEEK u can use only one field.
    SEEK is at my knowledge not faster than findfirst, this was only true in Access v2.

    The problem with findfirst lies in the AND criteria.
    For some reasons findfirst is terribly slow when using more than one field parameter.
    Even when having indexes on both fields or a joint field index. I believe this is a bug.

    so don't use multiple citeria in findfirst.
    Use  SQL strings in a loop with criteria and use only one in findfirst. This is the fastest you will get if you really need line-by-line processing. If you can avoid using the ORDER BY parameter in your SQL string this will speed thing even more.
    (you gonna have a lot of SQL queries in this loop)

    Rethink if you cannot do your thing only with queries. this is always faster.
    I see however the prob with this compare and sometimes queries are not updateable depending on the data.
    Please put some indexes or the criteria fields.

    Another remark thats important when reading/editting large quantities of records.

     rsP!AMOUNT=123 is slower than  rsP(AMOUNT)=123
    rsp.fields(AMOUNT)=123 is same speed as rsP(AMOUNT)=123

    stop using the ! parameter, it is outdated and slows down. There is only one situation ! is still need and I believe in a form or report referencing somewhere...

    Erwin 






      -----Oorspronkelijk bericht-----
      Van: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] Namens Jim Dettman
      Verzonden: woensdag 3 september 2003 20:09
      Aan: Access Developers discussion and problem solving
      Onderwerp: RE: [AccessD] Speed up DAO processing


      Lonnie,

        Seek is the fastest way to go.  Outside of that, it sounds like you should be able to do this just with bulk SQL Inserts/updates, which would be even faster.

      Jim Dettman
      President,
      Online Computer Services of WNY, Inc.
      (315) 699-3443
      jimdettman at earthlink.net 
        -----Original Message-----
        From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Lonnie Johnson
        Sent: Wednesday, September 03, 2003 2:02 PM
        To: 'MS-ACCESS-L at lists.missouri.edu'; AccessDevelopers; ms_access; AccessD solving'
        Subject: [AccessD] Speed up DAO processing


        I have four tables.

        Table1 is where all the records end up. It starts out empty.

        1. I take all transactions from Table2 and append them into Table1.

        2. I then check each record in Table3 and see if there is a corresponding record in Table1 based on two fields. If so, then I edit the existing record in Table1. If not, I add it.

        3. I the do the same as in step 2 for Table4.

        I am using DAO recordset processing and use the RS.FindFirst method to see if records from Table3 or 4 are in Table1. 

        It appears that this FindFirst method is greatly slowing my process. Is there a faster way. I pasted my code below if anyone wants to see it.

        Option Compare Database
            Dim dbCurr As DAO.Database
            
            Dim rsD As DAO.Recordset
                Dim rsF As DAO.Recordset
                    Dim rsP As DAO.Recordset
                        Dim rsR As DAO.Recordset
                        
            Dim fldName As String
            
            Public Sub TestRun()
               
                Call uTData("t")
                
            End Sub
        Public Sub uTData(objType As String)
                
                Dim passVar As String
                
                Set dbCurr = CurrentDb
                        
                'Clear the old values
                DoCmd.SetWarnings False
                    DoCmd.RunSQL "DELETE * FROM t_DATA"
                        DoCmd.SetWarnings True
                        
               If objType = "t" Then
               passVar = "T_Data_Files"
               Else
               passVar = "Q_Data_Files"



------------------------------------------------------------------------------


  _______________________________________________
  AccessD mailing list
  AccessD at databaseadvisors.com
  http://databaseadvisors.com/mailman/listinfo/accessd
  Website: http://www.databaseadvisors.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030904/b71a692b/attachment-0001.html>