Field Name Speed?

[David]

I've read that when using access fields that:

rstOrders.Fields("fldCustomer")

is faster than

!fldCustomer

=================
Question

Is this true, and if so why?

Thanks
David

 

[Stefan Hoffmann]

hi David,

I've read that when using access fields that:
rstOrders.Fields("fldCustomer")
is faster than
!fldCustomer

Is this true, and if so why?

Go and search for 'dot vs bang'...

It's basically caused by the fact that the bang'ed access must be
resolved at runtime and this is a little bit more complex than
enumerating a collection and returning a value.

mfG
--> stefan <--

 

[David]

Thanks Mr. Hoffmann for responding.

Are you saying "bang" is late-bound versus Field early-bound.
If so, do you know any article or MSDN reference that refers to this --
or -- the best way to test it?

David

 

[Stefan Hoffmann]

hi David,

Are you saying "bang" is late-bound versus Field early-bound.
If so, do you know any article or MSDN reference that refers to this --
or -- the best way to test it?

Take a closer look at

http://msdn.microsoft.com/en-us/library/aa210660(v=office.11).aspx

seems that I've also mixed it up.


mfG
--> stefan <--

 

[David]

Thanks for link. Been searching MSDN without any luck.

Using Me.("Customer") logically makes sense that it would be slower since a
string needs to be allocated versus referring to the field name directly.

Unfortuantely they did not specifically address the Fields collection.
Whether the logic prevails is a ?

David

 

[david]

When using Access fields,

Set fld = rstOrders.Fields("fldCustomer")

for i = 1 to 100000
rstOrders.AddNew
fld = str(i)
rstOrders.Update
next

is much faster.

Although record update loops are a very old technique, once
used mostly by old dBase programmers. Mostly you can use
an Update Query to update a lot of records.

Don't know why it is called an Update Query. In other
RDMS's it would have been called a stored procedure.

(david)

 

[Stefan Hoffmann]

When using Access fields,

Set fld = rstOrders.Fields("fldCustomer")
for i = 1 to 100000
rstOrders.AddNew
fld = str(i)
rstOrders.Update
next

is much faster.

This is basically true for all collections which can be bound like
fields, e.g.

Option Compare Database
Option Explicit

Declare Function GetTickCount Lib "kernel32.dll" () As Long

Public Sub Test()

Const MAX_COUNT As Long = 100000
Dim db As DAO.Database
Dim td As DAO.TableDef

Dim count As Long
Dim dummy As String
Dim tcStart As Long
Dim tcStop As Long

Debug.Print "---"
Set db = CurrentDb

tcStart = GetTickCount
For count = 1 To MAX_COUNT
dummy = db.TableDefs.item(0).Name
Next count
tcStop = GetTickCount
Debug.Print "Time elapsed:"; tcStop - tcStart; "ms"

Set td = db.TableDefs.item(0)

tcStart = GetTickCount
For count = 1 To MAX_COUNT
dummy = td.Name
Next count
tcStop = GetTickCount
Debug.Print "Time elapsed:"; tcStop - tcStart; "ms"

End Sub

Don't know why it is called an Update Query. In other
RDMS's it would have been called a stored procedure.

Only when you store it there, otherwise it may be called adhoc query.
Some DBA's are afraid of these


mfG
--> stefan <--