[AccessD] Fastest - DAO or Sql

"Shamil, if I had a clue what you were talking about, I would try it." Max

...as with so many of Shamil's posts, I need a ladder to elevate myself to 
his level of coding ...a very tall ladder :)

William

--------------------------------------------------
From: "Max Wanadoo" <max.wanadoo at gmail.com>
Sent: Monday, March 09, 2009 1:45 PM
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Fastest - DAO or Sql

> Shamil, if I had a clue what you were talking about, I would try it.
>
> Post the code - using mine as the base (so that I can follow it).
>
> Max
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Salakhetdinov
> Shamil
> Sent: 09 March 2009 17:40
> To: Access Developers discussion and problem solving
> Subject: Re: [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
>>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>