|
|
David W. Fenton wrote:
> Bri <not@here.com> wrote in news:kXcZf.2467$nf7.1564@pd7tw1no:
>
>
>
>
> I don't see where your tests prove that at all.
The test as written does indeed prove this. Opening the recordset on a
Table in both the tests (dbOpenTable and dbOpenSnapshot) was too fast to
measure. The Seek was extremely fast while the FindFirst took 3+ seconds
(for one find). There can't be any more definative proof than that.
Unless you can see a flaw in my logic and/or code.
>
>
> The idea that FindFirst is not using the index seems wrong to me.
Seems wrong to me too, but it is the only explaination I have for a 3+
sec find.
> I think there's something not right about your tests, but I'm not
> interested in running the tests myself.
I can't see anything obviously wrong. I only included the loops so there
would be something to measure. In the FindFirst case, there was plenty
to measure with only one pass.
> I can't think of a situation
> where I'd use FindFirst within a recordset where SEEK would be an
> option
I can't either. Since 99% of my apps are split, the option of Seek is
not the first thing I think of (yes, I could open a Database variable on
the BE to get past the split). I would be much more likely to try and
come up with an SQL string that would get me what I needed.
> (I do use it in one application where the recordset has
> multiple tables in it and sums certain fields; SEEK simply can't be
> used there, so FindFirst is the only option; the opening of the
> recordset with the sums takes most of the time, and I persist it so
> that this hit is taken only once). But I've also never had a
> scenario where I needed to use SEEK at all, since I just don't do
> much moving around recordsets in the first place, and when I do,
> it's almost always a multi-table recordset, so SEEK wouldn't be an
> option in the first place.
Agreed.
> I repeat that I see no purpose in using SEEK to find a single
> record.
Agreed. For a single record, the Seek is faster than the Where clause,
but not by a noticable amount to the user.
> Keep in mind that your WHERE clause retrieval may be impacted by
> Jet's internal caching. The order of operations might make a
> difference. I think you'd want to run the tests after a reboot of
> your computer (to clear the disk cache and any Access caching).
I tried the test again with the query first, then the Seek, then the
FindFirst and got the same ratio. I also did the FindFirst first, then
the query, then the Seek. The FindFirst was still the same, but the Seek
was only 2.5 times faster (vs 3x).
So, my conclusions remain the same:
- Speed order in real measurements: Seek, Where, FindFirst
- Practical order in real life: Where, FindFirst, Seek
--
Bri
|
|