[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/483d775a/attachment-0001.html>