[AccessD] Which is faster DAO or Append Query? (in this case)

Heenan, Lambert Lambert.Heenan at aig.com
Thu Dec 19 12:33:18 CST 2013


"I'm inclined"
     --------

-----Original Message-----
From: Heenan, Lambert 
Sent: Thursday, December 19, 2013 1:33 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Which is faster DAO or Append Query? (in this case)

Rocky,

I'm included to say 'it depends'. Depends on the kind of queries, and the nature of the back end.

For instance, with an Access back end (mdb or accdb) I find that union queries of large tables tend to be quite slow to run. So I wind up doing a series of append queries into a temporary table and then use a query based on the temporary table. The same is true for an Oracle Back end. The cause is that a complete table scan is needed for union queries as they are implicitly "SELECT DISTINCT ..." queries.

With an Oracle back end and large tables if you do...

Set Rs = CurrentDb.OpenRecordset("Select * from SomeTable",DbOpenDynaset) Rs.FindFirst "SomeField=" & SomeValue

... then the findfirst can take a very long time indeed, but with...

Set Rs = CurrentDb.OpenRecordset("Select * from SomeTable Where 'SomeField=' & SomeValue
",DbOpenDynaset)

... then the recordset object comes back almost instantly.

HTH

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Thursday, December 19, 2013 11:16 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Which is faster DAO or Append Query? (in this case)

Dear List:
 
I have a form which is bound to a temp table.  When the form is opened, the temp table records are deleted and re-created with an append query - two tables and two queries linked up.  It appends 36 fields.
 
The user reports 5-10 seconds delay opening the form and it is due, AFAICS, to the append query.
 
I have always done a lot of stuff in DAO and find it to be easy to write and fast in its operation.  So I could do this in DAO but would it be any faster?
 
MTIA
 
Rocky
 



More information about the AccessD mailing list