Microsoft KB Archive/108380

= PRB: Find Methods Don't Use Indexes to Speed Up VB Data Access =

Article ID: 108380

Article Last Modified on 1/8/2003

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition

-



This article was previously published under Q108380



SYMPTOMS
The find methods take longer to find farther records than nearer records. For example, the FindFirst method takes longer to find records that are farther into the Dynaset or Snapshot. Using FindFirst, FindNext, or FindLast on indexed fields isn't any faster than on non-indexed fields.



CAUSE
Creating a Dynaset or Snapshot is relatively fast because of indexes. Indexes are used when you create the Dynaset or Snapshot, such as when you specify a selection criteria or an ORDER BY clause in the CreateDynaset method. However, after the Dynaset or Snapshot is created, it does not use the indexes for searches.

The find and move methods always search sequentially within the current Dynaset or Snapshot. The find and move methods don't use indexes. For example, the FindLast and MoveLast methods require reading the entire recordset before processing continues.

The find methods are: FindFirst, FindLast, FindNext, and FindPrevious. The move methods are: MoveFirst, MoveLast, MoveNext, and MovePrevious.



WORKAROUND
Repositioning the record pointer to a location far into a large Dynaset or Snapshot may be faster if you re-create the Dynaset or Snapshot instead of searching the existing one.

For example, you could change the RecordSource property of a data control at run time as follows: txtSearchString = "target string to find" ' Place the following two lines on one, single line: data1.Recordsource = "select * from mytable where myfield = '" & txtSearchString & "' order by myotherfield" data1.Refresh  ' Update the data control with the new Recordset You can also open a Table object in addition to the Dynaset or Snapshot. You can use the Seek method on the Table to find a specific record keyed by an index.



STATUS
This behavior is by design.

