[AccessD] rsR("order") vs rsR!Order

Er... my 5th/6th groups. My fingers were too busy typing to count on them
and I used ears... ran out.

-----Original Message-----
From: William Benson (VBACreations.Com) [mailto:vbacreations at gmail.com] 
Sent: Monday, August 01, 2011 9:38 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] rsR("order") vs rsR!Order

Nice Test Stuart,... Did you delete table and compact in between tests? I
still think the time adding records to a table and updating the recordset is
needless expansion on the time test. The real issue is simple reference, and
I think that the fastest test for this may be just a string assignment (or a
debug.print - I opted for the former).

Here is another version of the test which looks at pretty much everything
except using one recordset's name as the basis for identifying the other
recordset's field. Personally I cannot see this being any faster than the
one which refers to the name of the field (my second group)

   >> With construction
   >> reference to a field's index in the field collection
   >> reference to a field specifically by Name

After looking at them these results do not surprise me at all. I merely
wrote without much thought before. Happens.

#1: Using bang and no With - Iteration #1 on 1000000 items:  0.953125
#1: Using bang and no With - Iteration #2 on 1000000 items:  0.90625
#1: Using bang and no With - Iteration #3 on 1000000 items:  0.921875

#2: Using bang and With - Iteration #1 on 1000000 items:  0.890625
#2: Using bang and With - Iteration #2 on 1000000 items:  0.8984375
#2: Using bang and With - Iteration #3 on 1000000 items:  0.890625

#3: Using index and no With - Iteration #1 on 1000000 items:  0.625
#3: Using index and no With - Iteration #2 on 1000000 items:  0.609375
#3: Using index and no With - Iteration #3 on 1000000 items:  0.625

#4: Using index and With - Iteration #1 on 1000000 items:  0.609375
#4: Using index and With - Iteration #2 on 1000000 items:  0.625
#4: Using index and With - Iteration #3 on 1000000 items:  0.625

#5: Using field name and no With - Iteration #1 on 1000000 items:  0.9140625
#5: Using field name and no With - Iteration #2 on 1000000 items:  0.90625
#5: Using field name and no With - Iteration #3 on 1000000 items:  0.921875

#6: Using field name no With - Iteration #1 on 1000000 items:  0.90625
#6: Using field name no With - Iteration #2 on 1000000 items:  0.890625
#6: Using field name no With - Iteration #3 on 1000000 items:  0.9140625

Option Compare Database
Option Explicit

Function testrs()
Dim r1 As DAO.Recordset
Dim D As DAO.Database
Dim i As Long, j As Long
Dim Str As String
Dim TimerNow As Double

Set D = CurrentDb

Set r1 = D.OpenRecordset("Select ID from Table1")
r1.MoveFirst

For j = 1 To 3
  TimerNow = Timer
  For i = 1 To 1000000
    Str = r1!id
  Next
  Debug.Print "#1: Using bang and no With - Iteration #" & j & " on " & i -
1 & " items:  " & Timer - TimerNow
Next
Debug.Print
With r1
  For j = 1 To 3
    TimerNow = Timer
    For i = 1 To 1000000
      Str = !id
    Next
    Debug.Print "#2: Using bang and With - Iteration #" & j & " on " & i - 1
& " items:  " & Timer - TimerNow
  Next
End With
Debug.Print
For j = 1 To 3
  TimerNow = Timer
  For i = 1 To 1000000
    Str = r1.Fields(0)
  Next
  Debug.Print "#3: Using index and no With - Iteration #" & j & " on " & i -
1 & " items:  " & Timer - TimerNow
Next
Debug.Print
With r1
  For j = 1 To 3
    TimerNow = Timer
    For i = 1 To 1000000
      Str = .Fields(0)
    Next
    Debug.Print "#4: Using index and With - Iteration #" & j & " on " & i -
1 & " items:  " & Timer - TimerNow
  Next
End With
Debug.Print
For j = 1 To 3
  TimerNow = Timer
  For i = 1 To 1000000
    Str = r1.Fields("id")
  Next
  Debug.Print "#5: Using field name and no With - Iteration #" & j & " on "
& i - 1 & " items:  " & Timer - TimerNow
Next
Debug.Print
With r1
  For j = 1 To 3
    TimerNow = Timer
    For i = 1 To 1000000
      Str = .Fields("id")
    Next
    Debug.Print "#6: Using field name no With - Iteration #" & j & " on " &
i - 1 & " items:  " & Timer - TimerNow
  Next
End With
End Function