Limits of Access
Greg Olsen
another subject, can someone tell me the limits of access with regards to
users on a network and file sizes , i am in an organization that has no real
database system and i am on the path to designing one. In the interests of
costs i am leaning towards Access, but i do realize as it grows i am sure
access has its limits.
It is intended that the data will be accessible over a web application in
the future. if some can give input i would greatly appreciate or
alternatively some web sites that address this matter
Regards
Greg
jojo
Microsoft Access database (.mdb) file size 2 gigabytes. However, because
your database can include linked tables in other files, its total size is
limited only by available storage capacity.
Number of objects in a database 32,768
Modules (including forms and reports with the HasModule property set to
True) 1,000
Number of characters in an object name 64
Number of characters in a password 14
Number of characters in a user name or group name 20
Number of concurrent users 255
"Greg Olsen" <greg...@bigpond.com> wrote in message
news:MTmW9.27614$jM5....@newsfeeds.bigpond.com...
Carl Smoot
across a network and lets the clients do the processing. A true DBMS will
only send the requested data cutting down considerably on network traffic.
--
Carl Smoot
Berea, KY
mrin...@vol.com (preferred)
mrin...@email.uophx.edu
(859) 985-8406
Eastern Standard Time
"Greg Olsen" <greg...@bigpond.com> wrote in message
news:MTmW9.27614$jM5....@newsfeeds.bigpond.com...
Rick Brandt
the entire DB
across a network. . ." is not accurate. It sends the same i/o across the
network as would occur if the file were being accessed on a local disk.
This would never be the "entire DB" and with proper indexing would seldom
even be an entire table.
"Carl Smoot" <mrin...@vol.com> wrote in message
news:v3oW9.226$Sv3....@newsread1.prod.itd.earthlink.net...
John Mishefske
> You're overall caution is not incorrect, but the statement ". . .it sends
> the entire DB
> across a network. . ." is not accurate. It sends the same i/o across the
> network as would occur if the file were being accessed on a local disk.
> This would never be the "entire DB" and with proper indexing would seldom
> even be an entire table.
Hey Rick. I believe that's true and MichKa's said that in a previous post here.
(1/15/2000 - search Google Groups on "michka file index wire").
I'm wondering if you (or others) have found anything I could cite in the MS docs
or somewhere else.
I've gone through the MS site and the JDEPG looking for anything I can find
to get a more definitive explanation. I thought I might try doing some
experiments using SysInternal's FileMon or some other mechanism.
Anyone have additional insights?
--
'-------------------------------
' John Mishefske
'-------------------------------
Albert D. Kallal
Rick's comments about the same i/o occurring when NO network is involved is
correct.
The fact of a network MAKES ZERO DIFFERENCE as to what actually gets loaded
into memory. In fact, whatever gets sent down the wire does get loaded into
memory!
When you load a excel file, the whole spreadsheet is loaded into memory.
Note that the process is EXACTLY the same regardless if a network is
involved or not!. If the whole spreadsheet loads into memory, the whole
spread sheet is read from disk. Now, if a network is placed between you and
the file, then everything still works EXACTLY the same. That means the WHOLE
SPREADSHEET will be transferred down the wire.
Ok, now in the case of ms-access, the whole mdb file is NOT loaded into
memory. Again, the exact same concept applies if we place network between
the file. If ms-access on a local PC with no network does not load the whole
file into memory, then the fact of placing a network between you and the
access file thus again does NOT force, or cause the whole file to be sent
down the wire.
If ms-access DID always load the whole file into memory, then yes, every
time you used ms-access the whole thing would load form disk right into your
memory. However, it does not do that. Also, if it did load the whole file,
then why would it take additional time to read any additional records? I
mean, if the whole file is sent down the wire, then at point it would be
super fast!
Simply put, there is zero debate, zero argument here. ms-access does NOT
load the whole table/file into memory.
Since ms-access does not load the whole file into memory, then it does not
transfer the whole file down the wire.
How can one possibility explain that when you have a small table with 5,
500, 5000 or 50,000 or even 500,000 records that the time to grab one record
is about the same time?
In fact, with small files of 50,000 records, 5 users on a typical office
network can retrieve one record in a instant. That setup would be impossible
if the whole 200 meg file was sent down the wire?
Further, if the whole file had been transferred after we read just one
record, then as mentioned additional record reads would be instant would
they not?
If you do retrieve a record by some search criteria, and a index cannot be
used, then yes.the whole table will come down the wire. But who the heck
does a search on a table by some id or field that does not have an index?
There are most certainly operations what will cause too many records to come
down the wire, but it is complete myth that JET normally drags down the
whole table. It does not.
Albert D. Kallal
Edmonton, Alberta Canada
kal...@msn.com
John Mishefske
> It most certainly does NOT load the whole file.
>
> load the whole table/file into memory.
>
> Since ms-access does not load the whole file into memory, then it does not
> transfer the whole file down the wire.
>
> How can one possibility explain that when you have a small table with 5,
> 500, 5000 or 50,000 or even 500,000 records that the time to grab one record
> is about the same time?
>
> In fact, with small files of 50,000 records, 5 users on a typical office
> network can retrieve one record in a instant. That setup would be impossible
> if the whole 200 meg file was sent down the wire?
>
> Further, if the whole file had been transferred after we read just one
> record, then as mentioned additional record reads would be instant would
> they not?
>
> If you do retrieve a record by some search criteria, and a index cannot be
> used, then yes.the whole table will come down the wire. But who the heck
> does a search on a table by some id or field that does not have an index?
> There are most certainly operations what will cause too many records to come
> down the wire, but it is complete myth that JET normally drags down the
> whole table. It does not.
Agreed. I was just looking for more details on the process.
Carl Smoot
is required to do the processing in Access client/server systems and on a
SQL/Oracle system (true DBMS) the processing is done on the server. And my
experience with Access in an office environment was that as the DB got
larger it took longer and longer for the dang thing to work.
--
Carl Smoot
"Carl Smoot" <mrin...@vol.com> wrote in message
news:v3oW9.226$Sv3....@newsread1.prod.itd.earthlink.net...
> Be aware that Access is not a true DBMS in that it sends the entire DB
> across a network and lets the clients do the processing. A true DBMS will
> only send the requested data cutting down considerably on network traffic.
>
> --
> Carl Smoot
Albert Marshall
Smoot <mrin...@vol.com> writes
>Be aware that Access is not a true DBMS in that it sends the entire DB
>across a network and lets the clients do the processing.
That is unadulterated garbage.
A properly written Access database only moves the records it needs
across the network.
About a year ago I wrote a small data mart in Access 97. The tables were
imported daily from Oracle and de-normalised for reporting efficiency.
Using properly indexed tables the users could open any record in a
fraction of a second although there were hundreds of thousands of
records in the tables.
That would have been impossible if all the data was being transferred to
the local machine.
I could cite other applications with read-write access that have the
same sort of performance.
>A true DBMS will
>only send the requested data cutting down considerably on network traffic.
>
>--
>Carl Smoot
>Berea, KY
>mrin...@vol.com (preferred)
>mrin...@email.uophx.edu
>(859) 985-8406
>Eastern Standard Time
>"Greg Olsen" <greg...@bigpond.com> wrote in message
>news:MTmW9.27614$jM5....@newsfeeds.bigpond.com...
>> Hi again
>> another subject, can someone tell me the limits of access with regards to
>> users on a network and file sizes , i am in an organization that has no
>real
>> database system and i am on the path to designing one. In the interests of
>> costs i am leaning towards Access, but i do realize as it grows i am sure
>> access has its limits.
>>
>> It is intended that the data will be accessible over a web application in
>> the future. if some can give input i would greatly appreciate or
>> alternatively some web sites that address this matter
>>
>> Regards
>> Greg
>>
>>
>>
>
>
--
Albert Marshall
Database Developer
Marshall Le Botmel Ltd
01242 222017
Lyle Fairfield
news:Nx9lvTOiboK+Ewz$@stowdata.demon.co.uk:
>>Be aware that Access is not a true DBMS in that it sends the entire DB
>>across a network and lets the clients do the processing.
> A properly written Access database only moves the records it needs
> across the network.
JET? ... Please, expand? Which records does it need? Suppose we have a
network as simple as two machines connected through a router, the kind
typically used to share a cable internet connection. JET is fully
operational on both machines. On Machine B we have an MDB and in the MDB is
a single table and a single stored query.
The query's (the query is in the MDB on Machine B) SQL is:
SELECT some records FROM the table in the MDB on Machine B WHERE some
condition is True.
From Machine A we open Access and through Access open the MDB on Machine B
and run the query (the query is in the MDB on Machine B) as the source for
a record set (the record set to be living in the Access app on Machine A).
1. Is the processing for the query done on Machine A or Machine B?
2. How many records get sent to Machine A, all the records in the Table, or
just those meeting the criterion?
Well, I thought I knew the answer for this ... but if I'm wrong I'd like to
be enlightened.
Lyle
Tester
> I bow to more experienced users. But I cannot help wondering why the
client
> is required to do the processing in Access client/server systems and on a
> SQL/Oracle system (true DBMS) the processing is done on the server. And my
> experience with Access in an office environment was that as the DB got
> larger it took longer and longer for the dang thing to work.
>
I too am an inexperienced user of Access and find this discussion
interesting. But I am not right in thinking that there is the possibility to
split an access DB into the forms part (and all the other objects) and the
tables part. Therefore even further reducing the amount of information that
is transferred over the network?
Regards,
Mike MacSween
"Lyle Fairfield" <lyle...@yahoo.com> wrote in message
news:Xns93084FEAC6FCD...@216.221.81.119...
> Albert Marshall <albert....@stowdata.co.uk> wrote in
> news:Nx9lvTOiboK+Ewz$@stowdata.demon.co.uk:
>
> >>Be aware that Access is not a true DBMS in that it sends the entire DB
> >>across a network and lets the clients do the processing.
>
> > A properly written Access database only moves the records it needs
> > across the network.
>
> JET? ... Please, expand? Which records does it need? Suppose we have a
> network as simple as two machines connected through a router, the kind
> typically used to share a cable internet connection. JET is fully
> operational on both machines. On Machine B we have an MDB and in the MDB
is
> a single table and a single stored query.
>
> The query's (the query is in the MDB on Machine B) SQL is:
>
> SELECT some records FROM the table in the MDB on Machine B WHERE some
> condition is True.
>
> From Machine A we open Access and through Access open the MDB on Machine B
> and run the query (the query is in the MDB on Machine B) as the source for
> a record set (the record set to be living in the Access app on Machine A).
>
> 1. Is the processing for the query done on Machine A or Machine B?
I would have thought Machine A. Because this would work even if msaccess.exe
wasn't installed on Machine B, wouldn't it? Or installed but not running.
The servers where my back end data file live don't usually have Access
installed.
> 2. How many records get sent to Machine A, all the records in the Table,
or
> just those meeting the criterion?
Is it actually records? I mean is it not, some bytes which when received by
msaccess.exe can be assemble into records, but which also contain
information which isn't records. Like the location of the data file, locks
in place, user and group permissions maybe. Maybe seems like a funny way to
express things, but a more accurate way to think of what actual binary data
gets moved.
Yours, Mike MacSween
Mike MacSween
> I bow to more experienced users. But I cannot help wondering why the
client
> is required to do the processing in Access client/server systems and on a
> SQL/Oracle system (true DBMS) the processing is done on the server.
Because an 'Access client/server system' (assuming you mean a Jet backend)
is an oxymoron. Doesn't exist. Is meaningless. Jet isn't a server DBMS. How
can any processing be getting done on the server machine if Access isn't
even installed there? Which it often isn't, in my systems.
That's the difference:
Access front end, Jet back end = file server system
VB front end, SQL Server back end (for instance) = client server system
Yours, Mike MacSween
Trevor Best
<3E2A2844...@execpc.com>, John Mishefske <mis...@execpc.com>
wrote:
While not sending the whole file it can send parts of it multiple
times, I was doing a "Quick Find" combo box for a supplier form, the
user would type the first couple of characters and restrict the rows
returned in the combo. While testing I used a supplier database of
87,000+ suppliers, the back-end MDB was 18MB, the rowsource would be
something like:
cboQuickFind.Rowsource = "Select SupplierID, ShortName From
tblSupplier Where ShortName Like '" & txtQuickFind & "*' Order By
ShortName"
(ShortName has a unique index on)
At the time I had a Novell Server and used it to monitor how much was
read from the server, with no criteria (e.g. ShortName Like '*') it
read 350MB from the server in order to populate the combo box, this
was considerably more than the 18MB size of the entire MDB.
Of course this was an extreme case and back in Access 2.0 (Jet 2.0) so
it may be a little more efficient now.
--
People say I'm old fasioned; That's poppycock.
(replace sithlord with trevor for email)
Trevor Best
This is normal practice for most developers.
Trevor Best
<Xns93084FEAC6FCD...@216.221.81.119>, Lyle Fairfield
<lyle...@yahoo.com> wrote:
>Albert Marshall <albert....@stowdata.co.uk> wrote in
>news:Nx9lvTOiboK+Ewz$@stowdata.demon.co.uk:
>
>>>Be aware that Access is not a true DBMS in that it sends the entire DB
>>>across a network and lets the clients do the processing.
>
>> A properly written Access database only moves the records it needs
>> across the network.
>
>JET? ... Please, expand? Which records does it need? Suppose we have a
>network as simple as two machines connected through a router, the kind
>typically used to share a cable internet connection. JET is fully
>operational on both machines. On Machine B we have an MDB and in the MDB is
>a single table and a single stored query.
>
>The query's (the query is in the MDB on Machine B) SQL is:
>
>SELECT some records FROM the table in the MDB on Machine B WHERE some
>condition is True.
>
>From Machine A we open Access and through Access open the MDB on Machine B
>and run the query (the query is in the MDB on Machine B) as the source for
>a record set (the record set to be living in the Access app on Machine A).
>
>1. Is the processing for the query done on Machine A or Machine B?
A
>2. How many records get sent to Machine A, all the records in the Table, or
>just those meeting the criterion?
Depends, Machine A may use an index on machine B, in this case parts
of the index will be send back to A, A can then just read the records
it needs.
If the table was unindexed or A was unable to use any index (e.g.
using "like" with a ? or * at the start would bypass an index or if
the query criteria contained a function that used part of the data as
a parameter) then the whole table will come down.
Trevor Best
<GdwW9.679$Sv3....@newsread1.prod.itd.earthlink.net>, "Carl Smoot"
<mrin...@vol.com> wrote:
>I bow to more experienced users. But I cannot help wondering why the client
>is required to do the processing in Access client/server systems and on a
>SQL/Oracle system (true DBMS) the processing is done on the server. And my
>experience with Access in an office environment was that as the DB got
>larger it took longer and longer for the dang thing to work.
Simply put, Jet is what's known as a desktop database, the client does
the processing because that's the way in which it was designed to
work.
Jet is a true RDBMS, just not "Client/Server".
The reason that things like this exist (that process on the client
side) is simply cost and maintenance, Jet is cheap, easy to implement
and maintain. Client/Server systems are expensive, take a bit more to
set up the server (Jet only requires you copy the file to a share).
Michael (michka) Kaplan
> JET? ... Please, expand? Which records does it need?
The answer is that if the table is properly indexed for the column used for the selection then the
index is the only thing that goes across the wire for processing. The only full records that go
across are the selected ones.
When I doubt, "show plan" is a great way to understand how Jet is processing a query. Look out for
the second most evil set of words in relation to a Jet application --USE TABLE SCAN <grin> -- this
means that the entire table is going across the wire.
--
MichKa
This posting is provided "AS IS" with
no warranties, and confers no rights.
Trevor Best
<3e2ad3e2$1...@news.microsoft.com>, "Michael \(michka\) Kaplan"
<mic...@spamless.trigeminal.nospamcom> wrote:
>When I doubt, "show plan" is a great way to understand how Jet is processing a query. Look out for
>the second most evil set of words in relation to a Jet application --USE TABLE SCAN <grin> -- this
>means that the entire table is going across the wire.
Where is "show plan"?
Dimitri Furman
<mic...@spamless.trigeminal.nospamcom> wrote in
news:3e2ad3e2$1...@news.microsoft.com:
> When I doubt, "show plan" is a great way to understand how Jet is
> processing a query. Look out for the second most evil set of words in
> relation to a Jet application --USE TABLE SCAN <grin>
Ok, so what's the first most evil set of words? I can think of a few
candidates, but that kind of thing can be somewhat subjective...
--
(remove a 9 to reply by email)
John Mishefske
> On Sun, 19 Jan 2003 08:35:45 -0800, in message
> <3e2ad3e2$1...@news.microsoft.com>, "Michael \(michka\) Kaplan"
> <mic...@spamless.trigeminal.nospamcom> wrote:
>
>
>>When I doubt, "show plan" is a great way to understand how Jet is processing a query. Look out for
>>the second most evil set of words in relation to a Jet application --USE TABLE SCAN <grin> -- this
>>means that the entire table is going across the wire.
>
>
> Where is "show plan"?
>
Registry key:
\\HKLM\SOFTWARE\MICROSOFT\Jet\3.5\Engines\Debug
Create a string data type called JETSHOWPLAN and set to ON or OFF
Appends text to a Showplan.out file when a query is compiled to explain the plan Jet will use.
Lyle Fairfield
news:3e2ad3e2$1...@news.microsoft.com:
> "Lyle Fairfield" <lyle...@yahoo.com> wrote...
>
>> JET? ... Please, expand? Which records does it need?
>
> The answer is that if the table is properly indexed for the column used
for the selection then the
> index is the only thing that goes across the wire for processing. The
only full records that go
> across are the selected ones.
>
> When I doubt, "show plan" is a great way to understand how Jet is
processing a query. Look out for
> the second most evil set of words in relation to a Jet application --USE
TABLE SCAN <grin> -- this
> means that the entire table is going across the wire.
Well, question 2 ... suppose there is another query, an update query ...
UPDATE Table SET Field1 = Field2
and again we somehow or other get that query which exists on Machine B to
run from our Access app on Machine A.
If we have the Task Manager running on both machines, showing the
perfomance window, which machine will show 100% CPU usage while the query
runs?
--
Lyle
Michael (michka) Kaplan
It is considered a MUST FIX bug if data loss is possible, and always has been.
--
MichKa
This posting is provided "AS IS" with
no warranties, and confers no rights.
"Dimitri Furman" <dfu...@cloud99.net> wrote in message
news:Xns93087B68D634...@199.184.165.241...
Michael (michka) Kaplan
> Well, question 2 ... suppose there is another query, an update query ...
>
> UPDATE Table SET Field1 = Field2
>
> and again we somehow or other get that query which exists on Machine B to
> run from our Access app on Machine A.
>
> If we have the Task Manager running on both machines, showing the
> perfomance window, which machine will show 100% CPU usage while the query
> runs?
There in no process running on machine B, other than the network. Think of Machine B as a big hard
drive, on the end of a network cable.
Jim Allensworth
<mic...@spamless.trigeminal.nospamcom> wrote:
>
>There in no process running on machine B, other than the network. Think of Machine B as a big hard
>drive, on the end of a network cable.
>
Right, I discovered that Acces doesn't even need to be installed on
Machine B.
- Jim
David W. Fenton
<v3oW9.226$Sv3....@newsread1.prod.itd.earthlink.net>:
>Be aware that Access is not a true DBMS in that it sends the
>entire DB across a network and lets the clients do the processing.
>A true DBMS will only send the requested data cutting down
>considerably on network traffic.
I wish people would check their facts before posting things like
this.
It is true that with Jet, all processing is done on the client
workstation. But Jet was designed from the ground up to send the
minimum amount of data necessary across the wire.
To understand why Jet (the database engine that is native to
Access) does not need to retrieve the whole MDB across the network
before you can start work, you have to understand something about
the internal structure of an MDB file. Jet data files are organized
into logical "pages" of 2KB (4KB for Jet 4 because of Unicode; I
don't know if turning Unicode compression off reduces the page size
to 2KB). Those pages constitute logical blocks that are treated as
the smallest building blocks of the data structures for data
retrieval and locking within a Jet data file. Many records can be
stored in one data page (though a record cannot span more than one
page). This is why when editing one record you can be locked out of
editing another record, because the non-edited is in the same data
page as the edited record.
Jet handles all file operations internally with these pages.
Now, the other issue is indexing. If a table has a primary key and
you request data by that primary key, the first thing Jet requests
is the pages of the file containing the index. I do not know for
certain if Jet requests a few pages at a time and stops requesting
other pages if it finds the match; for small indexes, there'd be
little point, but for large ones of 100s of thousands of records,
that could be very efficient. And for unique indexes, there'd
certainly be no reason to retrieve more index pages once the match
is found, because there can be no further matches. So, I suspect
that Jet will not retrieve any more index pages than it needs to
process (though I would wager it has a look-ahead cache of the next
pages to be examined). Assuming B-tree indexes, the number of index
pages needed should be less than half the index.
Now, what happens once the index entry is found?
Then the workstation requests the specific data page (or pages, in
the case of non-unique matches where the records needed take up
more than one page of data) where the requested record(s) are
stored within the file.
So, with indexes, the amount of data retrieved across the wire is
*very small* as a percentage of the total MDB file.
Now, a few points become obvious from this:
1. the better organized the internal pages of the MDB file are, the
more efficient the disk reading on the server will be. Compacting
rewrites indexes and data tables in contiguous data pages, so
regularly compacting your database will keep the internal pages
defragmented and lead to marginally faster data retrieval times
(since the disk head can do one zip through the relevant clusters
on the disk and won't have to jump around to lots of different
clusters to read the data). This is the same exact principle as is
in operation when you defragment your hard drive to improve
performance.
2. because an index page is going to contain data related to a lot
more records than a data page (since the data for each record is
very small), locking an index page is going to lock far more
records than locking a data page. Therefore, it's best to try to
minimize the number of indexes in a table so that you aren't
constantly having lock collisions. If you use pessimistic locking,
you're going to have lots more problems the more indexes you have.
This is also one of the reasons why optimistic locking is usually
the best choice with Jet data files, because then the lock only
matters exactly at the point you try to write to the data page
(index or table).
3. depending on the type of application, AutoNumber PK indexes (the
pages of which are written in order) may be more efficient with a
random AutoNumber in place of an incrementing number. Why? Because
editing records whose PK entries are randomly distributed through
the index pages will tend to lock more *different* pages. You'd
have to consider what type of application, and the balance between
adding records and editing records, and what kind of data retrieval
is being done. In an application where records are added a lot but
hardly ever edited after creation, the random index will not help
much, since the contention for the data page will be during the
adding stage, when the newest records will always be in a new index
data page (the index pages only get written in PK order during a
compact). And if your app's users tend to work with only the recent
data, there may be some advantage in data retrieval time in having
a non-random index because if users tend to use the more recent
data all the time, those index data pages could be cached, whereas
a random index would be evenly distributed through all the data
pages.
In any event, the main point here is that it's only data pages that
are retrieved, and the only times an entire table is retrieved and
scanned are:
1. when there is no index on a field being searched
OR
2. you are using a LIKE *[pattern]*
I'm not entirely certain if LIKE [pattern]* uses an index or not,
but it would seem to me from experience that searching within a
field is slower than searching the beginning of a field. I don't
know for sure whether or not Jet uses the index for this or scans
the table. It would seem to make more sense to retrieve the index
and do a full scan of it rather than the whole table, but I seem to
remember somebody (Albert?) having done a test on this and having
determined that the index is not used with the LIKE *[pattern]*
search.
There are a number of other issues around indexing and
normalization that have a big effect on the efficiency of data
retrieval with Jet, but I've already said far too much. The main
thing is that you should never bind a large table to a form unless
you have a very compelling reason to do so, since that will mean
the retrieval of much more data than your user is going to be able
to use at one time. I have found databases that allow the retrieval
of 1 to 250 or so records at a time to be very efficient, even with
100s of thousands of records in the source data table.
In short, Jet is very, very good at minimizing data pulled across
the wire *if* you in turn are careful not to ask it to do things
that can't be done efficiently.
Another note: criteria on expressions *must* be processed on the
workstation, since Jet does not match to partial indexed fields
(xBase can actually do that). For instance, if you are looking for
all the records in 1992, this:
WHERE Year(tblInvoice.InvoiceDat) = 1992
is going to be far less efficient than:
WHERE tblInvoice.InvoiceDate BETWEEN #1/1/1992# AND #12/31/1992#
because the latter can use the index on InvoiceDate and retrieve
the index pages first, whereas the former will have to retrieve the
whole table, run the values in the column through the Year()
function and then match *that*.
On the other hand, if your year query is combined with other
limiting criteria on an indexed field, the Jet query optimizer is
smart enough to apply the indexed filter first.
WHERE tblInvoice.InvoiceType = 2 AND
Year(tblInvoice.InvoiceDat) = 1992
may very well be quite efficient (assuming InvoiceType is indexed),
because the Jet query optimizer will first retrieve the InvoiceType
index and then scan only the records with InvoiceType 2, which
could very well be a very small number of records. However, using
the BETWEEN operator with the other criteria would be still more
efficient.
The point is that if you don't have any choice of filtering on an
expression, then it's not such a horrible thing *if* there's
another limiting field that *is* indexed.
OK, I'll stop there!
Please, anyone who sees errors or mis-statements -- be sure to
correct them!
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
David W. Fenton
<p5hl2vg7i2fq8s1cs...@4ax.com>:
LIKE operations are not as well optimized as others (and I believe
some LIKE matches skip the index entirely). I wonder what would
happen in your example if you replaced the LIKE with a BETWEEN?
David W. Fenton
>I bow to more experienced users. But I cannot help wondering why
>the client is required to do the processing in Access
>client/server systems and on a SQL/Oracle system (true DBMS) the
>processing is done on the server. . . .
Access is also a true DBMS, by the way, in fact, a true RDBMS. It
just isn't a database server. But, to answer your question:
Because with a SQL Server or Oracle system you are not
communicating with the file system on the other end, but with
another program running on the server whose entire purpose is to do
the retrieval for you in the server's memory and send the results.
That is, when you request invoices for 1992 from a server database,
the database server program running on the server, executing in the
memory and on the CPU of the server, pulls the relevant indexes and
data into the server's memory and uses the server's CPU to
determine which records match the request. Then it sends only those
matching records across the wire to the program on the workstation
that requested it.
With Access running against a Jet MDB file on a server, there is no
server process doing the heavy lifting for you in the server's
memory and on the server's CPU. Instead, the server is just a
remote hard drive to your workstation. In the example above, Access
would request the relevant index pages from the MDB file on the
server, which the server's file I/O subsytem would read from the
server's disk storage, and send the first few pages of that index
across the wire to your workstation. Your workstation, in its
memory and on its CPU, would process that index, then request from
the server's file I/O system the needed data pages where the
records matching your criteria are stored. Then the server's file
I/O system would read the records and send them across the wire.
In terms of data retrieved across the wire, Access/Jet is clearly
less efficient than Access/[database server], but that doesn't
necessarily mean that that the database server will always be
faster than the Jet back end. It all depends on a number of
factors, most involving the design of your application. Which leads
us to:
> . . . And my experience with Access in
>an office environment was that as the DB got larger it took longer
>and longer for the dang thing to work.
That shows that the application is very poorly designed. My guess
is that all forms are bound to tables (or to queries or SQL strings
that return all records), instead of being designed to only load
one or a small number of records when needed. Keep in mind that a
user cannot work with more than one record at a time, so what is
the point of loading 10,000 records, just in case they are needed?
Certainly all 10K will never be needed by any one user, so it makes
more sense to design your application to retrieve only the records
the user needs at one time.
Converting from Jet to a database server back end will not help you
if you're retrieving too many records, since you'll then have
exactly the same problem. The key is proper design of the
application to utilize indexes in order to minimize the amount of
data Access will need to populate your Access forms and reports.
David W. Fenton
<b0efip$sd4$1...@knossos.btinternet.com>:
Without question, the processing of the query is done on the
machine from which the query is sent. This is not in doubt. The
only thing that happens on the remote machine is the same file I/O
that would have happened on the requesting machine if the file had
been requested remotely. Obviously, there is some overhead for the
network protocol and all, so the amount of data that goes across
the wire is going to be somewhat larger than if requested locally,
but that is relatively minor, and far outweighed by the benefit of
being able to share the data when it's on a network volume.
>> 2. How many records get sent to Machine A, all the records in
>> the Table,
>or
>> just those meeting the criterion?
>
>Is it actually records? I mean is it not, some bytes which when
>received by msaccess.exe can be assemble into records, but which
>also contain information which isn't records. Like the location of
>the data file, locks in place, user and group permissions maybe.
>Maybe seems like a funny way to express things, but a more
>accurate way to think of what actual binary data gets moved.
Well, when you open the data MDB (i.e., request the first data from
a data table in it), Access on the workstation has to load the
relevant header information from the data MDB so that it can
intelligently request blocks of data from the data MDB. When a
request for records is made, Access will first request the data
pages for the indexes, figure out what it needs from those and then
request the data pages with the requested records. Obviously, since
the records requested may or may not be on contiguous data pages,
it may require the sending of several data pages. The ratio of sent
data to matching data will depend on how closely the stored order
of pages matches the logical order of the requested data set, and
how long the records are. That is, if the records are short, with
many to a data page, the efficiency will be lower in terms of
"discarded" retrieved data. But that's no reason to pad records to
fit one per page, because you'll still be retrieiving the same
gross amount of data.
David W. Fenton
<3E2ADE04...@execpc.com>:
>Trevor Best wrote:
>> On Sun, 19 Jan 2003 08:35:45 -0800, in message
>> <3e2ad3e2$1...@news.microsoft.com>, "Michael \(michka\) Kaplan"
>> <mic...@spamless.trigeminal.nospamcom> wrote:
>>>When I doubt, "show plan" is a great way to understand how Jet
>>>is processing a query. Look out for the second most evil set of
>>>words in relation to a Jet application --USE TABLE SCAN <grin>
>>>-- this means that the entire table is going across the wire.
>>
>> Where is "show plan"?
>
>Registry key:
>
> \\HKLM\SOFTWARE\MICROSOFT\Jet\3.5\Engines\Debug
>
>Create a string data type called JETSHOWPLAN and set to ON or OFF
>
>Appends text to a Showplan.out file when a query is compiled to
>explain the plan Jet will use.
Be careful with this: it slows down processing noticeably, a nd
also can make Access a little less stable. Because of that, I turn
it on only when I really need to troubleshoot a slow query.
Another problem with it is that the Jet developers never
implemented output of the plan for subqueries. I believe Jet does
optimization of many subqueries, but SHOWPLAN simply doesn't show
it.
(Pete Cresswell)
>It is intended that the data will be accessible over a web application in
>the future.
Intranet or "regular"?
I'd be hesitant to put up a JET back end in, say, New York, and have people in
Los Angles trying to use it.
-----------------------
Pete Cresswell
Larry Linson
circumstances/problems as "multiuser access" on a LAN.
"(Pete Cresswell)" <x@y.z> wrote in message
news:f0am2vsa9hu309kvd...@4ax.com...
Trevor Best
<930893192df...@24.168.128.78>, dXXXf...@bway.net (David W.
Fenton) wrote:
>LIKE operations are not as well optimized as others (and I believe
>some LIKE matches skip the index entirely). I wonder what would
>happen in your example if you replaced the LIKE with a BETWEEN?
I know in the example I gave, the index would not have been used as
the criteria began with a wildcard.
Trevor Best
<3e2af1fa...@netnews.attbi.com>, "Jim Allensworth" <n...@here.com>
wrote:
Of course not, you can have the back-end tables on a Novell Server if
you want, can't install Access on that.
(Pete Cresswell)
>Why, Pete? Accessing data via a website doesn't have the same
>circumstances/problems as "multiuser access" on a LAN.
Well, that just goes to show that some people think they know more than they
really do - and "some people" in this case is Yours Truly.
Now that I think about it, it's pretty obvious: the web server and the .MDB are
near and dear and all the server has to to is extract the data, format a page,
and dish it out to the user.
I was thinking in terms of a conventional user logon with front-end/back-end
.MDBs and ODBC connections...
-----------------------
Pete Cresswell
Albert Marshall
>Albert Marshall <albert....@stowdata.co.uk> wrote in
>news:Nx9lvTOiboK+Ewz$@stowdata.demon.co.uk:
>
>>>Be aware that Access is not a true DBMS in that it sends the entire DB
>>>across a network and lets the clients do the processing.
>
>> A properly written Access database only moves the records it needs
>> across the network.
>
>JET? ... Please, expand?
Other people already have, but just top show I'm awake, I'll expand
slightly.
>Which records does it need?
First it needs the appropriate index pages (assuming a properly indexed
structure and that the retrieve criteria don't use wild cards (there may
be other caveats, but they are the main ones.
Having identified the record(s) that match the criteria then it pulls
the data page(s) that contain the record(s) that are needed.
The efficiency of indexes compared to full table scans becomes glaringly
obvious. In the example I gave, with large, denormalised tables and an
autonumber PK, the ratio of record size to index size is of the order of
a hundred to one.
The app I was using as my example was set up with levels of drill-down
that took the user from department-level summaries down to individual
transactions and returned about a hundred records at a time for all but
the lowest level. There were usually a million or so records in the MDB
out on the LAN and each drill-down took less than a second. There is no
way that the network was moving about a quarter of a gig fast enough to
give that response time.
>Suppose we have a
>network as simple as two machines connected through a router, the kind
>typically used to share a cable internet connection. JET is fully
>operational on both machines.
Why?
> On Machine B we have an MDB and in the MDB is
>a single table and a single stored query.
>
>The query's (the query is in the MDB on Machine B) SQL is:
>
>SELECT some records FROM the table in the MDB on Machine B WHERE some
>condition is True.
>
>From Machine A we open Access and through Access open the MDB on Machine B
>and run the query (the query is in the MDB on Machine B) as the source for
>a record set (the record set to be living in the Access app on Machine A).
So you are using a front end on A that is running a query in a different
MDB on a different machine (B)?
How? I honestly don't know how to do that. It sounds too
"client-server-ish" to be possible in Access. I know I can do it with
views on SQL Server or Oracle, but on Access?
Or do you mean that you are opening the MDB on B as the front end? Which
means that the FE has to load into memory on A and all the processing
takes place there.
>
>1. Is the processing for the query done on Machine A or Machine B?
>
Probably. Or maybe not. See above
>2. How many records get sent to Machine A, all the records in the Table, or
>just those meeting the criterion?
However you do it, if the tables have effective indexes then only the
ones meeting the criterion will travel across the wire (plus a bit of
overhead if there are multiple records to a data page.
>
>Well, I thought I knew the answer for this ... but if I'm wrong I'd like to
>be enlightened.
>
>Lyle
>
--
Albert Marshall
Database Developer
Marshall Le Botmel Ltd
01242 222017
David W. Fenton
<ODGFT1FL...@stowdata.demon.co.uk>:
[Lyle:]
>> On Machine B we have an MDB and in the MDB is
>>a single table and a single stored query.
>>
>>The query's (the query is in the MDB on Machine B) SQL is:
>>
>>SELECT some records FROM the table in the MDB on Machine B WHERE
>>some condition is True.
>>
>>From Machine A we open Access and through Access open the MDB on
>>Machine B and run the query (the query is in the MDB on Machine
>>B) as the source for a record set (the record set to be living in
>>the Access app on Machine A).
>
>So you are using a front end on A that is running a query in a
>different MDB on a different machine (B)?
>
>How? I honestly don't know how to do that. It sounds too
>"client-server-ish" to be possible in Access. I know I can do it
>with views on SQL Server or Oracle, but on Access?
SELECT qryYourQuery.*
FROM qryYourQuery IN '\\MachineB\Databases\YourDatabase.mdb'
It still doesn't execute on Machine B.
It is simply retrieved to Machine A, the same way a saved QueryDef
would be retrieved from a different hard drive on Machine A.
I really wonder what Lyle is trying to get at, as I know he knows
all of this -- he's way too smart and experienced to think that
Machine A is getting anything from any instance of Jet running on
Machine B.
Lyle Fairfield
news:9309CFD8Bdf...@24.168.128.74:
I thought Albert was contesting this and I wanted to be sure that what I
believe is correct. What I believe on this matter is exactly what you say
about it.
--
Lyle
Albert Marshall
Fairfield <lyle...@yahoo.com> writes
>>
>> It is simply retrieved to Machine A, the same way a saved QueryDef
>> would be retrieved from a different hard drive on Machine A.
>>
>> I really wonder what Lyle is trying to get at,
I often do.
>>as I know he knows
>> all of this -- he's way too smart and experienced to think that
>> Machine A is getting anything from any instance of Jet running on
>> Machine B.
>
>I thought Albert was contesting this and I wanted to be sure that what I
>believe is correct. What I believe on this matter is exactly what you say
>about it.
>
I don't know what made you think anything of the kind. The statement I
originally made was:
A properly written Access database only moves the records
it needs across the network.
I didn't say if it was pulling or pushing, but after using Access for 9
years I would expect to have to pull. In fact, I took that for granted.
Similarly, I didn't say anything about Access being installed on the
server, or any of the other odd things you mentioned. The only time I
ever used Access on a server was via PC-Anywhere, which is just using
the server by remote control, and in that case the data, FE and Access
were all on the same machine.
Sometimes, Lyle, you remind me of a microbiologist I once knew. He
failed a basic test on microbes and how to control them because he knew
*too much*.