Hello,
I have an application that I'm converting to Access 2003 and SQL Server 2005
Express. The application uses extensive use of DAO and the SEEK method on
indexes. I'm having an issue when the recordset opens a table. When I
write
Set rst = db.OpenRecordset("MyTable",dbOpenTable, dbReadOnly)
I get an error. I believe it's invalid operation or invalid parameter, I'm
not in front of the application at the moment, but will let you know if it's
important.
When I remove the dbOpenTable, it works but I can't use the SEEK method on
the index.
Any ideas?
Thanks!
59  7417 
"Rico" <r c o l l e n s @ h e m m i n g w a y . c o mREMOVE THIS
PART IN CAPS> wrote in news:bDUWf.202692$H%4.69991@pd7tw2no: I have an application that I'm converting to Access 2003 and SQL
Server 2005 Express. The application uses extensive use of DAO
and the SEEK method on indexes. I'm having an issue when the
recordset opens a table. When I write
Set rst = db.OpenRecordset("MyTable",dbOpenTable, dbReadOnly)
I get an error. I believe it's invalid operation or invalid
parameter, I'm not in front of the application at the moment, but
will let you know if it's important.
When I remove the dbOpenTable, it works but I can't use the SEEK
method on the index.
Is SEEK a Jet-only operation? Given that it's based on indexes, I'd
think that it would be.
I don't think there are very many situations at all where SEEK is
justified at all. Either filter your recordset or use .FindFirst.
The latter may very well be 10 times slower than SEEK, but that
would only matter if you're doing loops that require hundreds of
thousands of operations that reposition the current record pointer.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
"Rico" wrote When I remove the dbOpenTable, it works but
I can't use the SEEK method on the index.
You have never been able to use dbOpenTable or Seek on anything but native
Jet tables in the same MDB/MDE. As far as I know, that is well-documented.
My personal view is that use of SEEK most often implies a poor design --
there may be a few cases in which it is needful to open a dataset with a
large number of records, and then find the one you want (SEEK if you can
open as a Table, or FIND / FINDFIRST if opened as a Dynaset).
It is more efficient, especially if you are using an Access client with a
Server DB (as you are now), to include criteria in your Query or SQL
statement so that the extraction of the one record you want is performed at
the server (or no record if it does not exist). It is really amazing to
realize just how often we need only one record (if it exists) or none (if it
does not exist).
Larry Linson
Microsoft Access MVP
SEEK is a fast native Access/Jet method.
If you want the same performance from SQL Server 2005,
you are going to need to use SQL Server native methods.
However, there are alternatives. At this point, you have
the choice of using ADO methods, DAO methods, or Stored
Procedures.
I can't say that there is much to choose between them.
Make a personal decision, flip a coin, or ask a friend.
Then ask here for an example using your chosen technology.
You will have to give an example of what you are trying
to do, because there is no direct plug-in replacement.
(david)
"Rico" <r c o l l e n s @ h e m m i n g w a y . c o mREMOVE THIS PART IN
CAPS> wrote in message news:bDUWf.202692$H%4.69991@pd7tw2no... Hello,
I have an application that I'm converting to Access 2003 and SQL Server
2005 Express. The application uses extensive use of DAO and the SEEK
method on indexes. I'm having an issue when the recordset opens a table.
When I write
Set rst = db.OpenRecordset("MyTable",dbOpenTable, dbReadOnly)
I get an error. I believe it's invalid operation or invalid parameter,
I'm not in front of the application at the moment, but will let you know
if it's important.
When I remove the dbOpenTable, it works but I can't use the SEEK method on
the index.
Any ideas?
Thanks!
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in
news:44***********************@lon-reader.news.telstra.net: SEEK is a fast native Access/Jet method.
If you want the same performance from SQL Server 2005,
you are going to need to use SQL Server native methods.
However, there are alternatives. At this point, you have
the choice of using ADO methods, DAO methods, or Stored
Procedures.
I can't say that there is much to choose between them.
Make a personal decision, flip a coin, or ask a friend.
From ADO help:
"Indexes on fields can greatly enhance the performance of the Recordset
object's Find method and Sort and Filter properties. You can create an
internal index for a Field object by setting its dynamic Optimize
property."
--
Lyle Fairfield
"Larry Linson" <bo*****@localhost.not> wrote in
news:Vr_Wf.13970$VL2.5902@trnddc04: You have never been able to use dbOpenTable or Seek on anything
but native Jet tables in the same MDB/MDE. As far as I know, that
is well-documented.
Er, you mean in the same MDB as referenced by the database object
with which you created the recordset.
It's perfectly easy to open a different database that has the tables
in it, assign it to a db variable, and then open a table-type
recordset on it.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Seek is the fastest method to find a record in an MDB database using DAO,
and generates the least network traffic. FindFirst is one of the slowest.
Steven
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1... "Rico" <r c o l l e n s @ h e m m i n g w a y . c o mREMOVE THIS
PART IN CAPS> wrote in news:bDUWf.202692$H%4.69991@pd7tw2no:
I have an application that I'm converting to Access 2003 and SQL
Server 2005 Express. The application uses extensive use of DAO
and the SEEK method on indexes. I'm having an issue when the
recordset opens a table. When I write
Set rst = db.OpenRecordset("MyTable",dbOpenTable, dbReadOnly)
I get an error. I believe it's invalid operation or invalid
parameter, I'm not in front of the application at the moment, but
will let you know if it's important.
When I remove the dbOpenTable, it works but I can't use the SEEK
method on the index.
Is SEEK a Jet-only operation? Given that it's based on indexes, I'd
think that it would be.
I don't think there are very many situations at all where SEEK is
justified at all. Either filter your recordset or use .FindFirst.
The latter may very well be 10 times slower than SEEK, but that
would only matter if you're doing loops that require hundreds of
thousands of operations that reposition the current record pointer.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
"Steve" <st***@nospam.net> wrote in news:Fk***************@fe10.lga: Seek is the fastest method to find a record in an MDB database
using DAO, and generates the least network traffic. FindFirst is
one of the slowest.
But if you're trying to find only one record, you'd use a WHERE
clause, and it will be faster than either SEEK or FindFirst.
If you're jumping around a recordset (which is the only reason you'd
ever use SEEK), then the difference between SEEK and FindFirst will
be obvious to an end user only if you're moving to thousands of
records.
The circumstances where that is a justifiable design are very few.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
See http://support.microsoft.com/kb/143237/en-us for an example provided by
Microsoft when seek is the most efficient method.
Steven
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1... "Steve" <st***@nospam.net> wrote in news:Fk***************@fe10.lga:
Seek is the fastest method to find a record in an MDB database
using DAO, and generates the least network traffic. FindFirst is
one of the slowest.
But if you're trying to find only one record, you'd use a WHERE
clause, and it will be faster than either SEEK or FindFirst.
If you're jumping around a recordset (which is the only reason you'd
ever use SEEK), then the difference between SEEK and FindFirst will
be obvious to an end user only if you're moving to thousands of
records.
The circumstances where that is a justifiable design are very few.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Yes seek is fast.
So is a BMW M series.
But more often than not a Toyota Corlloa will do the trick without the
overhead of the BMW.
I have used seek extensively. But the only place that it pays its way
is when I want to do multiple (as in thousands or more) finds in the
same recordset. As seek seems not to be supported in MS-SQL I have
abandoned it pretty much all together.
Most of MS's help and kb articles are just crap ...
(my evil twin Kyle adds, "Except when they agree with him in which case
he quotes them profusely!")
"Steve" <st***@nospam.net> wrote in news:i4************@fe08.lga: "David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1... "Steve" <st***@nospam.net> wrote in
news:Fk***************@fe10.lga:
Seek is the fastest method to find a record in an MDB database
using DAO, and generates the least network traffic. FindFirst
is one of the slowest.
But if you're trying to find only one record, you'd use a WHERE
clause, and it will be faster than either SEEK or FindFirst.
If you're jumping around a recordset (which is the only reason
you'd ever use SEEK), then the difference between SEEK and
FindFirst will be obvious to an end user only if you're moving to
thousands of records.
The circumstances where that is a justifiable design are very
few.
See http://support.microsoft.com/kb/143237/en-us for an example
provided by Microsoft when seek is the most efficient method.
I did not say SEEK was not the fastest method. I just said it was
not necessary in the vast majority of situations. The example you
cite is a case that fits the criteria I gave, which was that you
needed to do thousands of jumps around a recordset.
Those kinds of scenarios are really not very common at all.
In 10 years of full-time professional Access development I've never
needed to use a single SEEK, and every application I've ever done
has used a Jet back end.
I'm not entirely convinced there's no SQL solution to the moving
average problem, though it is likely to be slower than the
sequential solution given in the KB article (which uses an array for
retrieval of the averages into the query), since it will need to run
a nested subquery for every line of the query.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
David W. Fenton wrote:
<> Those kinds of scenarios are really not very common at all.
In 10 years of full-time professional Access development I've never
needed to use a single SEEK, and every application I've ever done
has used a Jet back end.
I've generally only used it when processing large amounts of data (eg.
importing a massive phone bill file and processing it against job records to
see which phone calls/costs were associated with which jobs (number,
start/end date/time). Obviously seek made a huge difference.
<>
--
regards,
Br@dley
Thanks!
I figured there likely wasn't a direct solution, but thought I'd ask anyway.
You're right, plenty of bad design in this application. As bad as it is, I
have to try and make it work as quickly / cheaply as I can. It's strange
because the way it's designed, in 90% of the occurrences a where statement
could have been used eliminating additional text / keystrokes.
That's what I wound up doing is replacing the seeks with where statements,
and although there were a couple of spots that the seek should have been
used, the performance hit wasn't noticable.
This is the least of the "bad design" points, this app is a NIGHTMARE! It's
obvious that the guy who wrote this never thought anyone else would support
it. And the author obviously isn't afraid of work, because the way it's
designed is very labour intensive to support.
Thanks for the info, it was very informative.
Rick
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1... "Rico" <r c o l l e n s @ h e m m i n g w a y . c o mREMOVE THIS
PART IN CAPS> wrote in news:bDUWf.202692$H%4.69991@pd7tw2no:
I have an application that I'm converting to Access 2003 and SQL
Server 2005 Express. The application uses extensive use of DAO
and the SEEK method on indexes. I'm having an issue when the
recordset opens a table. When I write
Set rst = db.OpenRecordset("MyTable",dbOpenTable, dbReadOnly)
I get an error. I believe it's invalid operation or invalid
parameter, I'm not in front of the application at the moment, but
will let you know if it's important.
When I remove the dbOpenTable, it works but I can't use the SEEK
method on the index.
Is SEEK a Jet-only operation? Given that it's based on indexes, I'd
think that it would be.
I don't think there are very many situations at all where SEEK is
justified at all. Either filter your recordset or use .FindFirst.
The latter may very well be 10 times slower than SEEK, but that
would only matter if you're doing loops that require hundreds of
thousands of operations that reposition the current record pointer.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
My experience has been different than yours. The moving average type of
calculation is typical in the type of systems that I have worked on.
I am not sure why "seek" in your eyes is such an evil method. Yes, it can't
be upsized to SQL Server, but many of us, including you, do not see the need
to upgrade to SQL Server. It can't be used on a query, but for finding a
single record in a table, the coding is efficient, and the method is quick.
Steven
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1... I did not say SEEK was not the fastest method. I just said it was
not necessary in the vast majority of situations. The example you
cite is a case that fits the criteria I gave, which was that you
needed to do thousands of jumps around a recordset.
Those kinds of scenarios are really not very common at all.
In 10 years of full-time professional Access development I've never
needed to use a single SEEK, and every application I've ever done
has used a Jet back end.
I'm not entirely convinced there's no SQL solution to the moving
average problem, though it is likely to be slower than the
sequential solution given in the KB article (which uses an array for
retrieval of the averages into the query), since it will need to run
a nested subquery for every line of the query.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
"Steve" <st***@nospam.net> wrote in news:mc***********@fe10.lga: I am not sure why "seek" in your eyes is such an evil method.
Yes, it can't be upsized to SQL Server, but many of us, including
you, do not see the need to upgrade to SQL Server. It can't be
used on a query, but for finding a single record in a table, the
coding is efficient, and the method is quick.
I have never said SEEK is "evil." I am only saying that there aren't
taht many circumstances where it is worth using.
And finding a *single* record in a table is the absolute worst
scenorio you could describe for using SEEK. Limiting the recordset
to the desired record will be *much* faster than opening the whole
recordset and using SEEK to navigate the recordset pointer to that
single record.
That was my whole point.
The speed that SEEK provides is only needed when you need to move
the the recordset pointer many times in a large recordset, and by
"many times" I mean thousands of pointer moves.
Furthermore, it only really works on a single index search (which
can include multiple fields), while .FindFirst can work with
multiple criteria on fields not in the same index.
SEEK is something you should use when the easy methods bog down, but
I see way too many people advocating starting out with SEEK. That's
why so many people run into problems when they split their
databases, because they've used the most efficient method when the
efficiency provided is not really needed.
This is referred to in programming terms as premature optimization
(or unnecessary optimization, depending on the situation) -- using
SEEK everywhere is optimizing for conditions that mostly don't
exist, and putting limits on what you can do that wouldn't be
necessary with the easier and more flexible methods (e.g., WHERE
clause or .FindFirst).
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
"Lyle Fairfield" <ly***********@aim.com> wrote (my evil twin Kyle adds, . . .
Strange, I thought I remembered that Kyle was the Good Twin.
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1... And finding a *single* record in a table is the absolute worst
scenorio you could describe for using SEEK. Limiting the recordset
to the desired record will be *much* faster than opening the whole
recordset and using SEEK to navigate the recordset pointer to that
single record.
That was my whole point.
The seek method does not pull down the full table prior to processing. It
utilizes the indexes to locate the record. It is as efficient, if not more
so, than any other method of finding a specific record. Microsoft clearly
states it is more efficient than any of the find methods
(http://support.microsoft.com/default...b;en-us;108149)
The speed that SEEK provides is only needed when you need to move
the the recordset pointer many times in a large recordset, and by
"many times" I mean thousands of pointer moves.
Furthermore, it only really works on a single index search (which
can include multiple fields), while .FindFirst can work with
multiple criteria on fields not in the same index.
Yes, the Find methods can use multiple indexes to find a specific record
(seek can not), assuming that they are structure correctly to take advantage
of the "rushmore" technology.
SEEK is something you should use when the easy methods bog down, but
I see way too many people advocating starting out with SEEK. That's
why so many people run into problems when they split their
databases, because they've used the most efficient method when the
efficiency provided is not really needed.
This is referred to in programming terms as premature optimization
(or unnecessary optimization, depending on the situation) -- using
SEEK everywhere is optimizing for conditions that mostly don't
exist, and putting limits on what you can do that wouldn't be
necessary with the easier and more flexible methods (e.g., WHERE
clause or .FindFirst).
One thing that I know, is when I the seek method in code, it MUST utilize an
index. Find methods and SQL select statements will utilize indexes assuming
that they are available, and the criteria is structured in a way that allows
for there use.
The above may be of no use to you, especially if you a one man shop, but can
be of use to larger companies.
Steven
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
"Steve" <st***@nospam.net> wrote in news:02****************@fe12.lga: The seek method does not pull down the full table prior to processing.
It utilizes the indexes to locate the record. It is as efficient, if
not more so, than any other method of finding a specific record.
Microsoft clearly states it is more efficient than any of the find
methods
(http://support.microsoft.com/default...b;en-us;108149)
Fascinating! I think the Seek we are talking about is:
Sub Seek(Comparison As String, Key1, [Key2], [Key3], [Key4], [Key5],
[Key6], [Key7], [Key8], [Key9], [Key10], [Key11], [Key12], [Key13])
Member of DAO.Recordset
So I'm guessing we need to have the recordset initialized and open before
we can apply the Seek. And then the Seek locates records which haven't been
"pulled down"?
So let me get this straight. It's a method of a recordset but it finds
records which are not in the recordset?
Fabulous!
Can you give us an example from the Northwind db?
Say maybe load the top 50 records of some table into the recordset, set the
index and do a seek (by the index key) for the sixtieth record?
And it will come up with that?
Incredible!
--
Lyle Fairfield
Try it and see. A table type recordset takes the same time to
open as a forward only recordset. Not like when you open a
snapshot. So let me get this straight. It's a method of a recordset but
it finds records which are not in the recordset?
:~)
(david)
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:Xn*********************************@216.221.8 1.119... "Steve" <st***@nospam.net> wrote in news:02****************@fe12.lga:
The seek method does not pull down the full table prior to processing.
It utilizes the indexes to locate the record. It is as efficient, if
not more so, than any other method of finding a specific record.
Microsoft clearly states it is more efficient than any of the find
methods
(http://support.microsoft.com/default...b;en-us;108149)
Fascinating! I think the Seek we are talking about is:
Sub Seek(Comparison As String, Key1, [Key2], [Key3], [Key4], [Key5],
[Key6], [Key7], [Key8], [Key9], [Key10], [Key11], [Key12], [Key13])
Member of DAO.Recordset
So I'm guessing we need to have the recordset initialized and open before
we can apply the Seek. And then the Seek locates records which haven't
been
"pulled down"?
So let me get this straight. It's a method of a recordset but it finds
records which are not in the recordset?
Fabulous!
Can you give us an example from the Northwind db?
Say maybe load the top 50 records of some table into the recordset, set
the
index and do a seek (by the index key) for the sixtieth record?
And it will come up with that?
Incredible!
--
Lyle Fairfield
"Steve" <st***@nospam.net> wrote in
news:02****************@fe12.lga:
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...
And finding a *single* record in a table is the absolute worst
scenorio you could describe for using SEEK. Limiting the
recordset to the desired record will be *much* faster than
opening the whole recordset and using SEEK to navigate the
recordset pointer to that single record.
That was my whole point.
The seek method does not pull down the full table prior to
processing. It utilizes the indexes to locate the record. . . .
Well, yes, of course, but it still has to move a recordset pointer
through an entire recordset, which is completely inefficient when
you're looking for a single record.
. . . It is as efficient, if not more
so, than any other method of finding a specific record. Microsoft
clearly states it is more efficient than any of the find methods
(http://support.microsoft.com/default...b;en-us;108149)
Microsoft's KB article is wrong.
The speed that SEEK provides is only needed when you need to move
the the recordset pointer many times in a large recordset, and by
"many times" I mean thousands of pointer moves.
Furthermore, it only really works on a single index search (which
can include multiple fields), while .FindFirst can work with
multiple criteria on fields not in the same index.
Yes, the Find methods can use multiple indexes to find a specific
record (seek can not), assuming that they are structure correctly
to take advantage of the "rushmore" technology.
Are you talking ADO or DAO? ADO is irrelevant to a discussion of
Jet, unless you're a nut case who swallowed the ADO Kool Aid, so
FIND is not relevant.
SEEK is something you should use when the easy methods bog down,
but I see way too many people advocating starting out with SEEK.
That's why so many people run into problems when they split their
databases, because they've used the most efficient method when
the efficiency provided is not really needed.
This is referred to in programming terms as premature
optimization (or unnecessary optimization, depending on the
situation) -- using SEEK everywhere is optimizing for conditions
that mostly don't exist, and putting limits on what you can do
that wouldn't be necessary with the easier and more flexible
methods (e.g., WHERE clause or .FindFirst).
One thing that I know, is when I the seek method in code, it MUST
utilize an index. Find methods and SQL select statements will
utilize indexes assuming that they are available, and the criteria
is structured in a way that allows for there use.
Yes, and the advantage of the non-SEEK methods is that you don't
need to know anything about the indexes to use them. That is a big
disadvantage of SEEK, in my opinion -- you have to write extra lines
of code and know your index names in order to use it. The other
methods do that work for you.
The above may be of no use to you, especially if you a one man
shop, but can be of use to larger companies.
How many times do I have to repeat that I have NEVER said that SEEK
is useless, just that it is only the best choice in a limited number
of circumstances. It should be the second choice when the other
methods don't work well (that's what is meant by avoiding premature
optimization).
You seem to me to be arguing exactly the opposite, that SEEK should
always be the first choice.
That looks like completely insane advice to me.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
"Lyle Fairfield" <ly***********@aim.com> wrote in
news:11**********************@u72g2000cwu.googlegr oups.com: Yes seek is fast.
So is a BMW M series.
But more often than not a Toyota Corlloa will do the trick without
the overhead of the BMW.
I have used seek extensively. But the only place that it pays its
way is when I want to do multiple (as in thousands or more) finds
in the same recordset. As seek seems not to be supported in MS-SQL
I have abandoned it pretty much all together.
This is what I've said repeatedly, but Steve refuses to agree with
it.
Most of MS's help and kb articles are just crap ...
Not most of them -- most of them are quite good. But they can't be
taken as completely authoritative and 100% reliable -- they do have
errors and can be misleading. It's important to consider when the
articles were written (which is not always clear, but you can bet
that an article that says it applies to Access 2, 95 and 97 was not
originally written in the current decade) and who the intended
audience was.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
I have disagreed with you when your statements about the seek method are
incorrect
When you say FindFirst is more efficient method than Seek, it is contrary to
the documentation, and our tests. I don't recommend Seek for everything, and
most of the time I locate records using SQL Select queries. But seek is
fast, as fast as any other method to find a record. Seek is a must in a few
situations, but these are situations that many may face - e.g. the
calculating weighted average is required in many inventory related systems
(for widgets to complex securities).
I disagree with you when you say
"Yes, and the advantage of the non-SEEK methods is that you don't
need to know anything about the indexes to use them. That is a big
disadvantage of SEEK, in my opinion -- you have to write extra lines
of code and know your index names in order to use it. The other
methods do that work for you."
The fact that people use Find methods without knowing anything about the
indexes is a huge problem - leading to many inefficient systems drastically
slowing down when placed on a network. To implement efficient Finds (or
select queries), you MUST know about the indexes in place. At least with a
Seek, I know that there are indexes in place - and that database engine will
optimize the search. I don't know that when I see a FindFirst or any other
Find method in code. I need to examine the criteria and available indexes
on the underlying tables to determine if the indexes will be used. The
fact is that I rarely use Find methods since I prefer SQL; but that is a
personal preference.
Oh, and I am not talking about ADO and, I have not, how you put it
"swallowed the ADO Kool Aid"
Steven
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1... "Lyle Fairfield" <ly***********@aim.com> wrote in
news:11**********************@u72g2000cwu.googlegr oups.com:
Yes seek is fast.
So is a BMW M series.
But more often than not a Toyota Corlloa will do the trick without
the overhead of the BMW.
I have used seek extensively. But the only place that it pays its
way is when I want to do multiple (as in thousands or more) finds
in the same recordset. As seek seems not to be supported in MS-SQL
I have abandoned it pretty much all together.
This is what I've said repeatedly, but Steve refuses to agree with
it.
Most of MS's help and kb articles are just crap ...
Not most of them -- most of them are quite good. But they can't be
taken as completely authoritative and 100% reliable -- they do have
errors and can be misleading. It's important to consider when the
articles were written (which is not always clear, but you can bet
that an article that says it applies to Access 2, 95 and 97 was not
originally written in the current decade) and who the intended
audience was.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
"Steve" <st***@nospam.net> wrote in news:Y7***********@fe12.lga: I have disagreed with you when your statements about the seek
method are incorrect
When you say FindFirst is more efficient method than Seek, . . .
I didn't say that.
. . . it is contrary to
the documentation, and our tests. I don't recommend Seek for
everything, and most of the time I locate records using SQL Select
queries. But seek is fast, as fast as any other method to find a
record. Seek is a must in a few situations, but these are
situations that many may face - e.g. the calculating weighted
average is required in many inventory related systems (for widgets
to complex securities).
But if you're looking for a single record, the overhead it takes to
open the recordset is far greater than the time it takes to move the
recordset pointer, whether via SEEK or FindFirst. So, any greater
efficiency for SEEK is lost in the much more resource-intensive
operation of opening the recordsource itself.
But if you're doing 1000s of repositioning operations, the
efficiency of SEEK becomes very helpful.
I disagree with you when you say
"Yes, and the advantage of the non-SEEK methods is that you don't
need to know anything about the indexes to use them. That is a big
disadvantage of SEEK, in my opinion -- you have to write extra
lines of code and know your index names in order to use it. The
other methods do that work for you."
The fact that people use Find methods without knowing anything
about the indexes is a huge problem - leading to many inefficient
systems drastically slowing down when placed on a network. . . .
I know what my indexes are, but I don't care about their names. When
I use .FindFirst, it's on an indexed field in 99.99% of the
situations where I'd use it (indeed, I can't think of a case where
I've ever done a FindFirst on a non-indexed field -- makes no
sense). But I don't have to write code to choose which index to use
-- DAO takes care of that for me.
. . . To implement efficient Finds (or
select queries), you MUST know about the indexes in place. . . .
You need to know what fields are indexed, but you don't have to know
the names of the indexes and you don't have to write the lines of
code it takes to choose the index.
. . . At least with a
Seek, I know that there are indexes in place - and that database
engine will optimize the search. . . .
I just don't see a difference here. Yes, the ease of FindFirst could
mean that someone who is inexperienced would use it on a non-indexed
field, but, well, who cares? They couldn't use SEEK on that field
anyway, so if it's something they need to do, it's *better* that
there is a method that can find the data regardless of the indexes
involved.
. . . I don't know that when I see a FindFirst or any other
Find method in code. I need to examine the criteria and available
indexes on the underlying tables to determine if the indexes will
be used.
Why do you care? I certainly don't.
. . . The
fact is that I rarely use Find methods since I prefer SQL; but
that is a personal preference.
Well, of course. If you can filter the recordset with a WHERE
clause, there's absolutely no reason to navigate around the
recordset, either with FindFirst or SEEK. That's not really relevant
to a comparison of FindFirst and SEEK, since in cases where the
WHERE clause is appropriate, you wouldn't be using SEEK either
(unless you're not doing it right in the first place).
Oh, and I am not talking about ADO and, I have not, how you put it
"swallowed the ADO Kool Aid"
There is no Find method in DAO, whereas there is in ADO. That's what
threw me off. I realize now that what your referring to are
FindFirst and FindNext.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...
<snipped> But if you're looking for a single record, the overhead it takes to
open the recordset is far greater than the time it takes to move the
recordset pointer, whether via SEEK or FindFirst. So, any greater
efficiency for SEEK is lost in the much more resource-intensive
operation of opening the recordsource itself.
You lost me. Both Seek and FindFirst requires a recordset.
?
Steven
Steve wrote: "David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...
<snipped>
But if you're looking for a single record, the overhead it takes to
open the recordset is far greater than the time it takes to move the
recordset pointer, whether via SEEK or FindFirst. So, any greater
efficiency for SEEK is lost in the much more resource-intensive
operation of opening the recordsource itself.
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
Using a tree index, it has to move 2 or 3 nodes to
find the record.
Which is what a select query does to find a record.
Because Seek exposes the native method of the database engine.
(david)
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1... "Steve" <st***@nospam.net> wrote in
news:02****************@fe12.lga:
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...
And finding a *single* record in a table is the absolute worst
scenorio you could describe for using SEEK. Limiting the
recordset to the desired record will be *much* faster than
opening the whole recordset and using SEEK to navigate the
recordset pointer to that single record.
That was my whole point.
The seek method does not pull down the full table prior to
processing. It utilizes the indexes to locate the record. . . .
Well, yes, of course, but it still has to move a recordset pointer
through an entire recordset, which is completely inefficient when
you're looking for a single record.
. . . It is as efficient, if not more
so, than any other method of finding a specific record. Microsoft
clearly states it is more efficient than any of the find methods
(http://support.microsoft.com/default...b;en-us;108149)
Microsoft's KB article is wrong.
The speed that SEEK provides is only needed when you need to move
the the recordset pointer many times in a large recordset, and by
"many times" I mean thousands of pointer moves.
Furthermore, it only really works on a single index search (which
can include multiple fields), while .FindFirst can work with
multiple criteria on fields not in the same index.
Yes, the Find methods can use multiple indexes to find a specific
record (seek can not), assuming that they are structure correctly
to take advantage of the "rushmore" technology.
Are you talking ADO or DAO? ADO is irrelevant to a discussion of
Jet, unless you're a nut case who swallowed the ADO Kool Aid, so
FIND is not relevant.
SEEK is something you should use when the easy methods bog down,
but I see way too many people advocating starting out with SEEK.
That's why so many people run into problems when they split their
databases, because they've used the most efficient method when
the efficiency provided is not really needed.
This is referred to in programming terms as premature
optimization (or unnecessary optimization, depending on the
situation) -- using SEEK everywhere is optimizing for conditions
that mostly don't exist, and putting limits on what you can do
that wouldn't be necessary with the easier and more flexible
methods (e.g., WHERE clause or .FindFirst).
One thing that I know, is when I the seek method in code, it MUST
utilize an index. Find methods and SQL select statements will
utilize indexes assuming that they are available, and the criteria
is structured in a way that allows for there use.
Yes, and the advantage of the non-SEEK methods is that you don't
need to know anything about the indexes to use them. That is a big
disadvantage of SEEK, in my opinion -- you have to write extra lines
of code and know your index names in order to use it. The other
methods do that work for you.
The above may be of no use to you, especially if you a one man
shop, but can be of use to larger companies.
How many times do I have to repeat that I have NEVER said that SEEK
is useless, just that it is only the best choice in a limited number
of circumstances. It should be the second choice when the other
methods don't work well (that's what is meant by avoiding premature
optimization).
You seem to me to be arguing exactly the opposite, that SEEK should
always be the first choice.
That looks like completely insane advice to me.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
"Steve" <st***@nospam.net> wrote in news:sy***************@fe10.lga: "David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...
<snipped>
But if you're looking for a single record, the overhead it takes
to open the recordset is far greater than the time it takes to
move the recordset pointer, whether via SEEK or FindFirst. So,
any greater efficiency for SEEK is lost in the much more
resource-intensive operation of opening the recordsource itself.
You lost me. Both Seek and FindFirst requires a recordset.
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).
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
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
Bri <no*@here.com> wrote in 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.
. . . 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. I
think there's something not right about your tests, but I'm not
interested in running the tests myself. I can't think of a situation
where I'd use FindFirst within a recordset where SEEK would be an
option (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.
I repeat that I see no purpose in using SEEK to find a single
record.
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).
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
David W. Fenton wrote: Bri <no*@here.com> wrote in 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.
. . . 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 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
Regarding FindFirst, use of indexes and performance relative to Seek, a very
old tech paper by Microsoft stated:
"If you select a table-type recordset with an index, you can use the Seek
method to position the cursor against any criteria that are stored in that
index. This will always be the fastest possible way to find a particular
piece of data. Even though Find will use indexes where possible, because it
is based on a dynaset or snapshot, the overhead of that mechanism will
always be somewhat greater than the equivalent Seek."
See "43 Ways to Speed Up DAO Code" http://msdn.microsoft.com/archive/de...eDAOFaster.asp
As I have stated, I use Seek when it is the most appropriate. But most of
the time, I use SQL Select statements for returning information; rarely do I
need to return information from only one table. I rarely use FindFirst (or
related methods).
Finally, I have found that small performance differences seem to get
magnified as the database grows, is moved to a server, and/or more users
start to access it. So, if I have an option to either use FindFirst or
Seek, and I know that pushing the system to SQL Server is not in the cards,
I would go with the better performing option. Microsoft states Seek is
faster than Find methods (see the Access 97 help FindFirst .... Methods),
your tests show that Seek is faster, and my tests have shown that Seek is
faster.
But, to each their own.
Your tests have confirmed the performance difference.
Thanks,
Steven
"Bri" <no*@here.com> wrote in message news:kXcZf.2467$nf7.1564@pd7tw1no...
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
Bri <no*@here.com> wrote in news:3ceZf.2940$_u1.1473@pd7tw2no: David W. Fenton wrote: Bri <no*@here.com> wrote in 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/
On Thu, 06 Apr 2006 18:17:52 GMT, Bri <no*@here.com> wrote:
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.
There is something wrong with your data as I get nothing like the speed drop you
experience with FindFirst.
However in all versions of Access the order of performance (using your test) is
Seek then FindFirst then Query.
Access97
==========
Seek (1000) - 0.171875
Query (1000) - 0.53125
Open rs - 0
FindFirst - 0.21875
FindFirst finish - 0.21875
Access 2000
============
Seek (1000) - 0.125
Query (1000) - 0.734375
Open rs - 0
FindFirst - 0.203125
FindFirst finish - 0.21875
Access 2002
============
Seek (1000) - 0.125
Query (1000) - 0.578125
Open rs - 0
FindFirst - 0.203125
FindFirst finish - 0.203125
Access 2003
============
Seek (1000) - 0.125
Query (1000) - 0.546875
Open rs - 0.015625
FindFirst - 0.25
FindFirst finish - 0.25
Wayne Gillespie
Gosford NSW Australia
The differences between 97, 2000,2002,2003 just show the
normal variation due to caching and noise.
(david)
"Wayne Gillespie" <be*****@NOhotmailSPAM.com.au> wrote in message
news:p0********************************@4ax.com... On Thu, 06 Apr 2006 18:17:52 GMT, Bri <no*@here.com> wrote:
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.
There is something wrong with your data as I get nothing like the speed
drop you
experience with FindFirst.
However in all versions of Access the order of performance (using your
test) is
Seek then FindFirst then Query.
Access97
==========
Seek (1000) - 0.171875
Query (1000) - 0.53125
Open rs - 0
FindFirst - 0.21875
FindFirst finish - 0.21875
Access 2000
============
Seek (1000) - 0.125
Query (1000) - 0.734375
Open rs - 0
FindFirst - 0.203125
FindFirst finish - 0.21875
Access 2002
============
Seek (1000) - 0.125
Query (1000) - 0.578125
Open rs - 0
FindFirst - 0.203125
FindFirst finish - 0.203125
Access 2003
============
Seek (1000) - 0.125
Query (1000) - 0.546875
Open rs - 0.015625
FindFirst - 0.25
FindFirst finish - 0.25
Wayne Gillespie
Gosford NSW Australia
Just a note: you get far better results on FindFirst if your
data fits into the Jet Cache. Finding to the end of the
recordset is always slow (they say it uses the index some
how, but it is hard to believe), but finding when it means
flushing the index each time is very slow.
(david)
"Bri" <no*@here.com> wrote in message news:kXcZf.2467$nf7.1564@pd7tw1no...
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
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote Well, I don't think your test is very realistic -- 200K records is
an awful lot to open at once.
Maybe I am missing something here: I thought you had said, use a Query that
lets the DB engine to the selection of the record or two you need to see,
and retreive only what you need. But he, on the other had, is bound and
determined to open the entire recordset and then locate one record of
interest be it with Seek or with.
Larry Linson
Microsoft Access MVP
"Larry Linson" <bo*****@localhost.not> wrote in
news:IhoZf.15911$e11.5525@trnddc02:
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote
Well, I don't think your test is very realistic -- 200K records is
an awful lot to open at once.
Maybe I am missing something here: I thought you had said, use a Query
that lets the DB engine to the selection of the record or two you need
to see, and retreive only what you need. But he, on the other had, is
bound and determined to open the entire recordset and then locate one
record of interest be it with Seek or with.
I agree with David AND Larry!? I gotta get a grip ....
--
Lyle Fairfield
The 'OpenRecordSet' method is used in both cases, but opening
a SnapShot retrieves all data: Opening a TableType retrieves
only meta-data.
A table type recordset is not an entire recordset.
(david)
"Larry Linson" <bo*****@localhost.not> wrote in message
news:IhoZf.15911$e11.5525@trnddc02...
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote
Well, I don't think your test is very realistic -- 200K records is
an awful lot to open at once.
Maybe I am missing something here: I thought you had said, use a Query
that lets the DB engine to the selection of the record or two you need to
see, and retreive only what you need. But he, on the other had, is bound
and determined to open the entire recordset and then locate one record of
interest be it with Seek or with.
Larry Linson
Microsoft Access MVP
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in
news:44***********************@lon-reader.news.telstra.net:
The 'OpenRecordSet' method is used in both cases, but opening
a SnapShot retrieves all data: Opening a TableType retrieves
only meta-data.
A table type recordset is not an entire recordset.
Would you, please, define "metadata" for this particular situation.
So that I can understand better perhaps you could use the Employees or
other Table of the Northwind Database. I believe (although I've messed
with mine and can't be sure) that the Employees has nine records. Suppose
we add 2047991 records (imagine it's some trivial civil service
department) and open a table type recordset.
What exactly is loaded? I would assume it's some schema including a
description of columns, their size and type. Since the table-type
recordset is recordcount aware does JET do an sql type count behind the
scenes or does it move to the end of the recordset (and back as record
pointer is at record one) as we must do with other types of recordsets?
If it loads only descriptive metadata about columns then one assumes
opening a 204800 record recordset is as fast as opening a 2048 record
recordset? Is this the case?
What happens when we set the index? What additional data is loaded then?
Is the index loaded into a separate memory space?
If someone were seriously interested in this problem (I am not), he or
she could create a 2K table, post it somewhere on the net for download ,
and then we could all, in our spare time experiment with various ways to
"find" the, say, 10th last record.
We could then compare coding time (time for us to write the code) and
finding time. When we published our methods and results others could try
them and eventually we would come to some consensus.
Until someone does this and we have an open and calm conversation about
it I will stick my by previous observations:
I have used Seek extensively in the past. I have championed Seek in the
past. Except in very unusual circumstances Seek is irrelevant today.
Microsoft kb articles have been cited here in support of Seek. If MS
believes Seek is so great, why isn't it available in MS-SQL Server?
--
Lyle Fairfield
"Larry Linson" <bo*****@localhost.not> wrote in
news:IhoZf.15911$e11.5525@trnddc02: "David W. Fenton" <XX*******@dfenton.com.invalid> wrote
Well, I don't think your test is very realistic -- 200K records
is an awful lot to open at once.
Maybe I am missing something here: I thought you had said, use a
Query that lets the DB engine to the selection of the record or
two you need to see, and retreive only what you need. But he, on
the other had, is bound and determined to open the entire
recordset and then locate one record of interest be it with Seek
or with.
Yes, that's what he's doing. The scenario is comparing two very
stupid things, and lo and behold, SEEK wins in the test of this very
stupid task.
My guess is that with a more realistic scenario, the difference
between the two will be sufficiently smaller to have no end-user
noticable impact. I have never used SEEK myself in the few times
I've jumped around recordsets and there certainly was not 3-second
lag involved. This is even the case with one of my apps where I use
FindFirst to pull in data from a recordset of grouped totals.
Initializing the recordset takes a long time, but the FindFirst
operation is not slow at all. And that's a summary on a table with
about 500K records (the summary has c. 200K records, I'm guessing).
I can't log onto that terminal server right now, but I'll check when
it comes online again.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
David W. Fenton wrote: Bri <no*@here.com> wrote in news:3ceZf.2940$_u1.1473@pd7tw2no:
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.
I picked a large table to test the extremes. This is ment to be a test
to show up differences in relative performance, not necessarily to mimic
something that I would do in an app.
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.
Opening the Table from the Database Window and navigating to the last
record take less time than the FindFirst. I don't know what is happening
either, but something is causing it to crawl.
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.
It is local, but in a PGPdisk volumn (mounted as N: to be my local
equivelent of the client's network drive). I expect that there is some
overhead due to the encrypt/decrypt process, but it is minimal compared
to an actual network. I've never seen any noticable difference between
something in the PGPdisk and something not in it. The Table is
realistic. It is an actual table with actual data that I actually search
and query against in the app. My test code is running in the BE (so I
can use Seek directly), but the app itself is a split FE.
I'd be interested to hear what happens with a 20K-record table, for
instance.
The tests I did used the tables PK which is a Text field
I just tried a few variations:
- Used a unique Long Integer field;
Open rs - 0
FindFirst - 2.972656
FindFirst finish - 2.984375
Query (1000) - 2.011719
Seek (1000) - 0.421875
All steps ran faster, but Seek improved the most, to a 4x ratio.
- Used a smaller table (32k records) and a unique Long Integer field;
Open rs - 0
FindFirst - 0.4609375
FindFirst finish - 0.4609375
Query (1000) - 1.820313
Seek (1000) - 0.390625
Now Seek is 4x+ faster. FindFirst is faster than before, but remember
that this time is for ONE findfirst vs 1000 loops of the others.
- Used a even smaller table (2.7k records) and a unique Long Integer
field;
Open rs - 0
FindFirst - 0.0390625
FindFirst finish - 0.05078125
Query (1000) - 1.722656
Seek (1000) - 0.5429688
It is now looking like Seek and the Query are only marginally affected
by the size of the table while FindFirst improves significantly (still
not as fast by a long shot). This seems to support that Seek and the
Query are using the index but FindFirst is not.
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 explain it either, only demonstrate it.
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.
Agreed.
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.
As mentioned above, the first tests were on a text PK, the test with the
Long Unique index, still shows the same order of speed. Same with the
smaller tables.
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.
Could be, but it seems too slow for even that.
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.
OK, using the last table above (2.7k records) using a field without an
index (dropped Seek from test as it requires an index):
Open rs - 0
FindFirst - 0.0390625
FindFirst finish - 0.05078125
Query (1000) - 3.632813
FindFirst times are IDENTICAL to the test with the unique index. This
supports my theory that FindFirst is not using the index. The query is
about half as fast. The table scan for the first loop may get cached so
that the subsequent loops are faster. I was expecting the query to have
slowed down a lot more than that.
None of this changes how I would code in real life as I usually use
FindFirst to do a single pass of a RecordsetClone where neither Seek or
a Query would be of use (the RecordsetClone is already based on a query,
not on a whole table so is usually <100 records).
--
Bri
Larry Linson wrote: "David W. Fenton" <XX*******@dfenton.com.invalid> wrote
> Well, I don't think your test is very realistic -- 200K records is
> an awful lot to open at once.
Maybe I am missing something here: I thought you had said, use a Query that
lets the DB engine to the selection of the record or two you need to see,
and retreive only what you need. But he, on the other had, is bound and
determined to open the entire recordset and then locate one record of
interest be it with Seek or with.
This was not ment to be a demonstration of coding practices. This was
ment to be a test of the relative speeds of the three methods of
retrieving a field value from a table from a specific record. In the
test, the Query with a Where clause was one of the tests along with Seek
and FindFirst.
--
Bri
David W. Fenton wrote: "Larry Linson" <bo*****@localhost.not> wrote in
news:IhoZf.15911$e11.5525@trnddc02:
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote
Well, I don't think your test is very realistic -- 200K records
is an awful lot to open at once.
Maybe I am missing something here: I thought you had said, use a
Query that lets the DB engine to the selection of the record or
two you need to see, and retreive only what you need. But he, on
the other had, is bound and determined to open the entire
recordset and then locate one record of interest be it with Seek
or with.
Yes, that's what he's doing. The scenario is comparing two very
stupid things, and lo and behold, SEEK wins in the test of this very
stupid task.
My guess is that with a more realistic scenario, the difference
between the two will be sufficiently smaller to have no end-user
noticable impact. I have never used SEEK myself in the few times
I've jumped around recordsets and there certainly was not 3-second
lag involved. This is even the case with one of my apps where I use
FindFirst to pull in data from a recordset of grouped totals.
Initializing the recordset takes a long time, but the FindFirst
operation is not slow at all. And that's a summary on a table with
about 500K records (the summary has c. 200K records, I'm guessing).
I can't log onto that terminal server right now, but I'll check when
it comes online again.
Yes, the test was to compare the speed of the three methods, not to be a
realistic scenario. It was never presented as such.
--
Bri
Steve wrote: Regarding FindFirst, use of indexes and performance relative to Seek, a very
old tech paper by Microsoft stated:
"If you select a table-type recordset with an index, you can use the Seek
method to position the cursor against any criteria that are stored in that
index. This will always be the fastest possible way to find a particular
piece of data. Even though Find will use indexes where possible, because it
is based on a dynaset or snapshot, the overhead of that mechanism will
always be somewhat greater than the equivalent Seek."
See "43 Ways to Speed Up DAO Code"
http://msdn.microsoft.com/archive/de...eDAOFaster.asp
As I have stated, I use Seek when it is the most appropriate. But most of
the time, I use SQL Select statements for returning information; rarely do I
need to return information from only one table. I rarely use FindFirst (or
related methods).
Finally, I have found that small performance differences seem to get
magnified as the database grows, is moved to a server, and/or more users
start to access it. So, if I have an option to either use FindFirst or
Seek, and I know that pushing the system to SQL Server is not in the cards,
I would go with the better performing option. Microsoft states Seek is
faster than Find methods (see the Access 97 help FindFirst .... Methods),
your tests show that Seek is faster, and my tests have shown that Seek is
faster.
But, to each their own.
Your tests have confirmed the performance difference.
I agree that Seek, while being blazingly fast, is not often the
appropriate method to use. It is so restrictive in how/where it can be
used that it is rarely practical to even consider it.
If you refer to my earlier post to David, you will see that I tested a
number of alternate situations. I saw relatively little difference in
the timings of Seek and the Query in the different scenarios, but the
FindFirst varied a lot. These results led me to conclude that FindFirst
does not use the index at all.
--
Bri
Wayne Gillespie wrote:
There is something wrong with your data as I get nothing like the speed drop you
experience with FindFirst.
Keep in mind that the Seek and the Query are in a loop running 1000
times while the FindFirst is only running ONCE.
However in all versions of Access the order of performance (using your test) is
Seek then FindFirst then Query.
Access97
==========
Seek (1000) - 0.171875
Query (1000) - 0.53125
Open rs - 0
FindFirst - 0.21875
FindFirst finish - 0.21875
Thank you for running the test in the different versions of Access. It
shows that the version is not making a significant difference to the test.
--
Bri
david epsom dot com dot au wrote: Just a note: you get far better results on FindFirst if your
data fits into the Jet Cache. Finding to the end of the
recordset is always slow (they say it uses the index some
how, but it is hard to believe), but finding when it means
flushing the index each time is very slow.
See the post to David I did earlier. I ran the test in a variety of
different scenarios (different table sizes, different field type for
index). The changes to Seek and the Query were minimal. The changes to
the timing of FindFirst seem to indicate that it does not use the Index
at all.
--
Bri
Bri <no*@here.com> wrote in news:_kyZf.6351$nf7.4592@pd7tw1no: David W. Fenton wrote: Bri <no*@here.com> wrote in news:3ceZf.2940$_u1.1473@pd7tw2no:
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.
I picked a large table to test the extremes. This is ment to be a
test to show up differences in relative performance, not
necessarily to mimic something that I would do in an app.
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.
Opening the Table from the Database Window and navigating to the
last record take less time than the FindFirst. I don't know what
is happening either, but something is causing it to crawl.
But the thing you're testing is bad practice to begin with, so it's
not a realistic test, so I don't see why you should choose realism
for one issue and complete unrealism for the basic test itself.
It may be that the large table flushes out a difference between SEEK
and FindFirst in the way index pages are accessed.
But, as someone else said, your results don't really match my
experience. I've never seen that kind of slow performance for a
FindFirst, and I do that operation occasionally on very large
tables.
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.
It is local, but in a PGPdisk volumn (mounted as N: to be my local
equivelent of the client's network drive). I expect that there is
some overhead due to the encrypt/decrypt process, but it is
minimal compared to an actual network. . . .
I don't know about that at all. I used compressed drives for years
with an old laptop and for database files it was a big slowdown
(though more for writes than reads).
Where's your %TEMP% space? Is it on an unecnrypted drive? If not, my
bet is that this might be the source of the problem.
. . . I've never seen any noticable difference between
something in the PGPdisk and something not in it. The Table is
realistic. It is an actual table with actual data that I actually
search and query against in the app. My test code is running in
the BE (so I can use Seek directly), but the app itself is a split
FE.
Well, I'm discarding your results as authoritative until they are
run on an unencrypted drive. The fact that your results differ
markedly with the only other person who has also run the test says
to me that there is something else causing the poor FindFirst
performance rather than the design of it.
I'd be interested to hear what happens with a 20K-record table,
for instance.
The tests I did used the tables PK which is a Text field
I just tried a few variations:
- Used a unique Long Integer field;
Open rs - 0
FindFirst - 2.972656
FindFirst finish - 2.984375
Query (1000) - 2.011719
Seek (1000) - 0.421875
All steps ran faster, but Seek improved the most, to a 4x ratio.
- Used a smaller table (32k records) and a unique Long Integer
field;
Open rs - 0
FindFirst - 0.4609375
FindFirst finish - 0.4609375
Query (1000) - 1.820313
Seek (1000) - 0.390625
Now Seek is 4x+ faster. FindFirst is faster than before, but
remember that this time is for ONE findfirst vs 1000 loops of the
others.
- Used a even smaller table (2.7k records) and a unique Long
Integer
field;
Open rs - 0
FindFirst - 0.0390625
FindFirst finish - 0.05078125
Query (1000) - 1.722656
Seek (1000) - 0.5429688
It is now looking like Seek and the Query are only marginally
affected by the size of the table while FindFirst improves
significantly (still not as fast by a long shot). This seems to
support that Seek and the Query are using the index but FindFirst
is not.
Or that FindFirst is retrieving and traversing the index pages in a
fashion different than the other methods.
But as long as you're running it on an encrypted drive, I think the
results are not reliable.
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 explain it either, only demonstrate it.
That you're having these results consistently, whereas those of us
who do similar things do *not* have such results suggests to me that
the problem is with your system, and the encrypted drive is the
likely culprit -- somehow, it's interfering with FindFirst's ability
to be efficient.
[]
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.
OK, using the last table above (2.7k records) using a field
without an index (dropped Seek from test as it requires an index):
Open rs - 0
FindFirst - 0.0390625
FindFirst finish - 0.05078125
Query (1000) - 3.632813
FindFirst times are IDENTICAL to the test with the unique index.
This supports my theory that FindFirst is not using the index. The
query is about half as fast. The table scan for the first loop may
get cached so that the subsequent loops are faster. I was
expecting the query to have slowed down a lot more than that.
Wow, that's really surprising to me.
None of this changes how I would code in real life as I usually
use FindFirst to do a single pass of a RecordsetClone where
neither Seek or a Query would be of use (the RecordsetClone is
already based on a query, not on a whole table so is usually <100
records).
Well, I'm going to have to do some timing tests on the app where I
use a FIND operations to navigate a GROUP BY recordset with totals.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
David W. Fenton wrote: But the thing you're testing is bad practice to begin with, so it's
not a realistic test, so I don't see why you should choose realism
for one issue and complete unrealism for the basic test itself.
Huh? The test is being done to verify what good practices should be. I'm
treating them the same, so how am I choosing realism in one case and not
in the other? I chose to have all three tests work against the entire
table so that that would be a constant. In any experiment it is best to
have as few variables as possible, preferably only one, the one you are
testing.
It may be that the large table flushes out a difference between SEEK
and FindFirst in the way index pages are accessed.
But the small table showed the same order of things. Yes, FindFirst
performed better as the tables got smaller, but Seek and the Query
didn't see the same degree of change.
But, as someone else said, your results don't really match my
experience. I've never seen that kind of slow performance for a
FindFirst, and I do that operation occasionally on very large
tables.
Well, if you are already following the good practices, then you would
only be using FindFirst on a smaller subset. If you are doing multiple
Finds, perhaps they can take advantage of caching so only the first one
takes the big time hit.
Where's your %TEMP% space? Is it on an unecnrypted drive? If not, my
bet is that this might be the source of the problem.
Its under my profile on my C: drive. The PGPdisk is just where the BE
resides to simulate the network environment of the client.
Well, I'm discarding your results as authoritative until they are
run on an unencrypted drive. The fact that your results differ
markedly with the only other person who has also run the test says
to me that there is something else causing the poor FindFirst
performance rather than the design of it.
OK, I moved the BE to my regular HDD and ran the test again. The results
were virtually identical.
Or that FindFirst is retrieving and traversing the index pages in a
fashion different than the other methods.
The final test showed no difference whether the field was indexed or not.
But as long as you're running it on an encrypted drive, I think the
results are not reliable.
See above.
That you're having these results consistently, whereas those of us
who do similar things do *not* have such results suggests to me that
the problem is with your system, and the encrypted drive is the
likely culprit -- somehow, it's interfering with FindFirst's ability
to be efficient.
Well, Wayne did the tests and came up with the same sort of results that
I got with the smaller table. He didn't seem to realize that he was
comparing a single FindFirst to 1000 Seeks and Queries.
So, to avoid this confusion I reran the tests with 10 loops for all
three methods on the smallest table using the unique long field index:
FindFirst (10) - 0.2500000
Query (10) - 0.0195313
Seek (10) - 0.0078125
So, I still find that FindFirst is 30x slower than Seek and 13x slower
than the Query. The Query continues to be 2.5x slower than Seek. With
this size of table, the FindFirst performance is still fast enough that
the user isn't going to notice the difference. The test confirms that it
is good practice to not do FindFirst against large recordsets if you can
use a Query to do the job.
FindFirst times are IDENTICAL to the test with the unique index.
This supports my theory that FindFirst is not using the index. The
query is about half as fast. The table scan for the first loop may
get cached so that the subsequent loops are faster. I was
expecting the query to have slowed down a lot more than that.
Wow, that's really surprising to me.
Me too.
Well, I'm going to have to do some timing tests on the app where I
use a FIND operations to navigate a GROUP BY recordset with totals.
All of my tests were against a single table (one of my constants, since
Seek can't be used against anything else). I would be interested in the
results of you test. I hypothosize that you will have a chunk of time to
open the recordset since it is a query that must run to completion
before it opens. The subsequent FindFirst should be similar to my
mid-sized table test (you did say 20k records in the results? That's
about 2/3 the size om my mid table at 32k records).
--
Bri
Bri <no*@here.com> wrote in news:m6VZf.9870$gO.808@pd7tw3no: So, to avoid this confusion I reran the tests with 10 loops for
all three methods on the smallest table using the unique long
field index: FindFirst (10) - 0.2500000
Query (10) - 0.0195313
Seek (10) - 0.0078125
Are you changing the searched-for value for each loop? Or just
searching for the same value 10 times? Or doing a .MoveFirst before
the find operation?
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Bri <no*@here.com> wrote in news:m6VZf.9870$gO.808@pd7tw3no: I would be interested in the
results of you test. I hypothosize that you will have a chunk of
time to open the recordset since it is a query that must run to
completion before it opens. The subsequent FindFirst should be
similar to my mid-sized table test (you did say 20k records in the
results? That's about 2/3 the size om my mid table at 32k
records).
No, I expect something like 200K records. I know that the initial
loading of the persistent recordset takes a while (c. 20 seconds),
but there is no noticeable lag once the form and the persistent
summary recordset have been initialized. Every OnCurrent event does
a FindFirst and FindNext's until no more records for that current
record are found. If there were a 3-second lag for that FindFirst
operation, I would have noticed it, unless it's only in the first
one (which would be obscured in the overhead of opening the summary
recordset).
Again, I'll have to run some benchmarks on it.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/ This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: WC Justice |
last post by:
I have a web site that uses an Access 2000 back end. Most of the recordsets
use the rsName.open "qryName"... method. So far, in converting to a SQL
Server back end, I have been changing to the...
|
by: Matt Hawley |
last post by:
I'm attempting to run a .NET windows application, but continuously get the following error when starting it up:
Application has generated an exception that could not be handled.
Process...
|
by: Sonu |
last post by:
Hello everyone and thanks in advance.
I have a multilingual application which has been built in MFC VC++ 6.0
(non-Unicode). It support English German Hungarian so far, which has been
fine. But...
|
by: Mindy |
last post by:
Hey,
I want to build a database which has an in front end application and
back end tables. I am new to access, and all my knowledge is from
Access for Dummies. Could any one give me a clue...
|
by: Jon Gabel |
last post by:
I have a WindowsForms application that communicates via TCP/IP or SNMP
(using IP*Works components) with various devices on my local network.
I would
like to duplicate the same functionality in a...
|
by: JLC |
last post by:
I am having some trouble displaying the following.
I want to display the startdatetime and enddatetime in a particular table using an offset. In that table is also a timezoneid that would...
|
by: kphip123 |
last post by:
Hi!
I recently converted an application from VS2003 to VS2005.
The application consists of a main 'unmanaged C++ executable' which
calls
a managed C++ .dll. The .dll basically launches a...
|
by: Glenn Spiller |
last post by:
I am trying to pass a JAVA url that needs variables from my application express app. Anybody know how to pass these variables. Even if there is a way to pass it using PL/SQL I could also use that.
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |
