|
|
David W. Fenton wrote:
> Well, let me make up some numbers to illustrate my point.
>
> If opening a recordset takes 250 milliseconds and SEEK takes 1ms to
> find a particular record and FindFirst takes 3ms, there isn't any
> real difference between the two methods for finding a single record
> -- one takes 251ms, the other 253ms. From the end-user point of
> view, the one using the application where you are performing this
> operation, the difference is indistinguishable.
>
> However, if you're repositioning the recordset pointer 1000 times,
> then you've got a difference of 1250ms vs. 3250ms. That's still a
> difference of only 2 seconds, but it's a difference I'd want to
> eliminate if a user is waiting on something to happen.
>
> I don't have the links for this, but I'm pretty sure my numbers here
> for the relative efficiency of SEEK vs. FindFirst are about right
> (this was investigated at length by folks here in this newsgroup
> after the authors of the ADH made a big deal about it; they made the
> same big deal about CurrentDB vs. DBEngine(0)(0), too, and the exact
> same caveats apply -- in a loop, the faster one makes sense; the
> only difference is that there's no reason to use DBEngine(0)(0) in a
> loop; but I digress). And the point is that for finding a single
> record, either is so fast that it doesn't make a difference (and for
> finding a single record it's much more efficient from a network
> point of view to use a WHERE clause, instead, as we both agree).
I thought I had remembered the same sort of comparitive values for these
operations. I setup a test to (I thought) verify this. See my response
to Steve yesterday. In it I compared Seek to a query with a Where
clause. In a loop that I executed 1000 times, I opened the recordset
then used Seek to find the last record in the Table of ~200k records. I
then did a similar loop that opened the recordset using a Where clause
to pull the same record. Both loops opened a recordet and closed the
recordset, so the difference in time should just be the method for
getting the desired record. I was surprised to see that Seek was ~three
times faster than the Where clause.
In your message above you speek of comparing Seek to FindFirst, so I
added a loop that did the FindFirst. After more than TEN MINUTES I gave
up and stopped the code to find it had only done 250 loops of the
FindFirst. So, I reduced it to 10 loops and ran again. My results were:
Seek (1000) - 0.8710938
Query (1000) - 2.285156
FindFirst (10) - 37.97266
This ment that each FIndFirst loop was over 3 secs. So, I thought, maybe
opening a recordset on a table was slow as a Dynaset vs a TableOnly. I
eliminated the loop and put in additional timer points. My results were:
Seek (1000) - 0.7929688
Query (1000) - 2.164063
Open rs - 0
FindFirst - 3.34375
FindFirst finish - 3.363281
My hypothesis was wrong. Opening the recordset was so fast it didn't
even register, the FindFirst was taking the 3+ secs. If I was to guess,
I would say that FindFirst does not use the Index at all but does a
sequential scan of the recordset.
Now, personally I do not open whole tables and use FindFirst to find a
record. I would say that 90% of my use of FindFirst is agains a
RecordsetClone of a Form to position the form on a searched for record.
In these cases, the Forms Recordsource has already used a Where clause
to limit the form to a subset of the Table so FindFirst is only scanning
a relatively small no of records.
The bottom line after this testing, your statement that if you need to
reposition the pointer in a Recordset numerous times that Seek is the
fastest is true. Your statement that to find a single record Seek and
FindFirst are indistinguishable to the user is not true. Your statement
that a Where clause is the fastest of all is also not true, but in most
circumstances it is indistinguishable to the user and is certainly the
most flexible.
I offer my final code for my test below. If you can find something I've
done wrong or assumed wrong that would invalidate this test, I would
welcome the feedback.
--
Bri
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 (1000) - " & Timer() - sTimer
'Next lets do the Query
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 (1000) - " & Timer() - sTimer
'Next lets do the FindFirst
sTimer = Timer()
'For i = 1 To 10
Set rs = db.OpenRecordset("Action", dbOpenSnapshot)
Debug.Print "Open rs - " & Timer() - sTimer
rs.FindFirst "ACIMPID='VVA994'"
Debug.Print "FindFirst - " & Timer() - sTimer
stResult = rs("ImportID")
rs.Close
Set rs = Nothing
'Next
Debug.Print "FindFirst finish - " & Timer() - sTimer
db.Close
Set db = Nothing
End Sub
|
|