Adding Primary Key = Better Performance ?

shumaker

I'm wondering if adding an autonumber primary key will improve the
performance of a multiuser access database on a network share. I have
a website that lists many tips for improving performance of access, but
doesn't mention primary keys.

However, it seems logical to think that having no primary key means
that when a user updates a record, the database has to do comparisons
on multiple fields to identify the specific record being updated. With
a primary key, the database can index into that primary key of the
record being updated much quicker.

Does this make sense?
If not, I'm wondering, if I update a field in a record via a form, how
does the database identify quickly which record is being updated and
write that update. Does it generate some sort of hidden index on it's
own that is used for this purpose?

I could create an index on a text field that contains very few
duplicates, but does that index only get used for queries that use it
in the where clause?

The most notable performance problem is users flipping between records
on a form after making updates to the record. The form is based on a
query that takes the user's initials and pulls all records that have
those initials in a partiulcar field. So each user has a mutually
exclusive set of records they are working on, but the records are all
from the same table, and each suer has 200-1000 records at a time.

May 25 '06 #1

Keith Wilby

<sh******@cs.fsu.edu> wrote in message
news:11*********************@j73g2000cwa.googlegro ups.com...

I'm wondering if adding an autonumber primary key will improve the
performance of a multiuser access database on a network share. I have
a website that lists many tips for improving performance of access, but
doesn't mention primary keys.

However, it seems logical to think that having no primary key means
that when a user updates a record, the database has to do comparisons
on multiple fields to identify the specific record being updated. With
a primary key, the database can index into that primary key of the
record being updated much quicker.

Does this make sense?

Search the help for "indexing" - extract:
An index helps Microsoft Access find and sort records faster. Access uses
indexes in a table as you use an index in a book: to find data, it looks up
the location of the data in the index. You can create indexes based on a
single field or on multiple fields. Multiple-field indexes enable you to
distinguish between records in which the first field may have the same
value.

Keith.

www.keithwilby.com

May 26 '06 #2

shumaker

Thank you, I understand the basic concept of indexing.

But what I'm wondering, is in the abscence of an index or primary key,
how does the DBMS find a particular record that a user is updating via
a form?

Does it have to do a sequential search for the record that matches all
fields, and then update it? This would be computationally slow, but I
don't know of any other search algorithms that would work with an
unordered set of data that has no indexing or hashtable support.

May 26 '06 #3

Bob Quintal

sh******@cs.fsu.edu wrote in
news:11*********************@j73g2000cwa.googlegro ups.com:

I'm wondering if adding an autonumber primary key will improve
the performance of a multiuser access database on a network
share. I have a website that lists many tips for improving
performance of access, but doesn't mention primary keys.

However, it seems logical to think that having no primary key
means that when a user updates a record, the database has to
do comparisons on multiple fields to identify the specific
record being updated. With a primary key, the database can
index into that primary key of the record being updated much
quicker.

Does this make sense?
If the tables are stored in an ODBC database, you can't update
without a primary key, whether an autonumber,single field or
multiple column index.

If the tables are stored in Access then the situation is
different. If you use update queries to change the data, it
makes sense. Access will create hidden indexes for such things.
but the creation of an index will slow the process down.

However, updating via a bound form does not need indexes as the
correct record is already known (and displayed in the form)
If not, I'm wondering, if I update a field in a record via a
form, how does the database identify quickly which record is
being updated and write that update. Does it generate some
sort of hidden index on it's own that is used for this
purpose?
Doesn't need to find the record, it's already at the record.
I could create an index on a text field that contains very few
duplicates, but does that index only get used for queries that
use it in the where clause?
Queries, forms and reports will use existing indexes if they are
there. If they are not there, Access will usually create a
hidden index linked to the object.
The most notable performance problem is users flipping between
records on a form after making updates to the record. The
form is based on a query that takes the user's initials and
pulls all records that have those initials in a partiulcar
field. So each user has a mutually exclusive set of records
they are working on, but the records are all from the same
table, and each suer has 200-1000 records at a time.

this should not be a problem, Try creating an index on the sort
order used in the form.

--
Bob Quintal

PA is y I've altered my email address.

May 26 '06 #4

shumaker

Thanks Bob, this makes sense.

May 26 '06 #5

David W. Fenton

sh******@cs.fsu.edu wrote in
news:11*********************@j55g2000cwa.googlegro ups.com:

But what I'm wondering, is in the abscence of an index or primary
key, how does the DBMS find a particular record that a user is
updating via a form?

Does it have to do a sequential search for the record that matches
all fields, and then update it? This would be computationally
slow, but I don't know of any other search algorithms that would
work with an unordered set of data that has no indexing or
hashtable support.

Well, most database engines actually keep track of the data by its
location in the storage system (or the same thing virtualized in
memory), using offsets.

When you retrieve a particular record by choosing a value on a
field, Jet looks at the index for that record, and if it's a primary
key index, it's going to point to the relevant data page(s) in the
MDB file, and that's how it knows which record(s) to load.

The database engine actually doesn't give a rat's ass about the
content of your data, so for it, a PK really doesn't mean anything,
since each record is unique based on its starting point and length.
The difficulty for the programmer in a non-PK situation is how to
figure out how to tell the db engine which of those unique
offset/length combinations to retrieve.

It seems to me like you're making the problem more difficult than it
is. Think of it this way: if you had a spreadsheet with 5 rows, and
the data in all the rows was identical, you could still identify
each row by the row number. From a SQL point of view, though, you
couldn't, since SQL works with sets based on the actual data. But
SQL is only an interface to the database engines, not the db engines
themselves.

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

May 27 '06 #6

shumaker

Thanks. Wasn't really trying to make it difficult. I was looking at
it from the "sets" point of view where elements of the set have no
location, I.E. it gets a set of records with no row numbers. And MS
SQL Server seems to enforce this POV by not allowing edits to a record
with no PK. So I was just wondering from a low level perspective what
mechanism was implemented by Access to allow the edits in a table with
no PK, whether it was some sort of search algorithm, or hidden "row
number", index, or pointer right into the structure of the database
file.

I didn't reallize that the client was always "at" a particular record
as Bob said. I just assumed that which record was currently displayed
was purely client side.

It's pretty clear now.

May 28 '06 #7

David W. Fenton

sh******@cs.fsu.edu wrote in
news:11*********************@j55g2000cwa.googlegro ups.com:

Thanks. Wasn't really trying to make it difficult. I was looking
at it from the "sets" point of view where elements of the set have
no location, I.E. it gets a set of records with no row numbers.
And MS SQL Server seems to enforce this POV by not allowing edits
to a record with no PK. . . .
I don't think that's SQL Server doing that, but the data interface
layer you're using (ODBC or ADO).
. . . So I was just wondering from a low level perspective what
mechanism was implemented by Access to allow the edits in a table
with no PK, whether it was some sort of search algorithm, or
hidden "row number", index, or pointer right into the structure of
the database file.
When you're using Access against Jet tables, there's no intervening
data interface layer. Access talks directly to Jet, since it's a
built-in part of Access.
I didn't reallize that the client was always "at" a particular
record as Bob said. I just assumed that which record was
currently displayed was purely client side.

I don't quite understand the distinction you're trying to make.

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

May 28 '06 #8

shumaker

I'm not trying to make a distinction.

David W. Fenton wrote:

sh******@cs.fsu.edu wrote in
news:11*********************@j55g2000cwa.googlegro ups.com:

Thanks. Wasn't really trying to make it difficult. I was looking
at it from the "sets" point of view where elements of the set have
no location, I.E. it gets a set of records with no row numbers.
And MS SQL Server seems to enforce this POV by not allowing edits
to a record with no PK. . . .

I don't think that's SQL Server doing that, but the data interface
layer you're using (ODBC or ADO).

. . . So I was just wondering from a low level perspective what
mechanism was implemented by Access to allow the edits in a table
with no PK, whether it was some sort of search algorithm, or
hidden "row number", index, or pointer right into the structure of
the database file.

When you're using Access against Jet tables, there's no intervening
data interface layer. Access talks directly to Jet, since it's a
built-in part of Access.

I didn't reallize that the client was always "at" a particular
record as Bob said. I just assumed that which record was
currently displayed was purely client side.

I don't quite understand the distinction you're trying to make.

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

May 31 '06 #9