[AccessD] Speed up DAO processing

Jim Dettman jimdettman at earthlink.net
Thu Sep 4 09:02:47 CDT 2003


Lonnie,

<<I will attempt to use the SEEK method. I was avoiding it because of having
to do the indexing and my need for multiple criteria.>>

  As Erwin pointed out, there are constraints that come with SEEK that you
may not be able to live with, but if you can, it is by far the fastest
method of locating a record.

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: Thursday, September 04, 2003 9:53 AM
  To: Access Developers discussion and problem solving
  Subject: RE: [AccessD] Speed up DAO processing


  I sincerly thank all of you for your help. I will attempt to use the SEEK
method. I was avoiding it because of having to do the indexing and my need
for multiple criteria. Thanks Erwin for your input and yours as well Jim.

  I will be printing your mails to add to my folder of helpful tips.


  Jim Dettman <jimdettman at earthlink.net> wrote:
    Erwin,

    <<
    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.
    >>

      Although seek is unavailable with linked tables, it's a simple matter
to open the remote database and open the table directly:

    '
    ' First, get the path to the MDB for the attached table.
    '
      Set wrk = DBEngine.Workspaces(0)
      Set dbCurrent = wrk.Databases(0)
      Set tdfAttached = dbCurrent.TableDefs("tblGeoAllData")

      strPath = tdfAttached.Connect
      strPath = right$(strPath, Len(strPath) - InStr(strPath, "="))

      Set dbRemote = wrk.OpenDatabase(strPath, False, True)

    '
    '  First, find the postal code.  Need to know the country.
    '
      intHaveMatch = True

      Set rst1 = dbRemote.OpenRecordset("tblGeoAllData", DB_OPEN_TABLE)
      rst1.index = "PrimaryKey"
      rst1.Seek "=", CountryCode, PostalCode


    <<
    SEEK is at my knowledge not faster than findfirst, this was only true in
Access v2.
    >>

      Totally disagree.  Seek is consistently faster then any other method
for any version.

    <<
    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.
    >>

      It's documented in the MSKB that in order for compound indexes to be
used, the index must match the sequence of the criteria.  If not, the index
will not be used and a table scan be performed.  It's not a bug, just the
way it works.

    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 Erwin Craps
      Sent: Thursday, September 04, 2003 2:46 AM
      To: Access Developers discussion and problem solving
      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




  Lonnie Johnson
  ProDev, Professional Development of MS Access Databases
  Visit me at ==> http://www.prodev.us








----------------------------------------------------------------------------
--
  Do you Yahoo!?
  Yahoo! SiteBuilder - Free, easy-to-use web site design software
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030904/cc063471/attachment-0001.html>


More information about the AccessD mailing list