Re: DAO MUCH faster than ADO in this test
From: Dirk Goldgar (dg_at_NOdataSPAMgnostics.com)
Date: 03/15/05
- Next message: pskrab: "RE: How do I set a specific date"
- Previous message: Mike Magnes: "Problems With LastUpdate Property!!"
- In reply to: Jeff: "DAO MUCH faster than ADO in this test"
- Next in thread: Dirk Goldgar: "Re: DAO MUCH faster than ADO in this test"
- Reply: Dirk Goldgar: "Re: DAO MUCH faster than ADO in this test"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 15 Mar 2005 14:22:47 -0500
"Jeff" <eatmy@grits.com> wrote in message
news:Opq09RYKFHA.3064@TK2MSFTNGP12.phx.gbl
> I often read that ADO is supposed to be faster than DAO,
I'm not sure where you read that. As far as I know, for working with
Jet databases, DAO is well-known to be faster than ADO.
> but that's
> not the case in this particular test. I wrote 3 procedures to test
> the speed of writing 100,000 records to a table using SQL, ADO, and
> DAO, and DAO easily came out the fastest method.
>
> First, I create a table called "table1" that has 5 fields of type
> BYTE. The weird nested loop in my code to generate numbers is
> necessary for a procedure I will be using in another database. I
> also used a delete query to empty the table after each test.
>
> In the testsql procedure, I tested 3 different commands that execute
> SQL code; DoCmd.RunSQL took 325 seconds,
> CurrentProject.Connection.Execute took 215 seconds, and
> CurrentDb.Execute took 198 seconds. By the way, my computer is a
> 2ghz Pentium 4 with WindowsXP and Access 2002.
>
> The testado procedure took 14 seconds.
>
> The testdao procedure took just under 4 seconds.
>
> Does anyone know how to do this with RDO? Is there another method
> that might be faster? I'll never use SQL code in my VBA procedures
> again, that's for sure.
Of course the DAO loop ran faster than the SQL loop; you've already
opened the recordset before you ever enter the loop. The SQL loop has
to parse the query and access the table in every iteration of the loop.
Not only that, but in your testsql loop you're calling the CurrentDb
function with each loop iteration. To be fair, you must do that only
once, before you enter the loop. In your DAO test, you get the
advantage of a table-type recordset, which only works on local tables.
If you set that to work on a query or a linked table, or otherwise force
the recordset to be a dynaset, you'll find that the results are
*extremely* different.
Let's look at some fairer variants of your test routines. Consider this
module code:
'----- start of module code -----
Option Compare Database
Option Explicit
Sub RunAllTests()
testsql
testado1
testado2
testdao1
testdao2
testdao3
testdao4
End Sub
Sub testsql()
Dim n0 As Byte, n1 As Byte, n2 As Byte, n3 As Byte, n4 As Byte
Dim db As DAO.Database
Dim starttime As Single, finishtime As Single
Set db = CurrentDb
db.Execute "DELETE FROM TableTest"
starttime = Timer
For n0 = 0 To 9
For n1 = 0 To 9
For n2 = 0 To 9
For n3 = 0 To 9
For n4 = 0 To 5
db.Execute "insert into [TableTest] values (" & n0 & "," & n1
& "," & n2 & "," & n3 & "," & n4 & ")"
Next n4
Next n3
Next n2
Next n1
Next n0
finishtime = Timer
Debug.Print "testsql:", finishtime - starttime
Set db = Nothing
End Sub
Sub testado1()
Dim n0 As Byte, n1 As Byte, n2 As Byte, n3 As Byte, n4 As Byte
Dim starttime As Single, finishtime As Single
Dim conn As ADODB.Connection
Dim rs As New ADODB.Recordset
Set conn = CurrentProject.Connection
conn.Execute "DELETE FROM TableTest"
starttime = Timer
rs.Open "TableTest", conn, adOpenKeyset, adLockOptimistic
For n0 = 0 To 9
For n1 = 0 To 9
For n2 = 0 To 9
For n3 = 0 To 9
For n4 = 0 To 5
rs.AddNew
rs(0) = n0
rs(1) = n1
rs(2) = n2
rs(3) = n3
rs(4) = n4
rs.Update
Next n4
Next n3
Next n2
Next n1
Next n0
rs.Close
finishtime = Timer
Set rs = Nothing
Set conn = Nothing
Debug.Print "testado1:", finishtime - starttime, "(ADO; open recordset
outside loop)"
End Sub
Sub testado2()
Dim n0 As Byte, n1 As Byte, n2 As Byte, n3 As Byte, n4 As Byte
Dim starttime As Single, finishtime As Single
Dim conn As ADODB.Connection
Dim rs As New ADODB.Recordset
Set conn = CurrentProject.Connection
conn.Execute "DELETE FROM TableTest"
starttime = Timer
For n0 = 0 To 9
For n1 = 0 To 9
For n2 = 0 To 9
For n3 = 0 To 9
For n4 = 0 To 5
rs.Open "TableTest", conn, adOpenKeyset, adLockOptimistic
rs.AddNew
rs(0) = n0
rs(1) = n1
rs(2) = n2
rs(3) = n3
rs(4) = n4
rs.Update
rs.Close
Next n4
Next n3
Next n2
Next n1
Next n0
finishtime = Timer
Set rs = Nothing
Set conn = Nothing
Debug.Print "testado2:", finishtime - starttime, "(ADO; open recordset
inside loop)"
End Sub
Sub testdao1()
Dim n0 As Byte, n1 As Byte, n2 As Byte, n3 As Byte, n4 As Byte
Dim starttime As Single, finishtime As Single
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
db.Execute "DELETE FROM TableTest"
starttime = Timer
Set rs = db.OpenRecordset("TableTest", dbOpenTable)
For n0 = 0 To 9
For n1 = 0 To 9
For n2 = 0 To 9
For n3 = 0 To 9
For n4 = 0 To 5
rs.AddNew
rs(0) = n0
rs(1) = n1
rs(2) = n2
rs(3) = n3
rs(4) = n4
rs.Update
Next n4
Next n3
Next n2
Next n1
Next n0
rs.Close
finishtime = Timer
Set rs = Nothing
Set db = Nothing
Debug.Print "testdao1:", finishtime - starttime, "(DAO; open recordset
outside loop; table-type)"
End Sub
Sub testdao3()
Dim n0 As Byte, n1 As Byte, n2 As Byte, n3 As Byte, n4 As Byte
Dim starttime As Single, finishtime As Single
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
db.Execute "DELETE FROM TableTest"
starttime = Timer
Set rs = db.OpenRecordset("TableTest", dbOpenDynaset)
For n0 = 0 To 9
For n1 = 0 To 9
For n2 = 0 To 9
For n3 = 0 To 9
For n4 = 0 To 5
rs.AddNew
rs(0) = n0
rs(1) = n1
rs(2) = n2
rs(3) = n3
rs(4) = n4
rs.Update
Next n4
Next n3
Next n2
Next n1
Next n0
rs.Close
finishtime = Timer
Set rs = Nothing
Set db = Nothing
Debug.Print "testdao3:", finishtime - starttime, "(DAO; open recordset
outside loop; dynaset)"
End Sub
Sub testdao2()
Dim n0 As Byte, n1 As Byte, n2 As Byte, n3 As Byte, n4 As Byte
Dim starttime As Single, finishtime As Single
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
db.Execute "DELETE FROM TableTest"
starttime = Timer
For n0 = 0 To 9
For n1 = 0 To 9
For n2 = 0 To 9
For n3 = 0 To 9
For n4 = 0 To 5
Set rs = db.OpenRecordset("TableTest", dbOpenTable)
rs.AddNew
rs(0) = n0
rs(1) = n1
rs(2) = n2
rs(3) = n3
rs(4) = n4
rs.Update
rs.Close
Set rs = Nothing
Next n4
Next n3
Next n2
Next n1
Next n0
finishtime = Timer
Set db = Nothing
Debug.Print "testdao2:", finishtime - starttime, "(DAO; open recordset
inside loop; table-type)"
End Sub
Sub testdao4()
Dim n0 As Byte, n1 As Byte, n2 As Byte, n3 As Byte, n4 As Byte
Dim starttime As Single, finishtime As Single
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
db.Execute "DELETE FROM TableTest"
starttime = Timer
For n0 = 0 To 9
For n1 = 0 To 9
For n2 = 0 To 9
For n3 = 0 To 9
For n4 = 0 To 5
Set rs = db.OpenRecordset("TableTest", dbOpenDynaset)
rs.AddNew
rs(0) = n0
rs(1) = n1
rs(2) = n2
rs(3) = n3
rs(4) = n4
rs.Update
rs.Close
Set rs = Nothing
Next n4
Next n3
Next n2
Next n1
Next n0
finishtime = Timer
Set db = Nothing
Debug.Print "testdao4:", finishtime - starttime, "(DAO; open recordset
inside loop; dynaset)"
End Sub
'----- end of module code -----
You'll note that I reduced your innermost loop from 10 iterations to 6.
That was just to cut down the running time a bit; as it is, on my
somewhat dated PC this takes an onerously long time to run.
The results of executing the "RunAllTests" procedure are as follows:
testsql: 111.3398
testado1: 11.4375 (ADO; open recordset outside loop)
testado2: 875.1289 (ADO; open recordset inside loop)
testdao1: 3.84375 (DAO; open recordset outside loop; table-type)
testdao2: 23.27344 (DAO; open recordset inside loop; table-type)
testdao3: 3.195313 (DAO; open recordset outside loop; dynaset)
testdao4: 546.5742 (DAO; open recordset inside loop; dynaset)
So what conclusions can we draw from this?
1. ADO is nowhere faster than DAO, with Jet tables.
2. If you can't use a table-type recordset, executing an SQL append
query to add a single record is faster than opening a dynaset-type
recordset to do it.
3. If you need to add a single record, and you can count on being able
to use a table-type recordset, then adding that record via a DAO
table-type recordset will be faster than executing an SQL append query.
4. If you're planning to add a lot of records, but must add them one at
a time, then use a recordset to do it -- but open that recordset once,
add the records, and then close the recordset.
I wouldn't let these results convince me to use table-type recordsets
all over the place, though. I just got done reworking an old
application in which I had done that, because now it was time to split
the database and all the table-type recordset logic no longer works with
linked tables.
-- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup)
- Next message: pskrab: "RE: How do I set a specific date"
- Previous message: Mike Magnes: "Problems With LastUpdate Property!!"
- In reply to: Jeff: "DAO MUCH faster than ADO in this test"
- Next in thread: Dirk Goldgar: "Re: DAO MUCH faster than ADO in this test"
- Reply: Dirk Goldgar: "Re: DAO MUCH faster than ADO in this test"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
