[AccessD] Fastest - DAO or Sql

Thanks for doing this Max.  I have done this but it was years ago.  It is useful to be reminded of 
the specifics every once in a while.

John W. Colby
www.ColbyConsulting.com


Max Wanadoo wrote:
>  *Well, there is no doubt about it in my mind now.  It is the opening of the
> record set that takes up the time.   As has often been stated here by many
> of the Listers.*
> **
> *If you can keep the record open, then the DAO.AddNew method takes 4 seconds
> for 100,000 records and the .Execute(Sql) takes 2 min 47 seconds whereas if
> you don't hold the record set open then the DAO.AddNew method takes over
> nearly 10 minutes.  Clearly the difference is in opening the record set.  *
> **
> *So for me, the message is clear.  Open record set. Keep it open if it is
> going to be used again.  Single records makes no difference as both methods
> have to open the record set*
> **
> *Summary of Times:*
> **
> *SQL*
> *'18 secs for 10,000
> ' 2min 47 secs for 100,000
> DAO Open Record set*
> *' 1 sec for 10,000
> ' 4 sec for 100,000*
> *DAO Closed Record sets*
> *' 18 secs for 10,000
> ' 9min 47 secs for 100,000
> *
> *Test Code below.*
> ** * *
> ** *Max*
> *ps. Thanks for all those who provided feedback and AD for his examples.*
> *pps. ADO not tested.*
> **
> **
> *Option Compare Database
> Option Explicit
> Const conQty As Long = 10000
> Const conDeleteAll As String = "Delete * from tblAppendTest"
> Const conName As String = "abcdefghijklmnopqrstuvwxys"
> Const conLong As Long = 123456789
> Private sql As String
> Private lngLoop As Long
> Private datStart As Date, datEnd As Date, datLapsed As Date*
> **
> *Public Function pfRunTests()
>   On Error Resume Next
>   sql = "Drop table tblAppendTest"
>   CurrentDb.Execute (sql)
>   sql = "Create Table tblAppendTest (txtName Text, lngLong Long)"
>   CurrentDb.Execute (sql)
>   CurrentDb.Execute (conDeleteAll)
>   Call pfAppendTestSql
>   CurrentDb.Execute (conDeleteAll)
>   Call pfAppendTestDAO
>   CurrentDb.Execute (conDeleteAll)
>   Call pfAppendTestDAO2
> End Function
> *
> **
> *Public Function pfAppendTestSql()
> '18 secs for 10,000
> ' 2min 47 secs for 100,000
>   datStart = Now()
>   Debug.Print "SQL Insert Test"
>   'Debug.Print Format(datStart, "hh:mm:ss")
>   For lngLoop = 1 To conQty
>     CurrentDb.Execute ("Insert into tblAppendTest (txtName,lngLong) values
> ('" & conName & "'," & conLong & ")")
>   Next lngLoop
>   datEnd = Now
>   'Debug.Print Format(datEnd, "hh:mm:ss")
>   Debug.Print Format(datEnd - datStart, "hh:mm:ss")
> End Function
> *
> ** **
> *Public Function pfAppendTestDAO()
> ' 1 sec for 10,000
> ' 4 sec for 100,000*
> **
> *  Dim rst As DAO.Recordset
>   Set rst = CurrentDb.OpenRecordset("Select * from tblAppendTest")
>   datStart = Now()
>   Debug.Print "DAO AddNew Test"
>   'Debug.Print Format(datStart, "hh:mm:ss")
>   For lngLoop = 1 To conQty
>     rst.AddNew
>     rst!txtname = conName
>     rst!lnglong = conLong
>     rst.Update
>   Next lngLoop
>   datEnd = Now
>   'Debug.Print Format(datEnd, "hh:mm:ss")
>   Debug.Print Format(datEnd - datStart, "hh:mm:ss")
>   Set rst = Nothing
> End Function
> *
> **
> *Public Function pfAppendTestDAO2()
> ' 18 secs for 10,000
> ' 9min 47 secs for 100,000
>   Dim rst As DAO.Recordset
>   datStart = Now()
>   Debug.Print "DAO AddNew Test2"
>   'Debug.Print Format(datStart, "hh:mm:ss")
>   For lngLoop = 1 To conQty
>     Set rst = CurrentDb.OpenRecordset("Select * from tblAppendTest")
>     rst.AddNew
>     rst!txtname = conName
>     rst!lnglong = conLong
>     rst.Update
>     Set rst = Nothing
>   Next lngLoop
>   datEnd = Now
>   'Debug.Print Format(datEnd, "hh:mm:ss")
>   Debug.Print Format(datEnd - datStart, "hh:mm:ss")
>   Set rst = Nothing
> End Function*
> 
> **