Efficient Seek

[dhstein]

I have a seek based on a composite index. It's working fine thanks to the
great help I got in this forum. But I'm wondering about efficiencies. My
test database is small, but the real database will be larger, so I wonder if
the order of fields defined in the composite index is important to the speed
of the search. Thanks for any light you can shed on this.

 

[Arvin Meyer [MVP]]

The order of the index should match the order of the fields searched for the
fastest possible searching. That said, it is unlikely that anyone will ever
notice much difference with a million records or less. On really big
searches, especially through multiple disks and servers, the difference is
quite noticeable.

 

[Tom van Stiphout]

On Sat, 6 Dec 2008 17:41:00 -0800, dhstein

Performance is simply a matter of how many reads are needed in a
binary tree to find the record in question. If N is the number of rows
in a table, the number of reads is log(N)/log(2). So for 1,024 rows it
is 10, for 1,048,576 rows it is 20, etc. So the read time has only
doubled while the number of rows is a thousand times more. That's the
power of indexing.
Without an index it would be N/2, so for 1,048,576 rows it would take
a long time.

Keep in mind that Seek only works on table-type recordsets. Once you
split the database in front-end and back-end, you need to have a
global database object that refers to the back-end. Then you can
continue to use Seek.

-Tom.
Microsoft Access MVP

 

[Larry Linson]

I have a seek based on a composite index.
It's working fine thanks to the great help I
got in this forum. But I'm wondering about
efficiencies.

Only tabletype datasets can be "seeked", and, AFAIK, that means "only Jet
databases, and possibly the new ACE databases of Access 2007". However, the
Jet database engine (and, I am sure, ACE, as well, because it is a
'descendant' of Jet, though little has been leaked about its internals,
compared to what we've learned over the years about Jet) is very thoroughly
optimized for retrieving data effeciently from Jet databases.

There are times when SEEK is best, but they are rare indeed -- generally
your best performance will be to use a Query (the Query Builder generates
SQL) with a criteria to select the record you need (a WHERE or HAVING clause
in the SQL). And, should you switch to a server database back-end, chances
are good that it, too, will be optimized for retrieving its own data. In
fact, I've been using VBA DAO since Access 2.0, regularly and frequently,
and have NEVER had occasion that I needed to SEEK other than to demonstrate
it to my user group. (As I said, there will be cases where it is best in
particular, specific types of processing, but this is an illustration that
"rare indeed" is an accurate, perhaps even optimistic, description.)

Larry Linson
Microsoft Office Access MVP

 

[David W. Fenton]

I have a seek based on a composite index. It's working fine
thanks to the great help I got in this forum. But I'm wondering
about efficiencies. My test database is small, but the real
database will be larger, so I wonder if the order of fields
defined in the composite index is important to the speed of the
search. Thanks for any light you can shed on this.

Let me second Larry's comments about Seek:

Seek is not something that has much use in normal database
applications. The number of situations where it is helpful are very
few, the only I can think of is when you need to jump around a
recordset for some reason. You can actually optimize
..FindFirst/FindNext/FindPrevious if your recordset is ordered by the
same field you're searching on by checking how far away the target
record is from the starting point. I once had a persistent recordset
that I was navigating in this fashion and I did find that choosing
FindNext or FindPrevious instead of always using FindFirst did make
a difference, because the recordset was quite large (hundreds of
thousands of records). Seek would have worked in that situation had
the recordset been based on a single table, but it *wasn't* based on
a single table, so was not an option.

To me, Seek is a leftover from the days when our computers were
extremely slow (mid-1990s) and had very little RAM. In my opinion,
that has not been an issue for a very, very long time.

 

[a a r o n . k e m p f]

wow, that sounds like quite a pain in the butt.. maybe you should just
move to SQL Server!

----------------------------------------------------------------------------------------
Access, however, is not perfect. Performance degrades significantly as
the database size increases. The database is also prone to corruption.
Finally, starting with an Access database has tempted many developers
to do a dangerous thing. Sometimes a single-user application becomes
popular enough that there's a desire for it to be used by multiple
simultaneous users. The temptation is to just move the Access database
file to a network share, copy the application to multiple machines,
and let many users connect simultaneously. Access performance drops
off quickly with multiple users, and it's highly unlikely that an
application that was designed for a single user will work reliably
with concurrent users.

Http://msdn.microsoft.com/en-us/library/aa730870(VS.80).aspx

 

[George Hepworth]

Dude,

You left out the preceding paragraph that explains WHY Access remains the
more popular choice for most developers, despite MS efforts to encourage use
of MSDE and SQL Server Express instead,

-----------------------------
Despite the considerable effort that Microsoft put into developing MSDE and
SQL Express, Microsoft Access databases remain a popular choice for many
developers. Access is included with Microsoft Office, so most developers
already have it installed. Access makes it very easy to create databases,
and the database is stored as a single file. It's trivial to include an
Access database with an application. You simply include the database as a
file with your deployment. There's also no "server" that needs to be carried
with your deployment and installed along with your application. This means
that an application that uses an Access database can be installed by a
typical user rather than an administrator. When the application connects to
the database, it does so simply by using the path to the database file in
the connection string. The application or user can back up the database by
simply making a copy of the database file.
---------------------------

It seems to me that there are some good arguments for continuing to deploy
Access-based solutions in situations where it is appropriate to do so.

And it might also have been relevant to include the fact that this
particular MSDN article is an introduction to SQL Server Compact Edition, "
a slimmed-down version of SQL Mobile. SQL Compact removes the restrictions
that keep the SQL Mobile product from running on a desktop or laptop, making
SQL Compact the ideal, lightweight database for many scenarios." I THINK it
is relevant to the purposes of this article that it is in reference to a
version of SQL Server intended to run on a desktop or laptop as an
alternative to a server-based install, don't you?

Honesty is ALWAYS the better policy, is it not? Even when it tends to
undermine certain strongly held opinions.





message
wow, that sounds like quite a pain in the butt.. maybe you should just
move to SQL Server!

----------------------------------------------------------------------------------------
Access, however, is not perfect. Performance degrades significantly as
the database size increases. The database is also prone to corruption.
Finally, starting with an Access database has tempted many developers
to do a dangerous thing. Sometimes a single-user application becomes
popular enough that there's a desire for it to be used by multiple
simultaneous users. The temptation is to just move the Access database
file to a network share, copy the application to multiple machines,
and let many users connect simultaneously. Access performance drops
off quickly with multiple users, and it's highly unlikely that an
application that was designed for a single user will work reliably
with concurrent users.

Http://msdn.microsoft.com/en-us/library/aa730870(VS.80).aspx

 

[a a r o n . k e m p f]

Actually, it's not relevent... Because MSDE / SQL Express has been
available for a decade.. and it has the same functionality built in.

Have you ever heard of a user-instance for SQL Server?

Maybe you should WAKE UP AND LEARN SQL before running around, bashing
SQL Server, just because you don't have the intelligence necessary to
become certified in the WORLDS MOST POPULAR DATABASE.

 

[George Hepworth]

Take two or three long, deep breaths.

Gaze out a window at the beauty of the world around you.

Take a few moments to enjoy the gifts God has given you and your family.

Then, with your mind refreshed and clear, come back and read again what you
have written below.

Find the line you wrote which is factually wrong. Consider WHY you felt it
necessary (appropriate? useful? ) to make a factually inaccurate statement
knowing that everyone who sees it--not just you and me--will know that what
you said is wrong. Is attacking those with whom you disagree such an intense
compulsion in you that you can't restrict your comments to those which are
even marginally defensible?

Then find the line you wrote which is logically inconsistent. Consider WHY
you felt it necessary (appropriate? useful? ) to make a logically
incoherent statement knowing that everyone who sees it will know that it is
wrong.

Reflect carefully and fully on the implications of continuing to make
false, misleading and illogical statements in a public forum where hundreds
of stangers can read them. Reflect carefully and fully on the implications
of failing to distinguish between statements which have validity and those
which are merely filled with passion. You are, you must be, capable of
logical thought or you couldn't work with databases, the epitome of
rationality and logic.

Unfortunately, it's been a long time since anyone--experienced developers
and newbie posters alike--took any of your posts seriously, and even then it
was a poor newbie who had had no previous experience in the news groups and
didn't know your name.

No one takes you seriously anymore! Doesn't that fact alone give you pause?
Doesn't that fact alone make you want to find a better way?

On the other hand, I do take seriously your claim to have done a lot of work
with SQL Server. I am also sure you have a lot of knowledge. The shame of it
all is that you continue to undermine yourself so blatantly and so publicly.
Sometimes it even seems like you do it on purpose, as if you are looking for
public ridicule as a means of gaining attention, of some kind.

Nonetheless, I can continue to hope that you will eventually realize the way
to inner peace and happiness lies not in attacking those whose opinions
differ from your own. The world is a great big place and there is room
enough in it for both Access and SQL Server, and even an Oracle database or
two, I suppose. (That last part was an ironic jest, by the way.)

You may even find that you can eventually regain some measure of credibility
among those who would be your peers. If you can somehow learn to offer
assistance pertinent to the poster's problems, you will find that you not
only earn their gratitude, but that others who now view you as little more
than a "troll", will come to accept your presence as a positive thing. It
won't come quickly, but it can be achieved if you try.

To sum up, I can't believe that in the ten plus years you've worked with
databases that you have learned nothing more than the "Access is bad/SQL
Server is good" mantra. I have to believe, because I believe that all people
are capable of learning something, that you do know a good deal about SQL
Server and that you can make a positive contribution. Please do make the
effort. The rewards, I promise you, will be worthwhile.





message
Actually, it's not relevent... Because MSDE / SQL Express has been
available for a decade.. and it has the same functionality built in.

Have you ever heard of a user-instance for SQL Server?

Maybe you should WAKE UP AND LEARN SQL before running around, bashing
SQL Server, just because you don't have the intelligence necessary to
become certified in the WORLDS MOST POPULAR DATABASE.

 

[a a r o n . k e m p f]

I don't care what you say or what you think.

SQL Server is bigger, better, faster, more reliable and easier.

There is no benefit to Jet, except corruption and bloat.
I've learnt a lot more than Jet is bad, SQL is good.

I've been called out of bed at 4am a half dozen times when Jet causes
locking problems.
It's never happened that way for SQL Server.

I was supporting VLDB using Jet a decade ago, I know the ins and outs
of Jet.
And I realized it was a piece of shit database.

Anyone with a clue would do the same.

It's like when they call mySQL the database engine for 'poor college
kids'-- Jet's even worse-- the database engine for 'poor Jr High
School kids'
It's just a piece of shit ass database, and anyone that tells you
otherwise is an infected jet _IDIOT_.

Jet corrupts at a half dozen users and a mere 25mb of data.

I've had Jet corruptions with 3-4 users at Premera.
Anyone with a clue would move to a more reliable database.

and it makes me fucking sick that you sit there and proclaim that
anyone using SQL Server -HAS- to be using Linked Tables.

Linked Tables are another tier of complexity that is not necessary,
beneficial, or fun.

Nobody needs 3 tiers of Jet.
You don't need an Auto-FE-Updater (GAG).

It's really simple.

Keep all your objects on SQL Server, and use ADP.

Anything else is just a waste of fucking time.

So sorry if you get offended by swearing.

Maybe you should make the other cocksuckers-- in this newsgroup-- who
resort to gang violence and bullying-- that maybe they should shut the
**** up and stop slamming anyone that reccomends SQL Server.

You guys aren't right just because more people like Jet in this group.

SQL Server is the worlds most popular database, and you can prepare to
be fox-pro'ed.
SQL Server is the worlds most popular database, and you can prepare to
be fox-pro'ed.
SQL Server is the worlds most popular database, and you can prepare to
be fox-pro'ed.

The decision was already made- a long time ago- to get rid of Jet.

You're just using a lame duck database for now.

 

[a a r o n . k e m p f]

tell me ONE things that is not coherent, or factual

meanwhile, Tony Toews, the dipshit himself-- proclaims that anyone
using Access in a way that's different from him-- is somehow WRONG and
a THREAT and needs to be stalked.

GO AHEAD **** NUT, FOLLOW ME AROUND, TELL EVERYONE THAT I AM THE ONE
THAT NEEDLESSLY RECCOMENDS SQL SERVER
AND THEN TELL THE OP THAT NO-- ADP DOESN"T ALLOW MULTIPLE PEOPLE TO
EDIT QUERIES AT THE SAME TIME
AND THEN TELL THE OP THAT NO-- ADP DOESN"T ALLOW MULTIPLE PEOPLE TO
EDIT QUERIES AT THE SAME TIME
AND THEN TELL THE OP THAT NO-- ADP DOESN"T ALLOW MULTIPLE PEOPLE TO
EDIT QUERIES AT THE SAME TIME
AND THEN TELL THE OP THAT NO-- ADP DOESN"T ALLOW MULTIPLE PEOPLE TO
EDIT QUERIES AT THE SAME TIME


AND YOU FUCKING SIT THERE AND TELL ME THAT I DON'T USE FACTS?

Mind your own fucking business.

Shut the **** up.
And lose the training wheels you fucking crybaby.

So sorry that you don't see the logic in clinging to a database (jet)
that corrupts and doesn't scale.
Maybe you should get certified in SQL Server before you try to tell me
that I'm fucking wrong.

**** you for having the audacity.

Maybe you have a crystal ball-- so you can forecast when your users
need to grow past _THREE_ users.
Maybe you have a crystal ball-- so you can forecast when Access hits
the 2gb limit (or should I say 1gb limit ROFL)

 

[Tony Toews [MVP]]

"a a r o n . k e m p f @ g m a i l . c o m" <[email protected]>
wrote:

And that paragraph is exceedingly slanted and by someone who clearly
doesn't understand Access.

How many hours did it take you to find that paragraph?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[a a r o n . k e m p f]

come on Tony.

Microsoft has been putting out statements almost every month for the
past decade talking about the fall of Jet.

Eat shit and learn a real database, fucking script kiddie

-Aaron