Based on advice given to me in another thread, I have implemeted a table with a single record and a single field which will contain the value of a variable.
On certain events, code will run which will:
a. Extract the value of this variable from the table.
b. Do some stuff
Now I've got everything down except for the command to extract the variable from the table.
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim myValue As String
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open "MyTable", cn
myValue = rs.Fields("MyField")
rs.Close
Set rs = Nothing
Set cn = Nothing
The Domain Aggregates, though I don't like them much, will on a single native table probably be even faster than DAO (which is also known to be faster than ADO under most circumstances).
This intrigued me into doing some small tests - now these tests should also take into consideration:
* a database (backend) on the server
* security
Using the QueryPerformanceCounter API for some small tests, timing the execution of only the statement(s) needed to execute the different retrievals, doing the average from 12 tests of exch, where the fastest and the slowest performance was excluded (not very scientific, though...). Tests are performed on XP proffesional (OS/Access), 256 MB RAM, 2.2 MHz. Neither of the databases have any security (which should also be tested!)
Using a single table with two fields (id, and some number), and only a single row
So, in this test, on a single table with one record, DAO is slightly faster than DLookup. ADO recordsets are almost the same, when the adcmdtabledirect option is used.
Now - adding some more records (just 100) and trying DMax vs Select Max.
Here, DLookup is the fastest (but by less then a hair, I'd say), but what impresses me, is how close ADO are in performance. I'd thought ADO would be slower - this is, though, using the undocumented adCmdTableDirect option.
Now, let's see what happens if we add a criterion (back to Dlookup and id = 50)
This time, DAO is tha fastest again, but not by much.
Now, I've added some 400 000 records to the same table, let's repeat the last test. Here using id = 100000, to fetch a record "in the middle somewhere"
Now, this is a surprise, it seems all the recordsetmethods performs slightly faster on larger recordsets, whilst DLookup performs slightly poorer.
Now then, the final tests, first repeating the last test on a linked table (unfortunately, I'm limited to link to another database on the same computer, so a more realistic test would be encouraged).
Now, one may say DAO recordsets outperforms DLookup. I'm also very surprised how close ADO recordsets are in performance, though the "winning" version uses an undocumented option that I found not long ago. The more documented versions are also rather close to the DLookup in performance, which I also find a bit surprising, since the Domain Aggregates are native Access functions designed to work on Access, whilst ADO is a more general manipulation library designed to match "all databases".
It'd be interesting if someone would bother either replicating these tests on other setups (and especially with security and a server backend), and perhaps using other performance timers, doing iterations...
What conclusions to draw?
I'll leave that up to you
How was the test conducted:
Here's the code used for the last test.
Code:
Option Compare Database
Option Explicit
Private Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As Currency) As Boolean
Private Sub test()
' Domain Aggregate
Dim s As Long
Dim l As Long
Dim c As Currency
Dim c1 As Currency
QueryPerformanceCounter c
s = DLookup("asdf", "asdf41", "id = 100000")
QueryPerformanceCounter c1
Debug.Print c1 - c, s
End Sub
Private Sub test1()
' DAO Recordset
Dim s As Long
Dim l As Long
Dim c As Currency
Dim c1 As Currency
Dim rs As DAO.Recordset
QueryPerformanceCounter c
Set rs = CurrentDb.OpenRecordset("select asdf from asdf41 where id = 100000", dbOpenForwardOnly)
s = rs.Fields(0).Value
QueryPerformanceCounter c1
Debug.Print c1 - c, s
End Sub
Private Sub test2()
' ADO Recordset
Dim s As Long
Dim l As Long
Dim c As Currency
Dim c1 As Currency
Dim rs As adodb.Recordset
QueryPerformanceCounter c
Set rs = New adodb.Recordset
rs.Open "select asdf from asdf41 where id = 100000", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly, adCmdTableDirect
s = rs.Fields(0).Value
QueryPerformanceCounter c1
Debug.Print c1 - c, s
End Sub
Private Sub test3()
' ADO Recordse, Currentproject.Connection.Execute
Dim s As Long
Dim l As Long
Dim c As Currency
Dim c1 As Currency
Dim rs As adodb.Recordset
QueryPerformanceCounter c
Set rs = CurrentProject.Connection.Execute("select asdf from asdf41 where id = 100000", , adCmdTableDirect)
s = rs.Fields(0).Value
QueryPerformanceCounter c1
Debug.Print c1 - c, s
End Sub
