[AccessD] ...building, managing, and using a page favorites list

Thanks AD.
Really interesting.  I don't suppose you have any sample code from these
tests, do you?

Max



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
Sent: 09 March 2009 05:30
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] ...building, managing, and using a page favorites
list

Max, Gustav, JC,

    For bulk inserts, action query is observed to be faster as compared to
pure recordset approach. The advantage becomes more pronounced with larger
data set. In fact, many orders of magnitude.

    In a test conducted for populating a five field table with 1,200,000
fresh records, the results were as follows (Access 2003 desktop on Win XP):

    (a) Using DAO recordset alone:                                  4605
seconds
    (b) Combination of action query and DAO recordset:    210 seconds

    With the above workload, method (b) using action query, comes out
approx. 23 times faster. The differential tends to increase with increase in
data size. On the other hand, if the number of records involved is very
small, the speed difference should not matter. 

Best wishes,
A.D. Tejpal
------------

  ----- Original Message ----- 
  From: Max Wanadoo 
  To: 'Access Developers discussion and problem solving' 
  Sent: Monday, March 09, 2009 00:14
  Subject: Re: [AccessD] ...building, managing, and using a page favorites
list


  Thanks Gustav, I guess it is the opening of the recordset which takes the
  time and by holding it open in a loop it will beat the sql statement every
  time. But nice to get this confirmed.

  As you say, for one record it makes no difference.  I *like* the sql
because
  it is just a one/two liner and not a block of code.

  Max


  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
  Sent: 08 March 2009 17:19
  To: accessd at databaseadvisors.com
  Subject: Re: [AccessD] ...building, managing, and using a page favorites
  list

  Hi Max

  No. Working with the recordset directly via DAO is always fastest - but
many
  forget or carry over their bad habits from VB or ASP where you see so much
  SQL command stuff and the like, because this once was the only - or at
least
  the easiest - method available in those environments.

  Of course, for a single record inserted, difference will not be big. But
can
  you keep the recordset open, one insert is very speedy.

  /gustav

  >>> max.wanadoo at gmail.com 08-03-2009 17:24:53 >>>
  > This speeds things up by keeping the recordset open the whole time.

  Would it be quicked to use an SQL statement ie,
  Sql = "Insert into tblAudit fields(x,y,z) values (a,b,c)
  Currentdb.execute(sql)

  Rather than opening recordsets.  I don't know, so i am really asking the
  question

  Thanks

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