[AccessD] Speed up DAO processing

Thu Sep 4 08:48:32 CDT 2003

You have lost me know...
Why would you use recordsetclone and bookmarks to do a bulk update?

Recordsetclone is a form functionality, dont see the point using that
unless you update your tables based on 1 record in that form.
Are you dooing this update for data based on 1 main record in your form?

Please explane what you are trying to achieve in non-technical words.

Erwin

	-----Original Message-----
	From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
- Beach Access Software
	Sent: Thursday, September 04, 2003 3:27 PM
	To: Access Developers discussion and problem solving
	Subject: Re: [AccessD] Speed up DAO processing

	Erwin:

	I've used RecordsetClone and Bookmark in DAO to advantage
sometimes but don't know about the speed.  Do you know?

	Rocky

		----- Original Message ----- 
		From: Erwin Craps <mailto:Erwin.Craps at ithelps.be>  
		To: Access Developers discussion and problem solving
<mailto:accessd at databaseadvisors.com>  
		Sent: Wednesday, September 03, 2003 11:45 PM
		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"

  _____  

		_______________________________________________
		AccessD mailing list
		AccessD at databaseadvisors.com
		http://databaseadvisors.com/mailman/listinfo/accessd
		Website: http://www.databaseadvisors.com

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030904/84ca347b/attachment-0001.html>