[AccessD] Fastest - DAO or Sql

> *So for me, the message is clear.  
> Open record set. Keep it open if it is
> going to be used again.
Max,

Keeping an open recordser is not so good idea from scalability point of view.

Also when programming with transactions updates of an opened recordset will not go to the database, will they?

And you didn't yet try to use/compare parameterized action queries with QueryDef's Execute method.

The latter could be an optimal solution from many points of view, could it be? :)

Thank you.

--
Shamil


-----Original Message-----
From: Max Wanadoo <max.wanadoo at gmail.com>
To: Access Developers discussion and problem solving<accessd at databaseadvisors.com>
Date: Mon, 9 Mar 2009 14:35:48 +0000
Subject: [AccessD] Fastest - DAO or Sql

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