[AccessD] Speed up DAO processing

	Mea culpa, SEEK is a lot faster than findfirst.
	I just tested this in a almost 1,000,000 records containing
table in AXP.
	 
	findfirst'ing 100 records took average 18 seconds.
	Seek'ing the same 100 records took average 0,7 seconds...
	 
	and for fun I tested SQL to (JET), build a sql string, open
recordset, close recordset 100 times same records as above.
	SQL'ling the same 100 records took average 13 seconds.
	 
	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.
	further more it does not support multiple criteria.
	and you need an index on it.
	 
	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.
	 
	Mea Culpa again....
	 
	 
	 
	 
	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/7b7cb1fa/attachment-0001.html>