<!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><SPAN class=740323211-04092003><SPAN class=679415605-04092003><SPAN
class=740323211-04092003>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV>
<DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN class=426442012-04092003>Mea
culpa, SEEK is a lot faster than
findfirst.</SPAN></FONT></FONT></FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN class=426442012-04092003>I
just tested this in a almost 1,000,000 records containing table in
AXP.</SPAN></FONT></FONT></FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=426442012-04092003></SPAN></FONT></FONT></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=426442012-04092003>findfirst'ing 100 records took average 18
seconds.</SPAN></FONT></FONT></FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=426442012-04092003>Seek'ing the same 100 records took average 0,7
seconds...</SPAN></FONT></FONT></FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=426442012-04092003></SPAN></FONT></FONT></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN class=426442012-04092003>and
for fun I tested SQL to (JET), build a sql string, open recordset, close
recordset 100 times same records as
above.</SPAN></FONT></FONT></FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=426442012-04092003>SQL'ling the same 100 records took average 13
seconds.</SPAN></FONT></FONT></FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=426442012-04092003></SPAN></FONT></FONT></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN class=426442012-04092003>I
did tought I tested seek, but I supose that I ignored seek because it can
only be used in JET and not with
MSSQL.</SPAN></FONT></FONT></FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=426442012-04092003>further more it does not support multiple
criteria.</SPAN></FONT></FONT></FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN class=426442012-04092003>and
you need an index on it.</SPAN></FONT></FONT></FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=426442012-04092003></SPAN></FONT></FONT></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN class=426442012-04092003>I
preparing myself for years now to migrate to SQL server, if I start using
newfunctions/ techniques they MUST be compliant for use with SQL
server.</SPAN></FONT></FONT></FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=426442012-04092003></SPAN></FONT></FONT></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN class=426442012-04092003>Mea
Culpa again....</SPAN></FONT></FONT></FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=426442012-04092003></SPAN></FONT></FONT></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=426442012-04092003></SPAN></FONT></FONT></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=426442012-04092003> </SPAN></FONT></FONT></FONT></SPAN></SPAN></DIV></SPAN></SPAN></DIV>
<DIV></SPAN><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></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<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>Erwin
Craps<BR><B>Sent:</B> Thursday, September 04, 2003 2:46 AM<BR><B>To:</B>
Access Developers discussion and problem solving<BR><B>Subject:</B> RE:
[AccessD] Speed up DAO processing<BR><BR></FONT></DIV>
<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></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>