|
|
Steve wrote:
> "David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in message
> news:Xns979C8FE391DB
2f99a49ed1d0c49c5bbb
2@127.0.0.1...
>
> <snipped>
>
>
>
> You lost me. Both Seek and FindFirst requires a recordset.
>
> ?
>
> Steven
From Help:
Seek - "Locates the record in an indexed table-type Recordset object
that satisfies the specified criteria for the current index and makes
that record the current record (Microsoft Jet workspaces only)."
FindFirst, etc - "Locates the first, last, next, or previous record in a
dynaset- or snapshot-type Recordset object that satisfies the specified
criteria and makes that record the current record (Microsoft Jet
workspaces only)."
Therefore, Seek must use a local table only, FindFirst can use a Table
(local or linked), query, ODBC Table/View/SP, etc.
As for the difference in efficiencies between the two scenarios, the
Seek is almost three times faster than a query. I just did this test on
a table of almost 200k records. The record searched for is the last
value of the selected index. My results:
CompareSeekAndQuery
Seek - 0.7734375
Query Dynaset - 2.054688
Query Snapshot - 2.050781
My Code:
Sub CompareSeekAndQuery(
)
Dim stSQL As String, db As DAO.Database, rs As DAO.Recordset, stResult
As String
Dim i As Integer, sTimer As Single
Set db = CurrentDb()
'First lets do the Seek
sTimer = Timer()
For i = 1 To 1000
Set rs = db.OpenRecordset("Action", dbOpenTable)
rs.Index = "ACIMPID"
rs.Seek "=", "VVA994"
stResult = rs("ImportID")
rs.Close
Set rs = Nothing
Next
Debug.Print "Seek - " & Timer() - sTimer
'Next lets do the Query with Dynaset
stSQL = "SELECT Action.ImportID FROM Action WHERE ACIMPID='VVA994'"
sTimer = Timer()
For i = 1 To 1000
Set rs = db. OpenRecordset(stSQL,
dbOpenDynaset)
stResult = rs("ImportID")
rs.Close
Set rs = Nothing
Next
Debug.Print "Query Dynaset - " & Timer() - sTimer
'Next lets do the Query with Snapshot
stSQL = "SELECT Action.ImportID FROM Action WHERE ACIMPID='VVA994'"
sTimer = Timer()
For i = 1 To 1000
Set rs = db. OpenRecordset(stSQL,
dbOpenSnapshot)
stResult = rs("ImportID")
rs.Close
Set rs = Nothing
Next
Debug.Print "Query Snapshot - " & Timer() - sTimer
db.Close
Set db = Nothing
End Sub
--
Bri
|
|