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

Gustav,

    I agree. Comparative advantage offered by action query gets realized mainly if it can be done in such a manner as to insert multiple records in target table through each execution. 

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

  ----- Original Message ----- 
  From: Gustav Brock 
  To: accessd at databaseadvisors.com 
  Sent: Monday, March 09, 2009 12:19
  Subject: Re: [AccessD] ...building, managing, and using a page favorites list


  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