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

Gustav Brock Gustav at cactus.dk
Mon Mar 9 01:49:56 CDT 2009


Hi A.D.

That is true, but what Max originally asked about was if DAO is faster than repeated calls of one line of SQL Insert .. Values (..).
Try to time that for 1,200,000 records (and prepare for lunch).

/gustav


>>> adtp at airtelmail.in 09-03-2009 06:29 >>>
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





More information about the AccessD mailing list