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. 8 3250
<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
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. 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.
Thanks Bob, this makes sense. 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/
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. 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/
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/ This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Philip Yale |
last post by:
I'm probably going to get shot down with thousands of reasons for
this, but I've never really heard or read a convincing explanation, so
here goes ...
Clustered indexes are more efficient at...
|
by: Mavis Tilden |
last post by:
Hi all,
So I've been reading the newsgroups, and reading a few books trying to learn
SQL and SQL Server 2000. The books tell me I need a Primary Key, and that
every table should have one. I know...
|
by: Adam Hartshorne |
last post by:
Hi All,
I have the following problem, and I would be extremely grateful if
somebody would be kind enough to suggest an efficient solution to it.
I create an instance of a Class A, and...
|
by: Neil |
last post by:
I've been discussing here a SQL 7 view which scrolls slowly when linked to
an Access 2000 MDB. After trying various things, I've distilled it down to
the following: when the linked view has a...
|
by: 101 |
last post by:
Taking a course on SQL. They are saying you can get better performance by
having multiple files for a group.
They then graphically show an example of "Primary" with multiple data files.
I have...
|
by: CAH |
last post by:
I once wrote on usenet and got a very good answer . This one
JN wrote:
> >| Should one place all the text in one big table, or is i better to place
> >| it in several tabels. Should one make 4...
|
by: sea |
last post by:
Is it a good idea to programatically create a primary key? For example
in a table called names, I have the following fields, (1) firstname
(2)lastname (3) ID
- will it be ok to create a primary...
|
by: rohan_from_mars |
last post by:
I have a windows service and am also using Enterprise Library June 2005
version. Now i have created Setup and Deployment Project which installs
the service. but how do i add EL dlls-...
|
by: Peter |
last post by:
I am interested in informed feedback on the use of Constraints, Primary Keys
and Unique.
The following SQL statement creates a Bands tables for a database of
bookings Bands into Venues, where the...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |