Fastest and most Efficient code?

[ggregg]

Access 2003
I have a table for Sales Orders and one for Sales Order Detail records with
a one-to-many relationship defined on SalesOrderID.
The Sales Order table has over 50,000 records and the Detail has over 200,000.
Sometimes I need to loop through all the detail records for one Sales Order.
Using an example of Sales Order ID #1, which is the most efficient and
fastest way to process the Detail records?

Set rcdSOD = SalesDB.OpenRecordset("SalesOrderDetail", DB_OPEN_DYNASET)
critSOD = "(SalesOrderID = 1)"
rcdSOD.FindFirst critSOD
Do Until rcdSOD.NoMatch
(process the data)
rcdSOD.FindNext critSOD
Loop

OR

critSOD = "Select * from SalesOrderDetail Where [SalesOrderID] = 1"
Set rcdSOD = SalesDB.OpenRecordset(critSOD)
rcdSOD.MoveFirst
Do Until rcdSOD.EOF
(process the data)
rcdSOD.MoveNext
Loop

OR create a Query that Filters the SalesOrderID to 1

Set rcdSOD = SalesDB.OpenRecordset("SalesOrderDetailQuery", DB_OPEN_DYNASET)
rcdSOD.MoveFirst
Do Until rcdSOD.EOF
(process the data)
rcdSOD.MoveNext
Loop

Or is there a better way?
Thank you for any suggestions

 

[Dirk Goldgar]

Access 2003
I have a table for Sales Orders and one for Sales Order Detail records
with
a one-to-many relationship defined on SalesOrderID.
The Sales Order table has over 50,000 records and the Detail has over
200,000.
Sometimes I need to loop through all the detail records for one Sales
Order.
Using an example of Sales Order ID #1, which is the most efficient and
fastest way to process the Detail records?

Set rcdSOD = SalesDB.OpenRecordset("SalesOrderDetail", DB_OPEN_DYNASET)
critSOD = "(SalesOrderID = 1)"
rcdSOD.FindFirst critSOD
Do Until rcdSOD.NoMatch
(process the data)
rcdSOD.FindNext critSOD
Loop

OR

critSOD = "Select * from SalesOrderDetail Where [SalesOrderID] = 1"
Set rcdSOD = SalesDB.OpenRecordset(critSOD)
rcdSOD.MoveFirst
Do Until rcdSOD.EOF
(process the data)
rcdSOD.MoveNext
Loop

OR create a Query that Filters the SalesOrderID to 1

Set rcdSOD = SalesDB.OpenRecordset("SalesOrderDetailQuery",
DB_OPEN_DYNASET)
rcdSOD.MoveFirst
Do Until rcdSOD.EOF
(process the data)
rcdSOD.MoveNext
Loop

Or is there a better way?
Thank you for any suggestions

Assuming that you *must* process the relevant records in code, and can't
create an update query to do the whole job, then your last two approaches
would be much more efficient than the first. I doubt there'd be much
difference in performance between those two.

Note, though, that in either case, your statement "rcdSOD.MoveFirst" is both
unnecessary and error-prone. It's unnecessary because any newly opened
recordset is always going to be positioned at the first record (if there is
one). It's error-prone (as written) because if the recordset is empty,
calling the MoveFirst method will raise an error. So I would just do this:

Set rcdSOD = SalesDB.OpenRecordset( ... )
With rcdSOD
Do Until .EOF
(process the data)
.MoveNext
Loop
.Close
End With

 

[Banana]

ggregg wrote:
which is the most efficient and fastest way to process the Detail records?

98% of time, a SQL query using joins. You didn't really explain what
you'd be doing with the data, but SQL, being a set-based language,
always beats out recordset which is going to be in a procedural language
doing one thing at a time.

 

[(PeteCresswell)]

Per ggregg:

Or is there a better way?
Thank you for any suggestions

Something that's probably not immediately relevant - due to all
the expertise applied to the OP - but which will eventually be
useful to you: The TickCount() API call.

Using it, you can put together a little routine that measures the
time diff between calls to it.

With such a routine it's easy to test two different approaches
and see which is faster. If the approaches do so little work
that thousandths of a second are too big, just write a loop to
repeat each approach 100, 1,000, 10,000 or however many times is
needed to get relevant info.

Code below is probably wretched excess, but you'll be able to get
the general idea from reading it.

----------------------------------------------------------------
Private Declare Function GetTickCount_lti Lib "kernel32" Alias
"GetTickCount" () As Long

Public Sub LogTime(ByVal theStartNewSwitch As Integer, ByVal
theLogEntry As String)
2000 debugStackPush mModuleName & ":LogTime"
2001 On Error GoTo LogTime_err

' PURPOSE: Provide a tool for benchmarking performance by
writing begin/end
' running times to an ASCII text file.
' ACCEPTS: Boolean telling whether-or-not this is the
beginning of a set of observations
' and a String describing the process being timed.
' USES: .INI file parameter "LogPath"
' OUTPUTS: ...to a log file
' NOTES: 1) Writes two times to the text file:
' > Since the previous invocation (i.e. the last
step...)
' > Cumulative since the first invocation with
'theStartNewSwitch=True'
' 2) The .INI file parameter does double duty. We
also use it as a switch to turn
' logging on and off. If not present, we exit
the this routine without making a log entry.
' 3) If we have a bad log file directory, we don't
want to trash up the error log with
' a bazillion entries (i.e. every time we call
LogTime()), hence the "BadLogFileNoted" switch.

2010 Static myLogPath As String
Static myCurrentUser As String
Static myMachineName As String
Static myCurrentVersion As String
Static SeqNum As Long
Static BadLogFileNoted As Boolean

Static prvTimeMS As Long ' ms marking, previous
time
Static beginTimeMS As Long ' ms marking, Beginning
time
Static sincePrevMS As Long ' ms marking, elspsed
since previous time
Static sinceBeginMS As Long ' ms marking, elspsed
since begin time
Dim currentMSMarking As Long ' ms marking, current
hash marking

Dim ParmValue As String
Dim L As Long
Dim i As Integer
Dim X As Integer

2020 Const defaultPath = "C:\TEMP"


2030 ParmValue = String(255, 0)
2031 ParmValue = Space(255)
2032 L = GetPrivateProfileString_lti("programParms", "LogPath",
"{NotFound}", ParmValue, 255, SysCmd(acSysCmdIniFile))

2040 If L And Left(ParmValue, 10) <> "{NotFound}" Then
2050 myLogPath = Left(ParmValue, L)
2060 If dirExist_lti(myLogPath) = False Then
2070 If BadLogFileNoted = False Then
2071 BugAlert False, "Invalid log path: '" & myLogPath &
"'."
2072 BadLogFileNoted = True
2073 End If
2080 Else
2100 If theStartNewSwitch = True Then
2110 myCurrentUser = CurrentUserGet()
2111 myMachineName = MachineNameGet()

2120 If Len(myMachineName) = 0 Then
2121 myMachineName = myCurrentUser
2122 End If

2130 If Len(myCurrentVersion) = 0 Then
2131 myCurrentVersion = currentVersionGet_lti()
2132 End If
2210 End If


2220 X = FreeFile

2240 myLogPath = myLogPath & "\" & "$" & myMachineName &
".txt"
2241 Open myLogPath For Append As X

2290 If theStartNewSwitch = True Then
2300 SeqNum = 0
2310 beginTimeMS = GetTickCount_lti
2320 prvTimeMS = beginTimeMS
2330 sinceBeginMS = 0
2340 sincePrevMS = 0

2350 Print #X, myCurrentVersion & "
--------------------- " & myCurrentUser & " " & Format$(Now,
"MM-DD-YYYY HH:NN:SS") & " -----------------------------"
2360 End If

2365 SeqNum = SeqNum + 1

2370 currentMSMarking = GetTickCount_lti '* Mark time in ms
2390 sinceBeginMS = currentMSMarking - beginTimeMS '*
Use same time marking in both calculations
2400 sincePrevMS = currentMSMarking - prvTimeMS '*
Use same time marking in both calculations

2410 Print #X, myCurrentVersion & " " & myCurrentUser & " "
& Format$(SeqNum, "@@@") _
& " " & Format(sincePrevMS \ 1000, "@@@@") &
"." & Format(sincePrevMS Mod 1000, "000") _
& " " & Format(sinceBeginMS \ 1000, "@@@@")
& "." & Format(sinceBeginMS Mod 1000, "000") _
& " " & theLogEntry

2500 prvTimeMS = currentMSMarking
2998 End If
2999 End If

LogTime_xit:
DebugStackPop
On Error Resume Next
Close #X
Exit Sub

LogTime_err:
BugAlert True, ""
Resume LogTime_xit
End Sub

 

[ggregg]

Thank you for replying. That was exactly what I was wondering about.
I appreciate it.

 

[James A. Fortune]

Access 2003
I have a table for Sales Orders and one for Sales Order Detail records
with
a one-to-many relationship defined on SalesOrderID.
The Sales Order table has over 50,000 records and the Detail has over
200,000.
Sometimes I need to loop through all the detail records for one Sales
Order.
Using an example of Sales Order ID #1, which is the most efficient and
fastest way to process the Detail records?

Set rcdSOD = SalesDB.OpenRecordset("SalesOrderDetail", DB_OPEN_DYNASET)
critSOD = "(SalesOrderID = 1)"
rcdSOD.FindFirst critSOD
Do Until rcdSOD.NoMatch
(process the data)
rcdSOD.FindNext critSOD
Loop

OR

critSOD = "Select * from SalesOrderDetail Where [SalesOrderID] = 1"
Set rcdSOD = SalesDB.OpenRecordset(critSOD)
rcdSOD.MoveFirst
Do Until rcdSOD.EOF
(process the data)
rcdSOD.MoveNext
Loop

OR create a Query that Filters the SalesOrderID to 1

Set rcdSOD = SalesDB.OpenRecordset("SalesOrderDetailQuery",
DB_OPEN_DYNASET)
rcdSOD.MoveFirst
Do Until rcdSOD.EOF
(process the data)
rcdSOD.MoveNext
Loop

Or is there a better way?
Thank you for any suggestions

Assuming that you *must* process the relevant records in code, and can't
create an update query to do the whole job, then your last two
approaches would be much more efficient than the first. I doubt there'd
be much difference in performance between those two.

Note, though, that in either case, your statement "rcdSOD.MoveFirst" is
both unnecessary and error-prone. It's unnecessary because any newly
opened recordset is always going to be positioned at the first record
(if there is one). It's error-prone (as written) because if the
recordset is empty, calling the MoveFirst method will raise an error.
So I would just do this:

Set rcdSOD = SalesDB.OpenRecordset( ... )
With rcdSOD
Do Until .EOF
(process the data)
.MoveNext
Loop
.Close
End With

First, try changing the DB_OPEN_DYNASET Access 2.0 constant to the
dbOpenDynaset constant used by later versions of Access. If
DB_OPEN_DYNASET works correctly it is due to some kind of compatibility
constant. It won't increase your efficiency, but it might make your
code more robust.

Recordsets generally beat SQL in more complex situations where factors
such as subqueries or multiple operations on groups of data can cause
repeated queries on a large table. In such cases, constructing the
recordset to make a single pass through the (large) main table, perhaps
including punctuated bits of dynamic SQL in the VBA, can sometimes lead
to impressive performance gains. Dirk's EOF method is nice, but I use
..MoveFirst (after checking .RecordCount against 0) because I like to
position the recordset explicitly, while reserving EOF techniques for
use with ADODB recordsets. I'm not sure why I prefer moving explicitly
to the first record, but I also try not to rely on default variable
settings either, so perhaps it simply raises my comfort level.

James A. Fortune
(e-mail address removed)

Paragraphs should be separated by a blank line or by indenting, but not
both. -- LaTeX style manual.

 

[Clif McIrvin]

...I'm not sure why I prefer moving explicitly to the first record,
but I also try not to rely on default variable settings either, so
perhaps it simply raises my comfort level.

Sounds like reasonable justification to me!

 

[ggregg]

Thank you for your suggestions

 

[ggregg]

Thank you for this information.
I've wondered about a way to time routines.
This will be very useful

 

[ggregg]

Just out of curiosity, when would the first approach ever be necessary?
Thanks

 

[ggregg]

Thank you for the info

 

[Dirk Goldgar]

Just out of curiosity, when would the first approach ever be necessary?

The first approach being to open a recordset on the whole table and loop
through all the records? I think James A. Fortune's reply discussed that.
There can be situations with complex logic, especially if the logic includes
references to other records in the table, which are most easily solved that
way. Sometimes it's *possible* to write a SQL statement that will do the
job, but the logic is so complex that it's easier to use a recordset. But a
good rule of thumb is: don't use a recordset when you can do the job with a
SQL statement, and if you do use a recordset, apply criteria to restrict the
recordset to just the records you need to process.

 

[ggregg]

I thought the first approach didn't loop through all the records because it
did the FindFirst based on the criteria (critSOD). This was the way I saw it
done in an Access book and I was using that method but it didn't seem to be
very efficient - the bigger the tables became, the slower it was. I recently
discovered using the other methods and I wasn't sure if they were any better.
Your rule of thumb makes a lot sense and I will go by that.
Thanks

 

[Dirk Goldgar]

I thought the first approach didn't loop through all the records because it
did the FindFirst based on the criteria (critSOD). This was the way I saw
it
done in an Access book and I was using that method but it didn't seem to
be
very efficient - the bigger the tables became, the slower it was.

FindFirst/FindNext doesn't use indexes, so it has to look through all the
records to find the ones that match. If an all-SQL approach won't work,
it's better to open the recordset on a query with criteria that limit the
records returned to just the ones you want to process. That allows the
database engine to use indexes if they exist. Even if the criteria fields
aren't indexed, allowing the database engine to apply the criteris will
almost always be faster than doing it yourself in a FindFirst/FindNext loop.

I recently
discovered using the other methods and I wasn't sure if they were any
better.
Your rule of thumb makes a lot sense and I will go by that.
Thanks

You're welcome.