[AccessD] Speed up DAO processing

Lonnie Johnson prodevmg at yahoo.com
Thu Sep 4 10:46:05 CDT 2003


Thanks again.

I'm going to try the query process. I have a question. One of my issues is that Table1 where all the transactions go has many fields that get filled dynamically based on a field in the other tables like so...

Table1 has fields...
"Client", "2003R", "2003F", "2003P"

Table2 has...
"Client", "PE", "Amount"


Table2 Data

Client            PE       Amount
ABC          2003Rx       5
DEF          2003Px       6
XYZ           2003Fx       4

My update query should make Table1 look like...

Client    2003R     2003F     2003P
ABC         5
DEF                                      6
XYZ                      4

See I have to dynamically select the field that the AMOUNT is going into by whats in a field in the from table. I don't know how to dynamically determine the field name with an SQL statement. This is what I tried...

DoCmd.RunSQL "INSERT INTO Table1( Client, Left([PE],4))SELECT Client, Amount FROM Table2"

This is why I was using the recordset method because I could dynamically determine the field name with each iteration.

Any further advise would be appreciated.


Jim Dettman <jimdettman at earthlink.net> wrote: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_______________________________________________
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/9f4c0a31/attachment-0001.html>


More information about the AccessD mailing list