Access Networking

[Guest]

Is anyone aware of any networking issues with Access
I am looking after a small business which has an Access 2000 database on the server and they all connect to it via the network. they are having some issues with it not working properly. New information doesn't appear when other people open the database. most of the PC's are win XP
Any general idea's would be really helpful.

 

[Pat Garard]

G'Day Neale,

There used to be some problems with Novell, but
none recently.

I presume that you have a Backend on the Server
and multiple Front Ends on individual PCs.

Be aware that if an individual PC queries that BE
and then cycles through the Records, it will not
necessarily REquery the BE and , in that case, will
not 'see' recent additions. You would need to
examine the FE design to confirm this. Another
thing you can test is the effect on an FE PC of
quitting Access and immediately starting it again -
if this ReFresh of the local cache resolves the
issue, then ReQuerying is the problem.

Be aware also that the BE is UNintelligent. When
an FE queries it, ALL records are passed to that
FE across the Network to Jet on the FE PC for
selection according to the query criteria.

I mention this because MSDE, a cut down version
of SQL Server, is available with Access 2000.

If MSDE were used, the Records would be
selected on the Server; and only those meeting
the criteria are passed over the network.

The reduction in network overhead will more
than compensate for any deficiencies in MSDE,
although the Server will have to work harder.

Your budget may well determine the outcome.
--
Regards,
Pat Garard
Australia

Anne & Pat Garard.
apgarardATbigpondDOTnetDOTau
_______________________________________________

 

[david epsom dot com dot au]

an FE queries it, ALL records are passed to that

FE across the Network to Jet on the FE PC for
selection according to the query criteria.

No, the index is passed to Jet on FE PC, so that
it can select the record that it needs.

The reduction in network overhead will more
than compensate for any deficiencies in MSDE,

Not on my tests: and remember that MSDE is throttled,
so you will never see the reputed efficiencies
of scale from having all the work done on one
processor on the server, rather than distributed
to 100s of separate processors on the workstations.

(david)

 

[Tom Wickerath]

David,

I believe Pat is correct in his original statement. Everything I have always heard is
that *ALL* records are passed over the network, and that any filtering only occurs on the
front-end. I have never heard or read anywhere before that only "the index is passed to
Jet on the FE PC for selection according to the query criteria".

______________________________________

an FE queries it, ALL records are passed to that
FE across the Network to Jet on the FE PC for
selection according to the query criteria.

No, the index is passed to Jet on FE PC, so that
it can select the record that it needs.

The reduction in network overhead will more
than compensate for any deficiencies in MSDE,

Not on my tests: and remember that MSDE is throttled,
so you will never see the reputed efficiencies
of scale from having all the work done on one
processor on the server, rather than distributed
to 100s of separate processors on the workstations.

(david)
_________________________________________

 

[Albert D. Kallal]

David,

I believe Pat is correct in his original statement. Everything I have always heard is
that *ALL* records are passed over the network, and that any filtering only occurs on the
front-end. I have never heard or read anywhere before that only "the index is passed to
Jet on the FE PC for selection according to the query criteria".

The idea that all records are sent down the wire is completely wrong. I
don't know where this myth, or idea comes from. You do any google search on
these newsgroups, and you will NOT FIND ONE EXPERIENCED DEVELOPER even
hinting at such a silly statement.

Please find me one article that says the above?

Remember, when you are using ms-access in a JET file share, you are talking
about a just a plain file sitting on the he hard disk. Try creating a huge
table with 500,000 records in it. Now, create another database with 5
records in it. Now, try loading either database, and retrieve one record
from the table by the key id. Do you think that 500,000 records are loading
into your computers memory to grab the one record? Ms-access DOES NOT load
the whole table into memory. Now, the fact that the file is sitting on your
local hard disk, or across the network down the hall MAKES ZERO DIFFERENCES
to this fact. In other words, the ONLY QUESTION WE MUST ASK is:

When JET loads a table, does it load the whole table into memory?

Again, I stress the fact that the file is sitting on your local hard disk,
or down the hall on a network share MAKES ZERO DIFFERENCE. In fact,
ms-access does not know, or even CARE if the file is sitting on your local
hard disk, or a file share on the server. Since JET does not load the whole
table into memory, then when using a network, then all records are NOT sent
down the wire.

Of course, if the file is sitting on your local hard disk, and you do a sql
query on the file, and a index can not be used, THEN YES, the whole table is
brought into memory. However, most queries can use a index, and thus only
very small chunks of the file are brought into memory. (again, the location
of the file on the local hard disk, or down the hall on the server does NOT
CHANGE this fact). Since only small parts of the index are loaded, and the
location of the record is determined, then this process also applies when
using a network. I mean, how can ms-access find a single record out of a
table of 100,000 records in an blink of an eye? Fact is, ms-access does NOT
load the whole table to do a search. There is no logical reason, or argument
as to why the process as to how ms-access works on your local hard disk as
opposed to a file share would cause/change the number of records ms-access
will load. So, as mentioned, the network does NOT change this fact, or how
this process works.

 

[Tony Toews]

I believe Pat is correct in his original statement. Everything I have always heard is
that *ALL* records are passed over the network, and that any filtering only occurs on the
front-end. I have never heard or read anywhere before that only "the index is passed to
Jet on the FE PC for selection according to the query criteria".

Respectfully disagree. While I don't recall any mention of this on
any official documentation I can't see why any database system of any
sort would not use the indexes wherever possible to retrieve only the
relevant pages/records.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tom Wickerath]

Albert and Tony,

I respectfully disagree with both of you.

Access is a file server--not a true relational database. I agree that Jet must scan all
rows of the appropriate table columns to recover their values for comparison with the
criteria. I think where we differ in opinion is that I maintain that in addition to
scanning all rows, it also retrieves all rows--not just the indexes. Operations such as
sorting or filtering are done on the FE, using the entire recordset that was sent over the
wire.

David indicated: "No, the index is passed to Jet on FE PC, so that it can select the
record that it needs." If I am understanding him correctly, he seems to be implying that
Jet can then use this index data in such as way as to have the back-end database return
only the appropriate records from a given recordset (or that the FE database can
"cherry-pick only the appropriate records from the BE database).

I quote the following from page 57 of "Access 2000 Client/Server Solutions", written by
Lars M. Klander (Published by Coriolis). I added the bold font shown below, although it
may not show up in the newsreader copy:
"Many people mistakenly believe that an Access MDB database file stored on a file server
acts as a database server. This isn't the case. The difference lies in the way in which
data is retrieved when Access is acting as the front end to a database server versus when
the data is stored in an Access MDB file. Imagine the following scenario:

Assume that you have a table with 500,000 records. A user runs a query that's based on
the 500,000-record table stored in an Access database on a file server. The user wants to
see a list of all the Nevadans who make more than $75,000 per year. With the data stored
on the file server in the Access MDB file format, all records are sent over the network to
the workstation, and the query is performed on the workstation. This results in
significant network traffic, as shown in Figure 2.3."
I have seen similar wording in other published works as well. Perhaps someone from
Microsoft should weigh in on this issue and give us the "official" word.

Tom Wickerath


__________________________________________

David,

I believe Pat is correct in his original statement. Everything I have always heard is
that *ALL* records are passed over the network, and that any filtering only occurs on the
front-end. I have never heard or read anywhere before that only "the index is passed to
Jet on the FE PC for selection according to the query criteria".

The idea that all records are sent down the wire is completely wrong. I
don't know where this myth, or idea comes from. You do any google search on
these newsgroups, and you will NOT FIND ONE EXPERIENCED DEVELOPER even
hinting at such a silly statement.

Please find me one article that says the above?

Remember, when you are using ms-access in a JET file share, you are talking
about a just a plain file sitting on the he hard disk. Try creating a huge
table with 500,000 records in it. Now, create another database with 5
records in it. Now, try loading either database, and retrieve one record
from the table by the key id. Do you think that 500,000 records are loading
into your computers memory to grab the one record? Ms-access DOES NOT load
the whole table into memory. Now, the fact that the file is sitting on your
local hard disk, or across the network down the hall MAKES ZERO DIFFERENCES
to this fact. In other words, the ONLY QUESTION WE MUST ASK is:

When JET loads a table, does it load the whole table into memory?

Again, I stress the fact that the file is sitting on your local hard disk,
or down the hall on a network share MAKES ZERO DIFFERENCE. In fact,
ms-access does not know, or even CARE if the file is sitting on your local
hard disk, or a file share on the server. Since JET does not load the whole
table into memory, then when using a network, then all records are NOT sent
down the wire.

Of course, if the file is sitting on your local hard disk, and you do a sql
query on the file, and a index can not be used, THEN YES, the whole table is
brought into memory. However, most queries can use a index, and thus only
very small chunks of the file are brought into memory. (again, the location
of the file on the local hard disk, or down the hall on the server does NOT
CHANGE this fact). Since only small parts of the index are loaded, and the
location of the record is determined, then this process also applies when
using a network. I mean, how can ms-access find a single record out of a
table of 100,000 records in an blink of an eye? Fact is, ms-access does NOT
load the whole table to do a search. There is no logical reason, or argument
as to why the process as to how ms-access works on your local hard disk as
opposed to a file share would cause/change the number of records ms-access
will load. So, as mentioned, the network does NOT change this fact, or how
this process works.

 

[Pat Garard]

Dear, dear, dear - how can this be????
Such incredible ructions!!!!

Let's try to agree, as Gentlemen, on two simple issues:

1. Whether an Access Back End is on the local PC
OR
on a Server, it is JUST ANOTHER NETWORK file!!!

Whatever work Jet does is the SAME!!!!

ALL the SELECTION of records takes place
where the Front End is - regardless of how this
selection takes place, or what volume of data is read.

The ONLY advantage of having it on the Server
is that it can be shared.

2. A relationl Database Server (Oracle, ..., SQL Server,
MSDE, ...) carries out intelligent work on the SERVER.

Access itself CHANGES according to whether the Back
End is Access OR an SQL Server.

With an SQL Server, Access uses Stored Procedures
that exist, and are executed, on the SERVER - pointers
to the selected Records are the ONLY results returned,
in the first instance, OVER THE NETWORK.

Hence the reduction of network traffic.

Please focus first on Principles!

In practise, the business needs and the budget determine the
(sometimes disastrous) outcome!!
--
Regards,
Pat Garard
Australia

Anne & Pat Garard.
apgarardATbigpondDOTnetDOTau
_______________________________________________

 

[Pat Garard]

G'Day AGAIN Neale,

Well!! You asked for some general idea's - sometimes a
small pebble provokes greater waves that a large Brick!!

But I am sure that it does us all Good!!
--
Regards,
Pat Garard
Australia

Anne & Pat Garard.
apgarardATbigpondDOTnetDOTau
_______________________________________________

 

[Albert D. Kallal]

Albert and Tony,

I respectfully disagree with both of you.

Access is a file server--not a true relational database. I agree that Jet must scan all
rows of the appropriate table columns to recover their values for comparison with the
criteria.

Sure, the fact that access is a file based system does not change anything.
And, by the way, the fact of ms-access being a file based system has NO
RELEVANCE on the argument of it being relational. (this is two different
issues). So, no, I don't agree with the above at all. JET does not scan all
rows. How then does my example of having a table with 1 record perform the
same with a table that has 5000 records? JET uses a index when there is one.
In fact all pc based systems like FoxPro, Dbase, DataEase etc etc etc. all
use a index to speed up retrieval of a record. Ask your self how can JET
possibility retrieve one record from a table of say 100,000 records in a
blink of an eye? If it had to retrieve all records, then how could this
search occur so fast?

The speed the database would be absolute horrible if all rows had to be
scanned to retrieve a value each time. There has not been a pc database
system of any wide spread use that scans all rows. The only exception to
this would be Excel (however, I am sure we don't think of Excel as a
database). However, Excel MUST scan all rows when searching a value.
JET/Ms-access does no such thing.

I think where we differ in opinion is that I maintain that in addition to
scanning all rows, it also retrieves all rows--not just the indexes. Operations such as
sorting or filtering are done on the FE, using the entire recordset that was sent over the
wire.

No, not all. Ask your self then what does a index do for a file? Lets drop
the issue the server/client, and simply ask your self the following
question:

Why is a index faster on a file then no index?

You absolute MUST be able to understand and answer the above question. If
you do not understand the above question, then of course you will make the
errorious conclusion that every time JET executes a query, all rows are
returned. This is simply not the case.

David indicated: "No, the index is passed to Jet on FE PC, so that it can select the
record that it needs." If I am understanding him correctly, he seems to be implying that
Jet can then use this index data in such as way as to have the back-end database return
only the appropriate records from a given recordset (or that the FE database can
"cherry-pick only the appropriate records from the BE database).

Yes, that is absolute correct. In fact, not even all of the index needs to
be sent down the wire, but only parts of the "tree" structure that the index
uses. Again, there is no difference between placing the file on your hard
disk, or the hard disk down the hall. Fact is, JET does not load all
records. JET does not ALWAYS read all records to grab one record. This
process of grabbing one record via a index does not change if the file is on
drive c: or some file share. (in fact, as mentioned, JET does not even know,
or care where the file is. (same goes for word, excel etc. They are all file
based systems).

I quote the following from page 57 of "Access 2000 Client/Server Solutions", written by
Lars M. Klander (Published by Coriolis). I added the bold font shown below, although it
may not show up in the newsreader copy:

"Many people mistakenly believe that an Access MDB database file stored on a file server
acts as a database server. This isn't the case.

The above is 100% true, but the above fact has ABSOLUTE NO RELEVANCE to this
discussion of how JET retrieves a record. The above has NO change in the
fact that JET will (or will not) retrieve the whole table to grab one
record. The above makes no such claim, and the above does not change, or
apply to this discussion in any way. So, while many people may mistakenly
believe that a JET file share is a database server, that don't change what
we are saying.

You have to go back to the issue of the index, and how many records JET will
retrieve using that index. So, while it is 100% true that JET is not a
client/server based system, that argument makes no change as if JET will
*always* read the whole table. We have two issues here. No one here is
claiming that JET is a client to server, but that does not mean that JET
loads the whole table to read one record.

Assume that you have a table with 500,000 records. A user runs a query that's based on
the 500,000-record table stored in an Access database on a file server. The user wants to
see a list of all the Nevadans who make more than $75,000 per year. With the data stored
on the file server in the Access MDB file format, all records are sent over the network to
the workstation,

If the book is making the above statement, then it is wrong!

In fact, what will happen is only the list of Nevadans will be sent down the
wire IF YOU HAVE an index on that state field (so, to be more clear: the
above is true/false depending on if a index is on that field). On a true
server based system, ONLY the required records are returned REGARDLESS if a
index is present. Of course, on the server, if a index is present, then only
records meeting the Nevadans is read from the hard disk! If you further have
a index on the Yearly Salary field, then JET again can further reduce the
number of records sent down the wire (it can't do this in all cases...but it
certainly will at least restrict the list to the Nevadans).

For sure, using a server based system can reduce bandwidth requirements
more, and for sure, even when NO INDEX is present, ONLY those records that
match the criteria are returned are *sent* down the wire. With a JET/index,
then things get a more grey.

However, ask yourself how indexes work, and how many records will be read
when using a index. As mentioned, JET does not care (or even know) anything
if the file is on your drive c:, or on a VPN network connection half way
across the world.

Ask your self what happens when you move a word document to a server.
Really, the only thing that is changed is now a network is between word, and
the word doc. In the case of word, you can't load one record, or part of a
document, so yes, all of the file will have to come down the wire. And, if
the word doc is on you local drive c, then all of the document is read from
drive c: (again, note how the location of the file does NOT change how much,
or how the file will be loaded). So, in the case of ms-access, since as a
matter of normal operation all records are not loaded, then the again using
a network will not change this fact.

Again, ask yourself why, or how does a index speed up data retrieval? Ask
yourself what does a index do? Ask yourself how does a index reduce the
number of records that JET will read? The only question you have to answer
here is:

Why does a index speed things up?
What does a index do?

I mean, by using a index, how/why does retrieval time get reduced?
Further, notice how I am using the word "JET" here, as the last 3 versions
of ms-access have shipped with a true client to server based data engine on
the office cd. So, any statement, or assuming about ms-access must refer to
what data engine that you are using. (sql server, MSDE, or JET).

Ms-access is not really a database anyway, but only a CLIENT to some
database (or data engine) that you choose. We had two choices for the last 3
versions of ms-access. However, our discussion is referring to a JET file
share, and this is NOT a client to server setup. However, this issue does
not change the fact if JET is Relational, and it also does not change the
fact that JET does NOT need to read all records in a table to retrieve a
record.

There is no question here as to the fact that client to server can reduce
bandwith requriemtns better then can JET, but that don't mean JET always
reads the whole table each time it grabs a reocrd.

 

[Tom Wickerath]

Albert,

And, by the way, the fact of ms-access being a file based system has NO

RELEVANCE on the argument of it being relational.

I'll let you argue that one with John Viescas, who is also an Access MVP and published
author. He has been a guest speaker many times to the Pacific NW Access Developer's
Group. I specifically remember him mentioning this several years ago. He added that
there was a lot of disagreement on this matter, but he contended that a file based system
does not follow the relational model.

Since your initial post, where you stated "Please find me one article that says the
above?", I have found additional references that support my position. However, it's
getting a bit too late in the evening (close to midnight) for me to stay up tonight typing
out word-for-word the information I have found. I can possibly do that later this week,
but I do have a job that I need to go to tomorrow morning, so I cannot spend more time
tonight. I also have very important commitments for the first three evenings of this
week, which may not make it possible to respond earlier. But I will respond with more
information before the week is over.

Let me turn your question around: How about YOU finding one or more articles that support
your position? Forget all this "ask yourself" nonsense! In God I trust. All others,
including you, Albert, show me the data!

I cannot answer your question:
"Ask your self how can JET possibility retrieve one record from a table
of say 100,000 records in a blink of an eye?"

since I have no knowledge of your network! I can tell you for certain that I have never
seen such performance on *any* shared Access database being run at The Boeing Company,
which is where I work. Could it be that your clients are all sitting within 5 feet of
your server, and there is precious little other network activity that is occurring at the
same time you have run your tests?

I will agree that Access does not retrieve all of the records as long as no criteria have
been included in a query. In that case, you are correct in stating that:
"Fact is, JET does not load all records. JET does not ALWAYS
read all records....".

Jet retrieves a page of records (4096 KB, if I recall correctly) if one is simply opening
up a bound form using a standard query to a .MDB file on a file server. As soon as you
add any criteria, I maintain that the entire recordset is transferred down the wire and
the filtering takes place on the front-end client.

I'll supply additional references later this week. Please start researching your own.


Tom

 

[Pat Garard]

Good Morning All,

It is 5am, and a beautiful Dawn Heralds the newborn Day.

My Inbox is soiled with Blood, Sweat, Saliva, Tears, Vitreous
& Aqueous Humours and broken Indices.

The subject for todays debate is this:

How does one select a Record, based on a criterion
(EmpSurname="Jones") without examining the data
(EmpSurname)?

There is no point in Indexing ALL the columns, and
yet arbitrary criteria are usually more complex than
above.

Regards,

Pat

Anne & Pat Garard.
apgarardATbigpondDOTnetDOTau
_______________________________________________


-----Original Message-----
From: Tom Wickerath [mailto:[email protected]]
Sent: Tuesday, 2 December 2003 5:37 AM
To: (e-mail address removed)
Subject: Fw: Friendly wager?


FYI - Reply from Albert Kallal - in case you are interested.....


----- Original Message -----
From: Albert D. Kallal
To: Tom Wickerath
Sent: Monday, December 01, 2003 12:56 AM
Subject: Re: Friendly wager?


Actually, Tom, the great thing so far is that we both very civilised
people. I am very happy that you simply want to "prove" you point of view,
and we have completely avoided any name calling etc (that is for dumb kids
anyway). I certainly appreciate your composure in the thread, and I
appreciate your gentleman approach. This how learning works. Thank for being
such a gentleman.

And, I yes, I am sure of this issue.

I don't have any need at all for some type of wager. The way JET works (or
just about any pc based system is the same). Hence, FoxPro, or dbase etc
(virtually ALL OF THE mainstream PC database systems are able to sue a index
to only retrieve the records required). Full table scans are NOT required at
all. Some of the index is retrieved, and that allows JET to grab the
required record. Also, of course, more then one record is often retrieved,
but a full table scan is NOT required (the reason why more then one record
is retrieved is that JET is page based, and further, jet records are
variable length. (this means that blanks are NOT stored on the hard disk.
Thus, no more disk spaced is taken if you define a field with 10 characters,
or a field with 200 characters (if they are both empty, then the blanks do
not take up disk space).

I can assure you that all records are not loaded, or transferred. However,
there is a heck of a lot more overhead, and data transferred then when using
JET as compared to a true client/server. In fact, if your network is wan
(not a lan), then you can't even really use JET anyway.

It has been a number of years since I have written my own indexing
routines, but going all the way back to my Computing Science days at
University, none of this stuff is new nor is a surprise to me. If I recall,
I learned this stuff in the 1st year of CS.

This issue applies to JET, and just about all any ISAM database.

If a index can be used, then a full table scan is not required at all.

I have no interest in making a bet on something that is widely known by
most ms-access developers.

However, I will publicly acknowledge my mistake, and re-cant anything I
have said if you can find some credible evidence to the contrary of what I
know to be true.

And, yes, you will OFTEN find JET slow over a network. In fact, you can
read the following article of mine on using JET and a network.

http://www.attcanada.net/~kallal.msn/Wan/Wans.html


Albert D. Kallal (Microsoft Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn


----- Original Message -----
From: Tom Wickerath
To: (e-mail address removed)
Sent: Monday, December 01, 2003 12:52 AM
Subject: Friendly wager?


Albert,

Tell you what.....since you are so sure of yourself, are you willing to
place a friendly wager? I am willing to put my money where my mouth is: I
will wager you, up to $200 USD max., that you are wrong. If you accept, the
loser will make a contribution in an amount that is agreed to in advance, to
a 501(c)(3) non-profit charity of the winner's choosing. Of course, we'd
have to also agree in advance as to who would be the final authority on this
matter. I believe I can get someone from the Microsoft Access Team to be
the arbitrator and sole determination of who is correct and who gets to
donate some of their money.

Tom

 

[Tony Toews]

Dear, dear, dear - how can this be????
Such incredible ructions!!!!

Not at all. Polite disagreements. Not a problem.

Let's try to agree, as Gentlemen, on two simple issues:

1. Whether an Access Back End is on the local PC
OR
on a Server, it is JUST ANOTHER NETWORK file!!!

Whatever work Jet does is the SAME!!!!
Correct.

ALL the SELECTION of records takes place
where the Front End is - regardless of how this
selection takes place, or what volume of data is read.
Correct.

The ONLY advantage of having it on the Server
is that it can be shared.
Correct.

2. A relationl Database Server (Oracle, ..., SQL Server,
MSDE, ...) carries out intelligent work on the SERVER.

Access itself CHANGES according to whether the Back
End is Access OR an SQL Server.

With an SQL Server, Access uses Stored Procedures
that exist, and are executed, on the SERVER - pointers
to the selected Records are the ONLY results returned,
in the first instance, OVER THE NETWORK.

Hence the reduction of network traffic.

Certainly SQL Server and it's brethren will give you a reduction in
network traffic.

Please focus first on Principles!

In practise, the business needs and the budget determine the
(sometimes disastrous) outcome!!

Agreed.

However this still does not mean that Access only pulls down what
portions of the index and pages of the MDB which it needs whenever
possible. Whenever possible meaning if the sequencing or selection
criteria are in indexed fields. If not then sure it has to pull down
the entire table.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Pat Garard]

Thank you!
You make my point with Clarity, Authority and Economy.
--
Regards,
Pat Garard
Australia

Anne & Pat Garard.
apgarardATbigpondDOTnetDOTau
_______________________________________________

 

[John Viescas]

Tom-

I believe I said that Access is NOT a database - in the classic sense of the
term. It's a very smart inverted list file manager that looks very much
like a relational database on a client machine. If you want a true
relational database server, you need something like SQL Server or Oracle.

That doesn't mean that a smart developer cannot use Access as the underlying
relational data store for a network application. For a small number of
users of a well-designed application, Access is an incredibly inexpensive
alternative to a true relational database system. Although JET always runs
on the client machine, it's pretty efficient fetching only the physical file
blocks it needs to solve a problem - sometimes returning only index blocks
rather than the full data row.

The most extreme successful case I witnessed recently was a customer support
application with more than 2 gig of data spread across several mdb files.
The front end uses unbound forms and very specific SQL to provide excellent
response time to more than 200 simultaneous users. I didn't think it
possible, but my son proved me wrong. When I asked him why the app isn't on
SQL Server, he replied that the IT department (he has been just a cog on the
user side of the wheel) wouldn't give him the permissions he needed to set
it up. Some of the IT execs saw his implementation - and offered him a big
promotion into the IT department. He's in the process of rewriting it for
SQL Server for world-wide deployment. (Hint: He works on contract for a
major printer manufacturer.)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

 

[John Viescas]

By "one" do you mean Access? If you're an intelligent Access developer, you
define indexes on the fields searched by your application. If you offer a
search QBF on EmpSurname, then you're stupid if you don't index it. When an
index is available, JET probes that index first and then uses any found
entries to retrieve the data blocks. With the incorporation of Rushmore as
of Access 2, JET can also intersect criteria on multiple indexed fields to
narrow down the actual data blocks fetched. Even when some criteria are on
fields not indexed, JET first uses any indexed values to narrow the search.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

 

[david epsom dot com dot au]

I'm not sure that other people will realise what
you mean when you say that something is 'just
another network file'

Just to expand on that part of the message: the
file system supports record access and record locking
semantics as well as file access and file locking
semantics.

And Jet uses the record handling semantics to lock
and retrieve parts of the files.

Now a File Server is a Server just like a Mail Server
or a SQL Server, and all the File Server operations
are done at the Server.

So when Jet wants a part of the file, say 'A Table'
or 'An Index' or 'A Record', it doesn't bring down
the whole file across the network: it just issues
a request for a particular part of the file, and the
Server sends that piece of data back.

In general, Jet will first request an index, and
use that index to select which parts of the file
(records) to request.

(david)

 

[david epsom dot com dot au]

If you use www.google.com to search Microsoft.public.access.*
for JetShowPlan, you will find instructions for how to use
this unsupported feature to show the query plan for simple
queries.

The query plan will show you which indexes and methods are
used for selecting the records for simple queries.
When the plan shows 'SCAN', it means Jet has requested the
entire table, and is scanning the field for matching data.


If you are slightly more sophisticated, you can use ODBC
trace tools to see the kinds of requests JET will put to
a SQL server when using linked tables.

If you are an Uber Geek, you can use a Network Sniffer
to watch the data requested from a Network Jet Database.

(david)

 

[Pat Garard]

Then I guess I'm not an 'Intelligent' applications developer!!

I OFFER (or I did before I retired) a service to the BUSINESS.
If the BUSINESS APPLICATION required various QUERIES
or VIEWS (depending upon the engine), then ONE of course
implemented them.

(By Application I mean the result of an agreed Requirements
Statement - based on the Business Plan and the IT Strategic
Plan).

If MANAGEMENT are to receive access to the DB, then I
offer WHAT I AM PAID TO OFFER!!!!!!!!

We have had Front End Tools for 10 years or more, that enable
Ad Hoc Queries for Managers.

(Try telling an HR manager that 'With the incorporation of
Rushmore as of Access 2, JET can also intersect criteria on
multiple indexed fields to narrow down the actual data blocks
fetched.'!!!!)

Access is a tool for doing BUSINESS!! If you choose to live in
Developers Heaven - So Be It!!! (Check your Bank Account)!!

GROUNDING:
Please check also, the context of the Original Questioner - I hope
he appreciates that he has had (for free) an excellent primer on
business applications development.

I hope also that he appreciates that TECHNICAL BULLSHIT is
no substitute for basic $Value in applications development!

I think I like being Stupid!!!!!!!!!!!!!!!!!!!!
(As well as technically competent)!!
--
Regards,
Pat Garard
Australia

Anne & Pat Garard.
apgarardATbigpondDOTnetDOTau
_______________________________________________

 

[John Viescas]

Tom-

I responded to your "relational" assertion yesterday. After spending some
time running some performance tests this morning, I have some numbers that I
trust will convince you that Access (JET) fetches only the minimum it needs
over the network to get the job done. You can easily reproduce this test
yourself.

Database: 1.8 million rows, name/address information. MDB file size: 420
meg. No index on City.

Problem: Find all people who live in "Baytown" - approximately 37,000 of
the rows.

Methodology: Shut down all applications that might use the network. Open
Windows XP network status window on the LAN connection. Start Access, open
the database, and open a simple query in Design View: SELECT * FROM
tblPeople WHERE City = "BayTown"

Write down the "Bytes Received" in the network status window. Switch back
to Access, switch to Datasheet view in the query, and click the Go To Last
Record button on the navigation bar.

With no index, it took approximately 55 seconds to find the last row, and
the LAN sent 14 million bytes to my machine. Note that this isn't the
entire file even though JET clearly has to do a table scan to solve the
query. Because I displayed only the first and last "page" of the Datasheet
(a total of about 100 rows), Access is doing a full fetch on only the rows
it needs to display.

Next, define a Duplicates OK index on the City field. Close the database
and close Access to flush all buffers (you can reboot if you like). Restart
Access, open the database, and open the query in Design View. Note Bytes
Received in the network status window, switch back to Access, switch to
Datasheet view, and immediately click go to last.

Result: Less than 2 seconds and only 69,000 bytes transferred. Although
there are 37,000 matching rows, again JET is transferring only the blocks
needed to satisfy my display request of the first and last "pages" of the
datasheet.

Run the same test on SQL Server, and you get about 100,000 bytes transferred
in both cases, but the query without the index takes nearly as long to solve
as JET. The only difference is all the searching is being done on the
server.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411