|
Home > Archive > MS Access database support > April 2006 > Trouble with DAO "SEEK" in converting application to SQL Express back end.
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Trouble with DAO "SEEK" in converting application to SQL Express back end.
|
|
|
| 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 W. Fenton 2006-03-30, 8:29 pm |
| "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/
| |
| Larry Linson 2006-03-30, 8:29 pm |
| "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
| |
| david epsom dot com dot au 2006-03-30, 8:29 pm |
| 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!
>
>
| |
| Lyle Fairfield 2006-03-30, 8:29 pm |
| "david epsom dot com dot au" < david@epsomdotcomdot
au> wrote in
news:442c7b8d$0$7524
5$c30e37c6@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
| |
| David W. Fenton 2006-03-30, 8:29 pm |
| "Larry Linson" <bouncer@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" <XXXusenet@dfenton.com.invalid> wrote in message
news:Xns9796B701A615
Ef99a49ed1d0c49c5bbb
2@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:
>
>
> 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/
| |
| David W. Fenton 2006-04-01, 8:27 pm |
| "Steve" <steve@nospam.net> wrote in news:FkDXf.211$rY6.129@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/
| |
| Steve 2006-04-03, 11:32 am |
| 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" <XXXusenet@dfenton.com.invalid> wrote in message
news:Xns9798CD4A77F1
Ef99a49ed1d0c49c5bbb
2@127.0.0.1...
> "Steve" <steve@nospam.net> wrote in news:FkDXf.211$rY6.129@fe10.lga:
>
>
> 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/
| |
| Lyle Fairfield 2006-04-03, 8:28 pm |
| 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!")
| |
| David W. Fenton 2006-04-04, 3:30 am |
| "Steve" <steve@nospam.net> wrote in news:i4bYf.13$ZP5.6@fe08.lga:
> "David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in message
> news:Xns9798CD4A77F1
Ef99a49ed1d0c49c5bbb
2@127.0.0.1...
>
> 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/
| |
| Br@dley 2006-04-04, 3:30 am |
| 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" <XXXusenet@dfenton.com.invalid> wrote in message
news:Xns9796B701A615
Ef99a49ed1d0c49c5bbb
2@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:
>
>
> 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" <XXXusenet@dfenton.com.invalid> wrote in message
news:Xns979ADD1BFCB5
5f99a49ed1d0c49c5bbb
2@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/
| |
| David W. Fenton 2006-04-04, 11:34 am |
| "Steve" <steve@nospam.net> wrote in news:mcvYf.9$ZG1.8@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/
| |
| Larry Linson 2006-04-04, 8:30 pm |
| "Lyle Fairfield" <lylefairfield@aim.com> wrote
> (my evil twin Kyle adds, . . .
Strange, I thought I remembered that Kyle was the Good Twin.
| |
|
|
"David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in message
news:Xns979B6BE29964
Df99a49ed1d0c49c5bbb
2@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/defaul...kb;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/
| |
| Lyle Fairfield 2006-04-04, 8:30 pm |
| "Steve" <steve@nospam.net> wrote in news:02BYf.1160$U_6.728@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/defaul...kb;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
| |
| david epsom dot com dot au 2006-04-05, 3:30 am |
| 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" <lylefairfield@aim.com> wrote in message
news:Xns979BB2BA6370
Elylefairfieldaimcom
@216.221.81.119...
> "Steve" <steve@nospam.net> wrote in news:02BYf.1160$U_6.728@fe12.lga:
>
>
> 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
| |
| David W. Fenton 2006-04-05, 9:35 am |
| "Steve" <steve@nospam.net> wrote in
news:02BYf.1160$U_6.728@fe12.lga:
>
> "David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in message
> news:Xns979B6BE29964
Df99a49ed1d0c49c5bbb
2@127.0.0.1...
>
>
> 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/defaul...kb;en-us;108149)
Microsoft's KB article is wrong.
>
> 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.
>
> 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/
| |
| David W. Fenton 2006-04-05, 9:35 am |
| Organization: David Fenton Associates
Message-ID: < Xns979C647667F8Ef99a
49ed1d0c49c5bbb2@127
.0.0.1>
User-Agent: Xnews/2005.10.18 Mime-proxy/2.0.c.3 (Win32)
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Mime-Version: 1.0
Date: Wed, 05 Apr 2006 08:52:26 -0500
Lines: 31
NNTP-Posting-Host: 64.48.59.18
X-Trace: sv3- V48ISTVaG9QKHNC4XOWv
cMwC8iaa9/ 7StQECi25jOHec1iYenh
QkuMrJVqDJV/2Luk2LjoM7LOQ/ kuv!MxG4z8ZHbAWxsEL3
PeCEBYd4tR8AEZU8kh5h
fTDNPqSKFDbwu2R53J40
NxIEamOKYR3SPJyczogG
!iy/nt3mx1nW2EpuR
X-Complaints-To: abuse@bway.net
X-DMCA-Complaints-To: abuse@bway.net
X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers
X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly
X-Postfilter: 1.3.32
Xref: number1.nntp.dca.giganews.com comp.databases.ms-access:856044
"Lyle Fairfield" <lylefairfield@aim.com> wrote in
news:1144097770.254189.162970@u72g2000cwu.googlegroups.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 2006-04-05, 11:36 am |
| 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" <XXXusenet@dfenton.com.invalid> wrote in message
news:Xns979C647667F8
Ef99a49ed1d0c49c5bbb
2@127.0.0.1...
> "Lyle Fairfield" <lylefairfield@aim.com> wrote in
> news:1144097770.254189.162970@u72g2000cwu.googlegroups.com:
>
>
> This is what I've said repeatedly, but Steve refuses to agree with
> it.
>
>
> 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/
| |
| David W. Fenton 2006-04-05, 1:44 pm |
| "Steve" <steve@nospam.net> wrote in news:Y7RYf.2$0n6.0@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" <XXXusenet@dfenton.com.invalid> wrote in message
news:Xns979C8FE391DB
2f99a49ed1d0c49c5bbb
2@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
| |
| david epsom dot com dot au 2006-04-05, 8:28 pm |
| 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" <XXXusenet@dfenton.com.invalid> wrote in message
news:Xns979C640E99CB
7f99a49ed1d0c49c5bbb
2@127.0.0.1...
> "Steve" <steve@nospam.net> wrote in
> news:02BYf.1160$U_6.728@fe12.lga:
>
>
> 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.
>
>
> Microsoft's KB article is wrong.
>
>
> 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.
>
>
> 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.
>
>
> 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/
| |
| David W. Fenton 2006-04-06, 9:35 am |
| "Steve" <steve@nospam.net> wrote in news:syWYf.158$w_6.146@fe10.lga:
> "David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in message
> news:Xns979C8FE391DB
2f99a49ed1d0c49c5bbb
2@127.0.0.1...
>
><snipped>
>
>
> You lost me. Both Seek and FindFirst requires a recordset.
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 2006-04-06, 1:34 pm |
| Bri <not@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/
| |
|
| 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/d...keDAOFaster.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" <not@here.com> wrote in message news:kXcZf.2467$nf7.1564@pd7tw1no...
>
>
> David W. Fenton wrote:
>
> 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 2006-04-06, 8:29 pm |
| Bri <not@here.com> wrote in news:3ceZf.2940$_u1.1473@pd7tw2no:
> David W. Fenton wrote:
>
> 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.
>
> 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 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 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.
>
> Agreed.
>
>
> 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.
>
> 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/
| |
| Wayne Gillespie 2006-04-06, 8:29 pm |
| On Thu, 06 Apr 2006 18:17:52 GMT, Bri <not@here.com> wrote:
>
>
>David W. Fenton wrote:
>
>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
| |
| david epsom dot com dot au 2006-04-07, 3:44 am |
| The differences between 97, 2000,2002,2003 just show the
normal variation due to caching and noise.
(david)
"Wayne Gillespie" < bestfit@NOhotmailSPA
M.com.au> wrote in message
news:p0ab32p5iqo0vsg
1dnrgcacbadala8v1c1@
4ax.com...
> On Thu, 06 Apr 2006 18:17:52 GMT, Bri <not@here.com> wrote:
>
>
> 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
| |
| david epsom dot com dot au 2006-04-07, 3:44 am |
| 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" <not@here.com> wrote in message news:kXcZf.2467$nf7.1564@pd7tw1no...
>
>
> David W. Fenton wrote:
>
> 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
>
| |
| Larry Linson 2006-04-07, 3:44 am |
|
"David W. Fenton" <XXXusenet@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
| |
| Lyle Fairfield 2006-04-07, 7:42 am |
| "Larry Linson" <bouncer@localhost.not> wrote in
news:IhoZf.15911$e11.5525@trnddc02:
>
> "David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote
>
>
> 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
| |
| david epsom dot com dot au 2006-04-07, 7:42 am |
|
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" <bouncer@localhost.not> wrote in message
news:IhoZf.15911$e11.5525@trnddc02...
>
> "David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote
>
>
> 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
>
>
| |
| Lyle Fairfield 2006-04-07, 7:42 am |
| "david epsom dot com dot au" < david@epsomdotcomdot
au> wrote in
news:44362c94$0$4437
0$c30e37c6@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
| |
| David W. Fenton 2006-04-07, 9:37 am |
| "Larry Linson" <bouncer@localhost.not> wrote in
news:IhoZf.15911$e11.5525@trnddc02:
> "David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote
>
>
> 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 2006-04-08, 9:27 am |
| Bri <not@here.com> wrote in news:_kyZf.6351$nf7.4592@pd7tw1no:
> David W. Fenton wrote:
>
> 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.
>
>
> 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.
>
> 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.
>
> 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.
>
> 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.
[]
>
> 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/
|
|
|
|
|