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

Good idea.  Turns out there were 9 indexes (duplicates OK) in the 36 fields.
So I deleted them all and will upload an update to the user to see if that
speeds up the append.

Thanks

Rocky
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Benson
Sent: Tuesday, December 24, 2013 5:37 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Which is faster DAO or Append Query? (in this case)

I have always found that indexed tables are slow to insert records into, but
since you say that this is a temporary  table that should not be the issue
unless... your reference to deleting and appending records refers to a semi
permanent table, and more like temporary *records* than a temporary table.

You know that the way one usually tests these things is to set up a variety
of conditions to see where the most bottle neck or overhead is.

This kind of question used to consume me.

I think a lot of developers go the temp table route but I guess I would have
to ask is there a reason to delete and reappend or can you turn off the
recordsource, (and warnings) delete the object, do a marketable turn back on
warnings,  and reassign the recordsource in less time? I suppose it would
depend if your queries made a lot or few changes. Certainly this is easy to
test best with extremely small or large data sets and changes.
Again, watch our for indexes on the table... if they not needed, don't use
them.

I also don't pretend to be any good at access programming anymore.
On Dec 24, 2013 6:36 AM, "Steve Schapel" <steve at datamanagementsolutions.biz>
wrote:

> Hi Rocky
>
> How are you running the Delete and Append queries?
>
> Do either of the queries that are behind your append query contain 
> user-defined functions or nested domain aggregate functions or 
> anything that causes them to run slowly?  If you change your append 
> query to a select query, and run it, do you notice any sluggishness as 
> the datasheet loads your records?
>
> Regards
> Steve
>
> -----Original Message----- From: Rocky Smolin
> Sent: Friday, December 20, 2013 5: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
>
>
>
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com