Field Name Speed?

  • Thread starter Thread starter David
  • Start date Start date
D

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
 
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?
Click to expand...
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 <--
 
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
 
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
 
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)
 
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.
Click to expand...
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.
Click to expand...
Only when you store it there, otherwise it may be called adhoc query.
Some DBA's are afraid of these :)


mfG
--> stefan <--
 
Back
Top