<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Bericht</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2800.1226" name=GENERATOR></HEAD>
<BODY>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff size=2>OK
see the prob...</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff size=2>I
had some speed issues to when comparing 10 or even 100 thousand
records.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>There is a thing with findfirst that I believe is a bug in A2K, don't
know for A97 or AXP.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff size=2>SEEK
is not usaeable with linked tables. SEEK u can use only one
field.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff size=2>SEEK
is at my knowledge not faster than findfirst, this was only true in Access
v2.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff size=2>The
problem with findfirst lies in the AND criteria.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff size=2>For
some reasons findfirst is terribly slow when using more than one field
parameter.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff size=2>Even
when having indexes on both fields or a joint field index. I believe this
is a bug.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff size=2>so
don't use multiple citeria in findfirst.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff size=2>(you
gonna have a lot of SQL queries in this loop)</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>Rethink if you cannot do your thing only with queries. this is always
faster.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff size=2>I
see however the prob with this compare and sometimes queries are not
updateable depending on the data.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>Please put some indexes or the criteria fields.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>Another remark thats important when reading/editting large quantities
of records.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003> rsP!AMOUNT=123 is slower
than rsP(AMOUNT)=123</SPAN></DIV>
<DIV><SPAN class=679415605-04092003>rsp.fields(AMOUNT)=123 is same speed as
rsP(AMOUNT)=123</SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff size=2>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...</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>Erwin</FONT> </SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=nl dir=ltr align=left><FONT face=Tahoma
size=2>-----Oorspronkelijk bericht-----<BR><B>Van:</B>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com] <B>Namens </B>Jim
Dettman<BR><B>Verzonden:</B> woensdag 3 september 2003 20:09<BR><B>Aan:</B>
Access Developers discussion and problem solving<BR><B>Onderwerp:</B> RE:
[AccessD] Speed up DAO processing<BR><BR></FONT></DIV>
<DIV><SPAN class=200040718-03092003><FONT face=Arial color=#0000ff
size=2>Lonnie,</FONT></SPAN></DIV>
<DIV><SPAN class=200040718-03092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=200040718-03092003><FONT face=Arial color=#0000ff
size=2> 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.</FONT></SPAN></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2>Jim Dettman<BR>President,<BR>Online Computer Services of
WNY, Inc.<BR>(315) 699-3443<BR>jimdettman@earthlink.net </FONT></DIV>
<BLOCKQUOTE>
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com]<B>On Behalf Of </B>Lonnie
Johnson<BR><B>Sent:</B> Wednesday, September 03, 2003 2:02
PM<BR><B>To:</B> 'MS-ACCESS-L@lists.missouri.edu'; AccessDevelopers;
ms_access; AccessD solving'<BR><B>Subject:</B> [AccessD] Speed up DAO
processing<BR><BR></FONT></DIV>
<DIV>I have four tables.</DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV>Table1 is where all the records end up. It starts out
empty.</DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV>1. I take all transactions from Table2 and append them into
Table1.</DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV>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.</DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV>3. I the do the same as in step 2 for Table4.</DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV>I am using DAO recordset processing and use the RS.FindFirst method
to see if records from Table3 or 4 are in Table1. </DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV>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.</DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV>Option Compare Database</DIV>
<DIV> Dim dbCurr As DAO.Database<BR>
<BR> Dim rsD As
DAO.Recordset<BR> Dim rsF As
DAO.Recordset<BR>
Dim rsP As
DAO.Recordset<BR>
Dim rsR As
DAO.Recordset<BR>
<BR> Dim fldName As String<BR>
<BR> Public Sub
TestRun()<BR>
<BR> Call
uTData("t")<BR>
<BR> End Sub</DIV>
<DIV>Public Sub uTData(objType As
String)<BR>
<BR> Dim passVar As
String<BR>
<BR> Set dbCurr =
CurrentDb<BR>
<BR> 'Clear the old
values<BR> DoCmd.SetWarnings
False<BR>
DoCmd.RunSQL "DELETE * FROM
t_DATA"<BR>
DoCmd.SetWarnings
True<BR>
<BR> If objType = "t"
Then<BR> passVar =
"T_Data_Files"<BR>
Else<BR> passVar =
"Q_Data_Files"<BR></DIV></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>