473,699 Members | 2,380 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Adding Primary Key = Better Performance ?

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
8 3269
<sh******@cs.fs u.edu> wrote in message
news:11******** *************@j 73g2000cwa.goog legroups.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
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
sh******@cs.fsu .edu wrote in
news:11******** *************@j 73g2000cwa.goog legroups.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,sing le 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
Thanks Bob, this makes sense.

May 26 '06 #5
sh******@cs.fsu .edu wrote in
news:11******** *************@j 55g2000cwa.goog legroups.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
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
sh******@cs.fsu .edu wrote in
news:11******** *************@j 55g2000cwa.goog legroups.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
I'm not trying to make a distinction.

David W. Fenton wrote:
sh******@cs.fsu .edu wrote in
news:11******** *************@j 55g2000cwa.goog legroups.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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

17
49826
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 returning large numbers of records than non-clustered indexes. Agreed? (Assuming the NC index doesn't cover the query, of course) Since it's only possible to have one clustered index, why is this almost always used for the primary key, when by...
4
1789
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 (I think) that a Primary Key is a special field that uniquely identifies each record or row within a table. My question is this: If I have a field or column whose values are all (and will be) different in every row, is that what a Primary Key...
34
4167
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 "push_back" a copy of this into a vector V. This is repeated many times in an iterative process. Ok whenever I "push_back" a copy of Class A, I also want to assign a pointer contained in an exisiting instance of a Class B to this
9
2382
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 virtual primary key, it is slow; when there is no virtual primary key, it scrolls quickly. Looking at Profiler, I see that when there is no virtual primary key, Access sends a simple select command to SQL Server. However, when there is a virtual...
9
6863
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 tried altering PRIMARY to have multiple data files and I get and error. I have tried creating a new database with multiple PRIMARY files and get an error. I can ALTER and CREATE secondary files with multiple data files with no
7
1835
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 colums one for each language, > >| or 4 rows one for each language? > > I use: > CREATE TABLE `usr_languages` ( > `pageName` varchar(25) default '',
7
3272
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 key using for example the first 2 letters of the first name and the last 2 letters of the last name AFTER the user enters the first and last names into a form? Maybe have an invisible field on the form called ID that is linked to the id field in...
1
3599
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- Common.dll, Logging.dll, Configuration.dll, Data.dll into setup project so that it is also installed automatically. It should perform what the 'Install Services.bat ' performs. I have created ProjectInstaller.cs file also
4
3829
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 rule of the business is that only band plays on the one night. The SQL statement prevents a Band name being repeated (as it is Unique). Similar statement for the Venues. CREATE TABLE Bands (BandID varchar(5) CONSTRAINT BandID PRIMARY KEY, Band...
0
9035
jinu1996
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8916
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8885
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7752
agi2029
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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6534
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4376
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4631
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2348
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2010
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.