Performance: FindFirst, FindNext vs Open New Recordset

  • Thread starter Danny J. Lesandrini
  • Start date
D

Danny J. Lesandrini

I'm working on a database application created by a different developer
and he does something I never have. Wondering if his approach is better
than what I'm accustomed to doing.

Open first recordset of "Types", which will have 2 to 4 records:

Type
--------
Red
Yellow
Blue

Next, he opens another recordset to find the "SubTypes"

Type SubType
---------- ---------------------
Red Apple
Blue Sno Cone
Red Strawberry
Yellow Bannana


The second recordset is opened without sorting or filtering and he uses
the FindFirst and FindNext methods to locate associated records while
looping through the first recordset. (caution, air code follows)

Do Until rstType.EOF
sType = rstType!Type
sCriteria = "[Type]='" & sType & "'"

rstSubType.FindFirst sCriteria
Do While Not rsProgs.NoMatch
' do stuff here
rstSubType.FindNext sCriteria
Loop
rstType.MoveNext
Loop

I changed the code so that it creates a new, filtered, ordered recordset
of subtypes and processes them all, since they all are associated with Type.

Do Until rstType.EOF
sType = rstType!Type
sSQL = "Select * From SubTypes WhereType='" & sType & "' Order By SubType"

Set rstSubType = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
Do Until rstSubType.EOF
' do stuff here
rstSubType.MoveNext
Loop
Set rstSubType = Nothing
rstType.MoveNext
Loop


I like the cleanness of my method better, but what are your expectations on
performance? These are used to load a TreeView control and will never
contain more than a handfull of values for either Type or SubType.
 
R

Roger Carlson

I would expect that your method is faster. FindFirst is notoriously slow.

However, the expection of performance is less important than the actuality.
You've got both pieces of code, why not test them and see? Add a
Debug.Print Now() line before and after the code. If the difference is too
small to matter then it doesn't matter. If you want to see theoretically,
try a larger dataset until you find a difference.

Then report back <grin>

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
D

Danny J. Lesandrini

Good point. It was a trivial matter to test it and in the end, I didn't like
the outcome, so I'm going to ignore it <grin>

Open New Recordset Method: .000011574 s
FindFirst/FindNext Method: 0 s

I should mention that the Open New method reported the same elapsed
time thrice in a row, while the Find Method reported zero twice and the
same .000011574 seconds once. So, they are probably pretty close, when
handling a dozen records or less, but I'll have to create a different test
harness to test thousands of records.
 
A

Albert D.Kallal

Openign a reocrdset is loop?

NO...that is going to hit you hard....

Further, remember, opening a reocrdset is much like using
a helicopter to cross a street. By the time you get the helicopter
up to speed, and the machine becomes airborne..I could have
EASILY crossed the street by foot. Gee,
walking is faster then a helicopter?

Remember, opening a reocrdset inside of loop can slow the code down to a
crawl. (ms-access has to wait for the file system (operating system) to open
the table..and load up things...this takes for ever). In the time it takes
to open and
setup a reocrdset...ms-access usually can have loaded about 100,000 records.

So, for example if you have code that loops, and each iteration of the loop
creates a reocrdset, then have to eliminate that (it is like starting up the
helicopter each time to do something....).

The cross over point is usually around 8000 records (so, if your data sets
are smaller then about 8000 records....open the recordset...and use
find first).

Test the results with the "timer" function...and see....

but, creation of a recordset in a loop is expensive...very expensive in
terms of time...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal





Danny J. Lesandrini said:
I'm working on a database application created by a different developer
and he does something I never have. Wondering if his approach is better
than what I'm accustomed to doing.

Open first recordset of "Types", which will have 2 to 4 records:

Type
--------
Red
Yellow
Blue

Next, he opens another recordset to find the "SubTypes"

Type SubType
---------- ---------------------
Red Apple
Blue Sno Cone
Red Strawberry
Yellow Bannana


The second recordset is opened without sorting or filtering and he uses
the FindFirst and FindNext methods to locate associated records while
looping through the first recordset. (caution, air code follows)

Do Until rstType.EOF
sType = rstType!Type
sCriteria = "[Type]='" & sType & "'"

rstSubType.FindFirst sCriteria
Do While Not rsProgs.NoMatch
' do stuff here
rstSubType.FindNext sCriteria
Loop
rstType.MoveNext
Loop

I changed the code so that it creates a new, filtered, ordered recordset
of subtypes and processes them all, since they all are associated with
Type.

Do Until rstType.EOF
sType = rstType!Type
sSQL = "Select * From SubTypes WhereType='" & sType & "' Order By
SubType"

Set rstSubType = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
Do Until rstSubType.EOF
' do stuff here
rstSubType.MoveNext
Loop
Set rstSubType = Nothing
rstType.MoveNext
Loop


I like the cleanness of my method better, but what are your expectations
on
performance? These are used to load a TreeView control and will never
contain more than a handfull of values for either Type or SubType.
--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast
Click to expand...
 
D

Danny J. Lesandrini

I performed the test on bigger recordsets. The parent, type, had 1500 members
while the child, subtype, processed 6531 records. (I purposely filtered out parent
records that had no related child records, so there would always be matches.)

Find First / Find Next Method: 66 seconds (second test 66 seconds)
Open New Recordset Method: 69 seconds (second test 69 seconds)

Next, I ran it for the whole parent recordset, including 3747 parent records that
had no related child records. Here are those results.

Find First / Find Next Method: 91 seconds
Open New Recordset Method: 118 seconds

So, it seems that maybe where there are numerous no-matches, the recordset
instantiation is costly. Where there are matches, it's pretty close.
 
Top