Microsoft KB Archive/210545

= ACC2000: Seek Method Is Faster Than Find Method =

Article ID: 210545

Article Last Modified on 12/12/2002

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210545



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



SYMPTOMS
In Visual Basic for Applications, to find a record in an indexed field, the Seek method may be faster than the Find method, especially in a large table.



RESOLUTION
Use the Seek method on indexed fields to optimize your search speed.



MORE INFORMATION
When you perform a Seek, you are opening a table directly and moving to the record based on an index value. When you create a dynaset and perform a Find, you are checking the value of the field in every record until you find a match.

Steps to Reproduce the Behavior
 Open the sample database Northwind.mdb.  Create a new module and paste or type the following code: Function MakeTestTable Dim dbs As Database, tdf As TableDef Dim idx As Index, fld As Field

Set dbs = CurrentDb

DoCmd.SetWarnings False

'Run a make table query copying Orders to BigOrder. DoCmd.RunSQL "SELECT Orders.OrderID, Orders.CustomerID, " _ & "Orders.EmployeeID, Orders.OrderDate, " _ & "Orders.RequiredDate, Orders.ShippedDate, " _ & "Orders.ShipVia, Orders.Freight, Orders.ShipName, " _ & "Orders.ShipAddress, Orders.ShipCity, " _ & "Orders.ShipRegion, Orders.ShipPostalCode, " _ & "Orders.ShipCountry INTO BigOrders FROM Orders;"

' Make table queries do not copy indexes, so a new one ' must be created.

' Open the table definition. Set tdf = dbs.TableDefs("BigOrders")

' Create an index called PrimaryKey for this TableDef ' and turn on the Primary and Required properties.

Set idx = tdf.CreateIndex("PrimaryKey")

With idx .Name = "PrimaryKey" .Primary = True .Required = True .IgnoreNulls = False End With

' Create an index field with the same name as a table field, ' then append it to the index. Set fld = idx.CreateField("OrderID") idx.Fields.Append fld

' Append the new index to the TableDef. tdf.Indexes.Append idx

'Append the records of the Orders table to the new Big Orders table '300 times, thus making a large table for testing the functions. For I = 1 To 300 DoCmd.RunSQL "INSERT INTO BigOrders ( CustomerID, EmployeeID, " _   & "OrderDate, RequiredDate, ShippedDate, ShipVia, Freight,  " _    & "ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode,  " _    & "ShipCountry ) SELECT Orders.CustomerID, Orders.EmployeeID,  " _ & "Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, " _ & "Orders.ShipVia, Orders.Freight, Orders.ShipName, " _ & "Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, " _ & "Orders.ShipPostalCode, Orders.ShipCountry FROM Orders;" Next I

DoCmd.SetWarnings True

End Function

'============================================= 'The following function runs the Seek method. 'It quickly returns the Customer ID where the 'PrimaryKey field = 260066. '=============================================

Function FastSeek Dim db As Database, tbl As Recordset

Set db = CurrentDb Set tbl = db.OpenRecordset("BigOrders", DB_OPEN_TABLE)

tbl.Index = "PrimaryKey" tbl.Seek "=", 260066 Debug.Print tbl("CustomerID")

tbl.Close End Function

'============================================= 'The following function uses the Find method. 'It is slightly slower in returning the Customer '  ID where the PrimaryKey field = 260066. '============================================= Function SlowFindNext Dim Criteria As String, MyDB As Database, Myset As Recordset

Set MyDB = CurrentDb Set Myset = MyDB.OpenRecordset("BigOrders", DB_OPEN_DYNASET) Criteria = "[OrderID] =" & 260066

Myset.FindNext Criteria Debug.Print Myset("Customerid") End Function   Type the following line in the Immediate window, and then press ENTER: ?MakeTestTable NOTE: You may have to wait several seconds for this function to create the test table.   Type the following line, and then press ENTER: ?FastSeek   Type the following line, and then press ENTER: ?SlowFindNext </ol>

Note that the FastSeek function is slightly faster than the SlowFindNext function.

<div class="references_section">