Using Find

The Find methods are useful for moving to a record that meets a specific criteria within a recordset. A common trap when converting code to work with an ODBC database is to try to replace Seek method calls with Find method calls. As a general rule, this code will be slow.

When trying to find an individual record, the best approach is to create a new recordset with a WHERE clause that contains the Find criteria. This will return all records that match the criteria. Because this is a single operation on the server, this will be as efficient as possible. It may be necessary to change forms or other visual elements to use this approach, but this is generally preferable – especially when large tables are involved.

Find With Dynasets

If your program can't be modified to avoid using Find methods, then understanding exactly how they work will help you make the best use of them. Find will be fairly efficient if the following two criteria are met:

• The search criteria is a match against a single column that is indexed on the server.

• The search expression is either testing for equality or uses LIKE.

Jet recognizes the above cases and executes a query on the server that returns primary keys for all the records that match the search criteria. If matching records are found, it then moves through the keys it has stored for the recordset until it finds a match. If the local recordset is reasonably sized (say less than 500 rows), this is a relatively quick operation.

One result of the above strategy is that unsuccessful Finds will be as fast as the server allows. This isn't something you'll be able to take advantage of in all scenarios(!), but it is may be useful in some.

An example of a reasonably efficient use of Find is if you want to match a lastname in a set of records (assuming lastname was indexed on the server):

RS.FindFirst "lastname = ""Smith"""
RS.FindFirst "lastname Like ""Smith*"""

Find With Snapshots

Find methods will also be reasonably efficient if:

• The recordset is a snapshot type recordset.

• The number of records in the snapshot is reasonable (say < 500, depending on the speed of your Microsoft Windows® TEMP directory and memory available for buffering)

In the above case, the Find criteria are resolved by searching the data that has already been downloaded into the snapshot on the local machine. Of course, if the snapshot is not yet fully populated, that time to fetch the data will add to the search time. This means that Finds that fail will be slow if all the records will have to be fetched, unlike the dynaset case above where no further fetching will be done if the Find fails.

Slow Find

Find methods will usually be slow if:

• The recordset is a dynaset type recordset on an ODBC data source.

• The number of records in the recordset is more than, say, one hundred.

• The Find criteria do not match the restrictions given previously.

The algorithm used to resolve the criteria in this case is to fetch each record and see if the criteria match until a match is found or all records have been examined.

At the risk of stating the obvious, don't be tempted to avoid using Find methods and code the equivalent loop in Microsoft Basic! That is guaranteed to be the slowest technique of all.