Re: Trouble with DAO "SEEK" in converting application to SQL Express back end.

Bri <not@xxxxxxxx> wrote in 3ceZf.2940$_u1.1473@pd7tw2no:">news:3ceZf.2940$_u1.1473@pd7tw2no:

David W. Fenton wrote:
Bri <not@xxxxxxxx> wrote in kXcZf.2467$nf7.1564@pd7tw1no:">news:kXcZf.2467$nf7.1564@pd7tw1no:


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. . . .

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.

Well, I don't think your test is very realistic -- 200K records is
an awful lot to open at once.

. . . 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.

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 just can't help but think that there's something else going on
here because I wouldn't expect that kind of difference.

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.

Is the data local or on a network? Granted, networked is likely to
be a production environment, but it seems like if you're going to
use a high-end test (a 200K-record table), then you should use the
most high-performance scenario. Either that, or use a more realistic
table.

I'd be interested to hear what happens with a 20K-record table, for
instance.

It also makes no sense to me that all of this would take that long
in a recordset since I have apps that basically do the same thing in
bound forms that don't take anything like that amount of time
(though the tables are 200K records in most of the apps, but some of
them are still close to 100K records).

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.

The main reason for me is that most of the time I'd want to jump
around a recordset I'd need data from more than one table.

(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.

Use of SEEK in that scenario would seem to me to be a clear case of
premature optimization.

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

I'd be interested if the slow FindFirst appears after passing some
threshold in the number of records. I assume you were using an
Autonumber PK for the index you were seeking on? It could be that
there are differences in FindFirst's efficiency with different data
types, since that might have an important impact on how many data
pages are in the index and need to be retrieved.

Perhaps FindFirst is doing a scan of the index, whereas SEEK is
doing nodal jumps through the b-tree. If that's the case, it would
cause me to wonder why FindFirst would have been engineered in that
manner.

It would also be useful to see how much FindFirst is slowed down by
dropping the index. That would definitely show if the FindFirst is
not using the index at all or if it's just using it less efficiently
than SEEK.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
.

Relevant Pages