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>