Jim Dettman
jimdettman at earthlink.net
Thu Sep 4 06:43:31 CDT 2003
BerichtErwin,
<<
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"
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030904/2d95cee0/attachment-0001.html>