Field Name Speed?
in [General]
|
From: david on 20 Apr 2010 02:19 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) "David" <NoWhere(a)earthlink.net> wrote in message news:uoLOXK83KHA.4332(a)TK2MSFTNGP02.phx.gbl... > 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 >
From: Stefan Hoffmann on 20 Apr 2010 04:20
On 20.04.2010 08:19, david wrote: > 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 <-- |