[AccessD] Fastest - DAO or Sql

Hi Max,

I mean something like that (watch line wraps):

Public Sub testAppendQuery()
'
'  qappShippers append action query has the following 
'    parameterized sql: 
'
'PARAMETERS [@shipperId] Long, [@companyName] Text ( 40 ), [@phone] Text ( 24 );
'INSERT INTO Shippers ( ShipperID, CompanyName, Phone )
'SELECT [@shipperId] AS ShipperId, [@companyName] AS CompanyName, [@phone] AS Phone;
'
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("qappShipper")
    qdf.Parameters("@shipperId").Value = 999
    qdf.Parameters("@companyName").Value = "Test Company"
    qdf.Parameters("@phone").Value = "1-123-4567890"
    qdf.Execute (dbFailOnError)
End Sub

Tested with Access2007.

Of course setting AutoNumber value is not necessary.

Guessing: the above approach could be as fast as .AddNew with querydef's object variable kept initialized once and then reused on subsequent calls...

But I can be wrong.

--
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 17:45:05 -0000
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
>