[AccessD] Fastest - DAO or Sql

OK, here are the results for 10,000 records

SQL Insert Test
00:00:11
DAO AddNew Test
00:00:00
DAO AddNew Test2
00:00:17
SQl Parameters
00:00:03

Again, DAO.addnew was too fast to record even 1 second...
But the new options form Shamil was second best with just 3 seconds.

Max
Ps. Thanks for that Shamil.  Your Ladder is still in place..


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Salakhetdinov
Shamil
Sent: 09 March 2009 20:39
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Fastest - DAO or Sql

Max,

It works (Access2007) here with the following SQL/sample code - watch that
select doesn't use any table - just copy the below SQL expression into SQL
view in your query, remove leading quotes and save it:

Public Sub testAppendQuery1()
'
'PARAMETERS [@txtName] Text ( 255 ), [@lngLong] Long;
'INSERT INTO tblAppendTest ( txtName, lngLong )
'select [@txtName] as txtName, [@lngLong] as lngLong
'
Dim conName As String
Dim conLong As Long
    conName = "TEST"
    conLong = 999

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("qAppendTest")
    qdf.Parameters("@txtName").Value = conName ' error here.
    qdf.Parameters("@lngLong").Value = conLong
    qdf.Execute (dbFailOnError)
End Sub

--
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 20:10:26 -0000
Subject: Re: [AccessD] Fastest - DAO or Sql

> Shamil,
> I am not familiar with this construct.
> I have set an Append Query up.  When I look at the SQL it shows this:-
> 
> PARAMETERS txtName Text ( 255 ), lngLong Long;
> INSERT INTO tblAppendTest ( txtName, lngLong )
> SELECT tblAppendTest.txtName, tblAppendTest.lngLong
> FROM tblAppendTest;
> 
> I have set module code up as follows:
> 
> Public Sub testAppendQuery()
> Dim dbs As DAO.Database
> Dim qdf As DAO.QueryDef
>     Set dbs = CurrentDb
>     Set qdf = dbs.QueryDefs("qAppendTest")
>     qdf.Parameters("@txtName").Value = conName ' error here.
>     qdf.Parameters("@lngLong").Value = conLong
>     qdf.Execute (dbFailOnError)
> End Sub
> 
> But it won't run.  Errors out on "Item not found in this collection"
> 
> Max
> 
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Salakhetdinov
> Shamil
> Sent: 09 March 2009 19:32
> To: Access Developers discussion and problem solving
> Subject: Re: [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
> 
<snip>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com