Records across LAN

[Damon Grieves]

Hi
I just want to be sure I understand how the Access client works. If I have
an Access back end with a million records on a server and an Access client.
If the client is installed on the users pc and run by the user...does Access
drag the whole million records across the LAN when we call up one record ie
filters for that record locally? If the client is on the same server as the
back end, and the user starts up the client on the server from their pc...is
only one record transferred across the LAN to the user pc or the million?
Is the way around this to use the msde back end with a local client?
TIA

[Bill]

Access drags all the records across the network. The server is not
doing any processing like a SQL server.

[Jack MacDonald]

My understanding is that Access will retrieve all the "pages"
necessary to find the record within the appropriate INDEX. Using the
information found in the index, it will retrieve the page that
contains the specific record. Pages are (IIRC) 2K each.

If your database is not indexed, then it will need to scan all the
rows. Yikes!! with one million records.
On Fri, 04 Feb 2005 01:19:10 GMT, "Damon Grieves"
<da****@vfeNOSPAMmail.net> wrote:

Hi
I just want to be sure I understand how the Access client works. If I have
an Access back end with a million records on a server and an Access client.
If the client is installed on the users pc and run by the user...does Access
drag the whole million records across the LAN when we call up one record ie
filters for that record locally? If the client is on the same server as the
back end, and the user starts up the client on the server from their pc...is
only one record transferred across the LAN to the user pc or the million?
Is the way around this to use the msde back end with a local client?
TIA

**********************
ja**************@telusTELUS.net
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

[Rick Brandt]

Bill wrote:

Access drags all the records across the network. The server is not
doing any processing like a SQL server.

The second statement above is true, but it does not necessarily follow from that
that the first one is. In fact the first would only be true if no index was
present that could be utilized.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Damon Grieves]

If the database is suitably indexed, how do the two scenarios compare ..of
the client on the server and the client local on the users pc?

"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:36*************@individual.net...

Bill wrote:

Access drags all the records across the network. The server is not
doing any processing like a SQL server.
The second statement above is true, but it does not necessarily follow

from that that the first one is. In fact the first would only be true if no index was present that could be utilized.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Rick Brandt]

Damon Grieves wrote:

If the database is suitably indexed, how do the two scenarios compare
..of the client on the server and the client local on the users pc?

The simplest way to think about it is that the server is acting as a remote hard
drive. The exact same I/O that would happen on a local hard drive is what
happens over the LAN when running Access as a file share.

The reason Access is such a good performing desktop database is that it is
intelligent enough to process queries with as little I/O as possible. This is
vital since disk I/O is the single biggest factor in how a database performs.
This intelligence is not tossed out merely because the file is being accessed
over a network.

Given a query...

SELECT Field1, Field2, ... FROM Table1
WHERE Field3 = SomeValue

If Field3 has an index then pages containing the index are pulled until the
information about which pages contain the data has been processed. Then those
pages containing the data are pulled. If Field3 is the Primary Key (thus we are
selecting a single record) then the amount of I/O is tiny compared to "pulling
the entire table".
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Albert D. Kallal]

"Bill" <bm*******@excite.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Access drags all the records across the network. The server is not
doing any processing like a SQL server.

No, this is complete wrong. If you have a million records, and we request
the
record via some number (customer number, or invoice number for example),
and this key field used is indexed, then only the record searched for is
brought across the network. In fact, more correctly the "page" is loaded,
which in practice might contain a few records.

Of course, if the field is NOT indexed, then a full table scan must occur.
However, Oracle, or Sql-server ALSO WILL DO A FULL table scan if the field
you are working with is NOT indexed. Of course, in this case, the server
based
system gives the disk drive a thrashing while no network activity occurs.
With
ms-access, since you are "remote" use of the disk drive, then everything is
not
only loaded (like the other data engines), but since we are using the disk
drive ACROSS a network, then all disk activity occurs across this network
also.

So, lets put this in perspective. Lets assume we have MS-Sql-server,
Oracle, and ms-access all installed ON THE SAME MACHINE!!!

Example #1:
Retrieve a customer record via some indexed field (ie: custoemrID).

NONE of the databases systems mentioned will read all records from disk, but
ONLY read the required record from disk. This done by utilizing a index.

Example #2:
Retrieve a customer records via a NON indexed field

ALL of the databases systems mentioned will read ALL records from disk.

Remember, when you use ms-access, you are in effect using a database engine,
but that engine runs on YOUR pc.

Now, lets change the system:

MS-Sql-server, Oracle, are installed on a server on your network, you
have ms-access installed on your LOCAL pc.

Now, in this case, you did NOT install Oracle etc. on your local pc. And,
further, you do not (and DID NOT) HAVE TO install ms-access on the
server...since our shared mdb file is now just a plain Jane file sitting on
the server.

Now:
Example #1:
Retrieve a customer record via some indexed field (ie: custoemrID).

NONE of the databases systems mentioned will read all records from disk.
Again, this statement holds true. WHY WOULD the database start to READ ALL
RECORDS? Why would the database all of a sudden change its behavior when it
has a index to use? Once again, NONE of the database will read all records
from disk. All databases ONLY read the required record from disk. This done
by utilizing a index. The different between the client to server based
systems (ms-sql, Oracle etc.) is that the indexing, and processing to read
the record is done on the server, and once the record is found, then it
THE ONE RECORD is transmitted to the client pc. In the case of ms-access,
the
one record is retrieved, but the processing and searching is done on the
CLIENT pc, and this searching occurs ACROSS the network. however, the whole
table is NOT pulled across the network. Again, why would if the file is
local, and ms-access does not have to read all records, why then would a
network make a difference? (it does not, and in fact ms-access does not even
KNOW there is a network involved. The ONLY factor here is will ms-access
read all records from disk into memory. The fact that the file is local, or
shared down the hall DOES NOT CHANGE this fact that ms-access does NOT read
all records into memory. However, EVERY SINGLE record that ms-access does
in fact read DOES GET transmitted across the network. So, in the case of 1
million records, the amount of data transmitted down he write is in fact
very
small, and the whole table is NOT transferred.

When ms-access is running local on your pc, and you ask it to read a record
from disk, it DOES NOT read the whole table into memory, as the performance
of the system would be very bad. And, if you move the file to a network
share, it has ZERO EFFECT on the amount of data that ms-access will read
into memory. I repeat again: ZERO effect...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal