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

Fyi marketable was meant to be MakeTable
On Dec 24, 2013 9:38 AM, "Rocky Smolin" <rockysmolin at bchacc.com> wrote:

> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>