Faster FindFirst?

[supmktg]

I am comparing records in two dao recordsets using FindFirst.
rstOne is based on an attached table with about 90,000 records.
rstTwo is based on a table in the currentdb and has about 1,600.
It is taking about 5 minutes to compare all 1,600 records against the 90,000.

Here is my code:

Dim projDB as DAO.Database
Dim rstOne as DAO.Recordset
Dim rstTwo as DAO.Recordset

Set projDB = OpenDatabase("T:\Proj.mdb")
Set rstOne = projDB.OpenRecordset("tblOne", dbOpenSnapshot)
Set rstTwo =CurrentDB.OpenRecordset("tblTwo", dbOpenSnapshot)

Do Until rstTwo.EOF

rstOne.FindFirst "ID = '" & rstTwo![ID] & "' AND Street='" & rstTwo![Street] & "'"
            If Not rstOne.NoMatch Then
            'do stuff
            End If

rstTwo.MoveNext
Loop

rstOne.close
rstTwo.close
Set rstOne = Nothing
Set rstTwo = Nothing

I sped things up substantially by changing to dbOpenSnapshot, but it is still taking 5 minutes or more. Any ideas on how to speed this up some more?

Thanks,
Sup

 

[MarkK]

Don't use findfirst if you want speed. Use your findfirst criteria in the where clase of a new recordset that contains exactly what you want.
For even more speed make sure any fields you use to sort or search are indexed.
For even more speed write a query that takes parameters for the where clause and open a recordset from that.
Cheers,

Sub Test2803754()
   Dim dbs As DAO.Database
   Dim qdf As DAO.QueryDef
   Dim rst As DAO.Recordset
   
[COLOR="Green"]   'open the remote database object[/COLOR]
   Set dbs = OpenDatabase("T:\Proj.mdb")
[COLOR="Green"]   'create the 'findfirst' query def[/COLOR]
   Set qdf = dbs.CreateQueryDef("", _
      "SELECT * " & _
      "FROM tblRemote " & _
      "WHERE ID = p0 " & _
         "AND Street = p1")
   
[COLOR="Green"]   'open local recordset, also used as loop control structure[/COLOR]
   With CurrentDb.OpenRecordset("tblLocal", dbOpenSnapshot)
[COLOR="Green"]      'traverse local recordset[/COLOR]
      Do While Not .EOF
[COLOR="Green"]         'set querydef parameters from current record of local recordset[/COLOR]
         qdf.Parameters(0) = !ID
         qdf.Parameters(1) = !Street
[COLOR="Green"]         'open final recordset, no 'findfirst' required, since the criteria are paramterized...
         '...and applied in the where clause.[/COLOR]
         Set rst = qdf.OpenRecordset
         
[COLOR="Green"]         'do stuff[/COLOR]
         
         .MoveNext
      Loop
      .Close
   End With
   
End Sub

See if you can make any sense of that. It'll be fast. Also, make sure that you have an index on the fields ID and Street in T:\Proj.mdb
Mark

 

[supmktg]

Wow! What a difference. From 5 minutes to about 40 seconds.

Thank you VERY much!
Sup