DAO MUCH faster than ADO in this test

[Jeff]

I often read that ADO is supposed to be faster than DAO, 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.

*** VBA code is below ***

Option Compare Database

Sub testsql()
Dim n0 As Byte, n1 As Byte, n2 As Byte, n3 As Byte, n4 As Byte
Dim starttime As Single, finishtime As Single

SetOption "confirm action queries", False
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 9
sql = "insert into [table1] values (" & n0 & "," & n1 & "," & n2 & "," & n3 & "," & n4 &
")"
'DoCmd.RunSQL sql
'CurrentDb.Execute sql
CurrentProject.Connection.Execute sql
Next n4
Next n3
Next n2
Next n1
Next n0
finishtime = Timer
SetOption "confirm action queries", True
Debug.Print finishtime - starttime

End Sub

Sub testado()
Dim n0 As Byte, n1 As Byte, n2 As Byte, n3 As Byte, n4 As Byte
Dim starttime As Single, finishtime As Single
Dim rs As New ADODB.Recordset

rs.Open "table1", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
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 9
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
Set rs = Nothing
finishtime = Timer
Debug.Print finishtime - starttime

End Sub

Sub testdao()
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()
Set rs = db.OpenRecordset("table1")
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 9
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
Set rs = Nothing
Set db = Nothing
finishtime = Timer
Debug.Print finishtime - starttime

End Sub

 

[Ron Weiner]

What happens to the time when you change the Cursor type to
adOpenForwardOnly as in:

rs.Open "table1", CurrentProject.Connection, adOpenForwardOnly,
adLockOptimistic

Betcha' that in your contrived example things go faster!!! On my much
slower box the time went from 26 to 8 seconds. A Keyset type cursor is
pretty expensive time-wise as it offers the greatest flexibility in the
thjings you can do with your open recordset. The ForwardOnly (sometimes
called Fire Hose) cursor does not allow for much flexibility, but is very
fast!

Ron W

I often read that ADO is supposed to be faster than DAO, 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.

*** VBA code is below ***

Option Compare Database

Sub testsql()
Dim n0 As Byte, n1 As Byte, n2 As Byte, n3 As Byte, n4 As Byte
Dim starttime As Single, finishtime As Single

SetOption "confirm action queries", False
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 9
sql = "insert into [table1] values (" & n0 & "," & n1 & "," & n2 & "," & n3 & "," & n4 &
")"
'DoCmd.RunSQL sql
'CurrentDb.Execute sql
CurrentProject.Connection.Execute sql
Next n4
Next n3
Next n2
Next n1
Next n0
finishtime = Timer
SetOption "confirm action queries", True
Debug.Print finishtime - starttime

End Sub

Sub testado()
Dim n0 As Byte, n1 As Byte, n2 As Byte, n3 As Byte, n4 As Byte
Dim starttime As Single, finishtime As Single
Dim rs As New ADODB.Recordset

rs.Open "table1", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
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 9
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
Set rs = Nothing
finishtime = Timer
Debug.Print finishtime - starttime

End Sub

Sub testdao()
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()
Set rs = db.OpenRecordset("table1")
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 9
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
Set rs = Nothing
Set db = Nothing
finishtime = Timer
Debug.Print finishtime - starttime

End Sub

 

[Jeff]

aaaaaahhh, thanks! I never did understand much about those additional parameters for an ADO
recordset. I just follow my book's example which doesn't explain those parameters.

Yes, the ADO method for writing 100,000 records went from 14 seconds to only 5 seconds on my
computer. That at least makes it close to DAO's 4 seconds.

Fire Hose? hahahaha, never heard of that.

 

[Jeff]

Once strange thing about the SQL method of this test is when you look at the result table, the
records are out of order starting at record #4335. This doesn't happen in the DAO or ADO methods.
Any idea why that is?

 

[Dirk Goldgar]

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.

 

[Ron Weiner]

Jeff

Because of the structure of your table is there is no "natural" order.
Typically when we want data to be in some kind of order we'd use an Order By
clause when we retrieve the data.

If you want the data to be in some kind of natural order in the table, then
create a Primary Key using all of your columns. This will force the
database to physically place the records in the order they were created
because of the algorithm you are using to create the records. Obviously
adding Indexes to any of the columns will slow your test results as the
database must update the index on every insert.

Relational Databases do not care what the order rows are inserted into the
table, and there is no guarantee that the physical row order in the table
will have anything to do with the order that the records were inserted. You
are guilty of Sequential thinking in a Set based environment. Get some new
books on Relational Database theory if you really want to have greater
insight.

Ron W

 

[Marshall Barton]

Jeff, I have a few comments about your tests.

These tests are a little contrived and may not represent a
more normal situation, so don't over generalize the results
to too many other situations.

Did you run the tests the same way every time? I.e. was the
machine freshly rebooted before each test. If not, then
second runs may have vastly different results than the first
run have when the data is not in cache memory. What happens
if you run the same test sequence several times in
succession?

How about the order they were executed each time?
Considering possible caching scenarios, what happens if you
run the tests in a different order? It is at least possible
that the first one will spend a lot of time retrieving data
that the later tests will already have available in cache.

Are there any other activities executing on the machine?
Time slicing may hit one of your test harder than another.
In Windows, there is always something else grabbing CPU
cycles so it is important to note that a performance
measurement is **always** a statistical value with a mean,
standard diviation, etc.

As with all things programming, the biggest performance
boost comes from an optimal design for whatever operation
you need to perform. I can't say I can think of a better
way to design the DAO and ADO tests, but the SQL test is
just about the worst design I can imagine for this
operation. A better design would be to get rid of the loops
and execute a single query. Create a little table named
Digits with a single field named Digit. Populate the table
with 10 rows: 0,1,2,...9. Now you can populate your table1
by executing a single, much faster query:

INSERT INTO table1
SELECT Th.Digit, H.Digit,T.Digit,U.Digit
FROM Digits As Th, Digits AS H, Digits AS T, Digits AS U

As Ron pointed out, different kinds of recordset options can
make a big difference. You should investigate the other
kinds of DAO recordsets too.

There are many ways to do just about everything. You need
to pay very close attention to the details before reaching a
conclusion, especially if you're going to generalize the
results to SQL vs DAO vs ADO.
--
Marsh
MVP [MS Access]

I often read that ADO is supposed to be faster than DAO, 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.

*** VBA code is below ***

Option Compare Database

Sub testsql()
Dim n0 As Byte, n1 As Byte, n2 As Byte, n3 As Byte, n4 As Byte
Dim starttime As Single, finishtime As Single

SetOption "confirm action queries", False
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 9
sql = "insert into [table1] values (" & n0 & "," & n1 & "," & n2 & "," & n3 & "," & n4 &
")"
'DoCmd.RunSQL sql
'CurrentDb.Execute sql
CurrentProject.Connection.Execute sql
Next n4
Next n3
Next n2
Next n1
Next n0
finishtime = Timer
SetOption "confirm action queries", True
Debug.Print finishtime - starttime

End Sub

Sub testado()
Dim n0 As Byte, n1 As Byte, n2 As Byte, n3 As Byte, n4 As Byte
Dim starttime As Single, finishtime As Single
Dim rs As New ADODB.Recordset

rs.Open "table1", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
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 9
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
Set rs = Nothing
finishtime = Timer
Debug.Print finishtime - starttime

End Sub

Sub testdao()
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()
Set rs = db.OpenRecordset("table1")
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 9
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
Set rs = Nothing
Set db = Nothing
finishtime = Timer
Debug.Print finishtime - starttime

End Sub

 

[Dirk Goldgar]

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.

I might also comment that operations on multiple records that can be
performed without actually returning records to the user, such as an
action query that affects multiple records, will almost certainly be
significantly faster when performed via an SQL statement.

 

[David C. Holley]

I would concur on the real world ramifications. How many times in the RW
are you appending 100,000 records other than interfacing or loading
tables? From my experience with QA/Testing and limited knowledge of
DB's, I would suggest that a more realistic test involve the following

single table/single record update
single table/multiple record update (non concurent)
multiple table/single parent-single child update
multiple table/single parent-multiple child update
multiple table/multiple parent-multiple child update

I would also toss in some scenarios dealing with the use of transactions
including transactions that passed and those that need to be rolled back.

David H

 

[David C. Holley]

I would concur on the real world ramifications. How many times in the RW
would a regular user append 100,000 records? While there would be
instances related to interfacing, loading tables and regular batch jobs,
the tasks would be typically be some combination of back-office,
development or overnight functions. From my experience with QA/Testing
and limited knowledge of DB's, I would suggest that a more realistic
test involve the following...

single table/single record update
single table/multiple record update (non concurent)
multiple table/single parent-single child update
multiple table/single parent-multiple child update
multiple table/multiple parent-multiple child update

I would also toss in some scenarios dealing with the use of transactions
including transactions that passed and those that need to be rolled back.

David H

 

[Jeff]

Yes, I ran the procedures the same way every time and had no other applications running. I also ran
them multiple times in various orders and got the same results, most of which varied by less than a
second.

The reason this particular method of filling a table is useful to me is because I'm writing an
application (for the hell of it) to do statistical calculations on lottery numbers, and one thing I
need is a table of all possible combinations. For Powerball, I would need over 120 million records
to work with. I originally tried to generate this with the SQL statement in a VBA procedure and it
wreaked havoc on my computer. After running for 30 minutes the CPU temperature when up and
processing seemed to slow down. When I finally broke out of the procedure, only about 32 million
combinations (records) were written to the table. At that point, I set out to find a better way.

I don't understand how your INSERT statement would eliminate the need for a loop to generate the
records I need. I tried running your statement and it gave an error saying "dupicate output alias
Digit" If you could show me this or refer me to a good book it would be greatly appreciated.
Thanks!

Jeff

 

[Allen Browne]

Jeff, you have had lots of good feedback on this thread. Here's a couple
more observations.

DAO is the native Access library. It was created for Access. The A in DAO
*is* Access. Access itself uses it internally when you execute queries and
so forth. It makes sense that DAO is therefore going the be the most
suitable, flexible, efficient way to work with data in Access (JET) tables.

If you are adding that quantity of records to a table in one hit, you may
want to consider other ways of making this as efficient as possible. For
example, consider dropping all the indexes in the table, and then adding
them back again at the end, so that Access does not need to maintain the
indexes after adding every record.

 

[David C. Holley]

So now I"m just plain curious as to what statistical calcs you're
interested in doing?

Yes, I ran the procedures the same way every time and had no other applications running. I also ran
them multiple times in various orders and got the same results, most of which varied by less than a
second.

The reason this particular method of filling a table is useful to me is because I'm writing an
application (for the hell of it) to do statistical calculations on lottery numbers, and one thing I
need is a table of all possible combinations. For Powerball, I would need over 120 million records
to work with. I originally tried to generate this with the SQL statement in a VBA procedure and it
wreaked havoc on my computer. After running for 30 minutes the CPU temperature when up and
processing seemed to slow down. When I finally broke out of the procedure, only about 32 million
combinations (records) were written to the table. At that point, I set out to find a better way.

I don't understand how your INSERT statement would eliminate the need for a loop to generate the
records I need. I tried running your statement and it gave an error saying "dupicate output alias
Digit" If you could show me this or refer me to a good book it would be greatly appreciated.
Thanks!

Jeff

 

[Michel Walsh]

Hi,


In the real life, you probably get the data from somewhere? If it is
from the keyboard, the "loop" time is negligible in comparison with the user
time spent keying in the numbers. If it is from a table (ISAM or
otherwise), the SQL way to do it would be through a single statement. If the
data is randomly generated, then, again, do it, inside a single SQL
statement. If you use Jet, you should not get the error you mentioned
(unless you SELECT INTO, to create a new table, rather than INSERT INTO, to
append to an existing table). You can try to supply explicit alias:

SELECT Th.Digit As ThDigit, H.Digit As HDigit, T.Digit As TDigit,
U.Digit As UDigit FROM ...


Hoping it may help,
Vanderghast, Access MVP

Yes, I ran the procedures the same way every time and had no other
applications running. I also ran
them multiple times in various orders and got the same results, most of
which varied by less than a
second.

The reason this particular method of filling a table is useful to me is
because I'm writing an
application (for the hell of it) to do statistical calculations on lottery
numbers, and one thing I
need is a table of all possible combinations. For Powerball, I would need
over 120 million records
to work with. I originally tried to generate this with the SQL statement
in a VBA procedure and it
wreaked havoc on my computer. After running for 30 minutes the CPU
temperature when up and
processing seemed to slow down. When I finally broke out of the
procedure, only about 32 million
combinations (records) were written to the table. At that point, I set
out to find a better way.

I don't understand how your INSERT statement would eliminate the need for
a loop to generate the
records I need. I tried running your statement and it gave an error
saying "dupicate output alias
Digit" If you could show me this or refer me to a good book it would be
greatly appreciated.
Thanks!

Jeff

 

[Ron Weiner]

Jeff

Basically I believe that all of us are saying the same thing. That is
almost always the best performance gains can be gotten by looking at
changing the Algorithm and not the relative speed of this instruction vs.
that one. As a developer I am always thinking about how fast my application
will run, BUT I spent the OVERWHELMING amount of my time making sure I get
the application to work CORRECTLY.

Having said that how would you like to be able to create the 100K records
you have been playing with in less than a second?

I thought so!

First make a new Table "tblNum" with one column "Num" as an integer. It
does not need any indexes or primary keys. Once the table is made open it
and add 10 records 0 through 9. Close the table and paste the following Sql
statement into a query.

INSERT INTO Table1 ( N0, N1, N2, N3, N4 )
SELECT tblNum.Num, tblNum_1.Num, tblNum_2.Num, tblNum_3.Num, tblNum_4.Num
FROM tblNum, tblNum AS tblNum_1, tblNum AS tblNum_2, tblNum AS tblNum_3,
tblNum AS tblNum_4

Run the query and see if I was right about the time it takes to create 100K
records.

When you are done go to Google and do a search on Sql Cartesian Product
Join. There should be more than enough hits there to explain how and why
this query works. Once you have done this reasearch you will always have
the Cartesian Product in your tool box ready to amuse and amaze all.

Ron W

Yes, I ran the procedures the same way every time and had no other

applications running. I also ran

them multiple times in various orders and got the same results, most of which varied by less than a
second.

The reason this particular method of filling a table is useful to me is because I'm writing an
application (for the hell of it) to do statistical calculations on lottery numbers, and one thing I
need is a table of all possible combinations. For Powerball, I would need over 120 million records
to work with. I originally tried to generate this with the SQL statement in a VBA procedure and it
wreaked havoc on my computer. After running for 30 minutes the CPU temperature when up and
processing seemed to slow down. When I finally broke out of the

procedure, only about 32 million

combinations (records) were written to the table. At that point, I set out to find a better way.

I don't understand how your INSERT statement would eliminate the need for a loop to generate the
records I need. I tried running your statement and it gave an error saying "dupicate output alias
Digit" If you could show me this or refer me to a good book it would be greatly appreciated.
Thanks!

Jeff

 

[Jeff]

Yes, but my question why does it first appear in the order it was written when using DAO and ADO,
and not in order when using SQL?

 

[David C. Holley]

But if the test includes the time to input the data, the test becomes
more focused on the total business process as opposed to simply DAO
versus ADO. If this is something that needs to be tested, you'd have to
factor in the differences in typing speed between individuals.
Personally, if the test is to determine system response time, the amount
of time neccessary to input the data should be irrelevant since your
looking at system RESPONSE time.

David H

Hi,


In the real life, you probably get the data from somewhere? If it is
from the keyboard, the "loop" time is negligible in comparison with the user
time spent keying in the numbers. If it is from a table (ISAM or
otherwise), the SQL way to do it would be through a single statement. If the
data is randomly generated, then, again, do it, inside a single SQL
statement. If you use Jet, you should not get the error you mentioned
(unless you SELECT INTO, to create a new table, rather than INSERT INTO, to
append to an existing table). You can try to supply explicit alias:

SELECT Th.Digit As ThDigit, H.Digit As HDigit, T.Digit As TDigit,
U.Digit As UDigit FROM ...


Hoping it may help,
Vanderghast, Access MVP

 

[david epsom dot com dot au]

You are comparing an Access method to a DAO/ADO method.
Access is a Windows program that decouples the user
interface by using Windows to complete actions: Windows
does not guarantee the order of those actions once
they have started.

(david)