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 3272
<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
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******** *************@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.
Thanks Bob, this makes sense. 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/
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******** *************@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/
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/ 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 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...
|
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...
|
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
|
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...
|
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
| |
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 '',
|
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...
|
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
|
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...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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...
| |
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...
|
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...
|
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...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
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...
| |