MS Access Multiuser - Optimizing forms by limiting recordsource- what's really true

Author

Optimizing forms by limiting recordsource- what's really true

John Welch

2005-08-02, 1:24 pm

I'm developing my first multi-user database, and I want to address the most
significant optimization issues. I've been reading a lot about multi-user
optimization (ADH vol 2, google, Tony Toews' tips, etc.), but there seems to
be disagreement about the importance of limiting the size of a form's
recordsource, i.e. getting input from user to create a WHERE clause in a
query then setting form's recordsource to that query. It's obvious that this
is important with an SQL server backend, because the server will be
processing the query, but how important is it with an mdb backend?
Some people insist that an mdb backend must send the "whole table" across
the wire to be processed by a query on the front end, and if that's true, I
can't see why using WHERE to limit the recordsource would help. But I've
been getting the idea that if there are proper indices, then it happens more
like this:

1) The entire index is sent across the wire and into memory on local machine
2) Query on front end searches the index and identifies records that match
the criteria
3) Query requests these records from back end, which sends only these
records

Is this correct? And if so, can it make a big difference?
Tony Toews's performance page mentions setting recordsources at runtime, but
not necessarily limiting their size.
Larry Linson seems a strong proponent of limiting size, but then others tell
me no, "the whole table comes across anyway."

By the way, my app will be an mdb backend on a LAN, ~10 concurrent users and
50,000 records.

Thanks in advance
-John

Van T. Dinh

2005-08-03, 11:25 am

See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)

"John Welch" < j+ohnw+elch@cal+cent
ral.com (remove +'s)> wrote in message
news:dco9l701gmp@ene
ws2.newsguy.com...
> I'm developing my first multi-user database, and I want to address the
> most significant optimization issues. I've been reading a lot about
> multi-user optimization (ADH vol 2, google, Tony Toews' tips, etc.), but
> there seems to be disagreement about the importance of limiting the size
> of a form's recordsource, i.e. getting input from user to create a WHERE
> clause in a query then setting form's recordsource to that query. It's
> obvious that this is important with an SQL server backend, because the
> server will be processing the query, but how important is it with an mdb
> backend?
> Some people insist that an mdb backend must send the "whole table" across
> the wire to be processed by a query on the front end,

That is not true. If that the case, some of my applications would have been
exceedingly slow. However, you need to be careful of setting criteria or
sorting. For eaxample, if the criteria or sorting is placed on a
non-indexed Field, Field values of all Records may need to be retrieved
before selection criteria / sorting can be applied. See next section also.

> and if that's true, I can't see why using WHERE to limit the recordsource
> would help. But I've been getting the idea that if there are proper
> indices, then it happens more like this:
>
> 1) The entire index is sent across the wire and into memory on local
> machine
> 2) Query on front end searches the index and identifies records that match
> the criteria
> 3) Query requests these records from back end, which sends only these
> records
>
> Is this correct? And if so, can it make a big difference?

It sounds correct and consistent with the performance of my databases. When
the selection criteria can use the indexes (indices?), the query execution
is always a lot faster. This is the Rushmore Query Optimisation implemented
in JET.

Also, in my experience, Access doesn't retrieve all selected rows in one go.
It seems that Access only retrieve enough to display on screen and a bit
more for reserve (like memory cache). You can observe this behaviour in
DatasheetView or Form in CtsFormView: when you scroll down quickly, there
are time when Access stalls a little bit waiting for additional data being
retrieved from the Back-End.

> Tony Toews's performance page mentions setting recordsources at runtime,
> but not necessarily limiting their size.
> Larry Linson seems a strong proponent of limiting size, but then others
> tell me no, "the whole table comes across anyway."
>
I doesn't do any harm trying to reduce the RecordSource to as small as
possible.

> By the way, my app will be an mdb backend on a LAN, ~10 concurrent users
> and 50,000 records.
>
For a well-designed and implemented database, this won't be a problem but
need to think about a procedure for regular back-ups without interrupting
the users' needs to use / access the data.

> Thanks in advance
> -John
>

david epsom dot com dot au

2005-08-03, 8:25 pm

> Is this correct? And if so, can it make a big difference?

If you have only one user (yourself, in development mode!)
then Windows moves the entire file into your local file
cache. So sometimes you don't see the effect of querying
large recordsets across the LAN.

Then, when your users are seeing 3 minutes to open a
form, you realise that every little bit adds up.

But there are plenty of other things that can take a long
time: multiple requeries, Sorting & Joining, UDF's etc.
These factors are in play with small recordsets AND with
large recordsets.

Even with large recordsets, the first critical point is
likely to be not the number of records displayed, but the
process which put them there.

(david)

"John Welch" < j+ohnw+elch@cal+cent
ral.com (remove +'s)> wrote in message
news:dco9l701gmp@ene
ws2.newsguy.com...
> I'm developing my first multi-user database, and I want to address the
> most significant optimization issues. I've been reading a lot about
> multi-user optimization (ADH vol 2, google, Tony Toews' tips, etc.), but
> there seems to be disagreement about the importance of limiting the size
> of a form's recordsource, i.e. getting input from user to create a WHERE
> clause in a query then setting form's recordsource to that query. It's
> obvious that this is important with an SQL server backend, because the
> server will be processing the query, but how important is it with an mdb
> backend?
> Some people insist that an mdb backend must send the "whole table" across
> the wire to be processed by a query on the front end, and if that's true,
> I can't see why using WHERE to limit the recordsource would help. But I've
> been getting the idea that if there are proper indices, then it happens
> more like this:
>
> 1) The entire index is sent across the wire and into memory on local
> machine
> 2) Query on front end searches the index and identifies records that match
> the criteria
> 3) Query requests these records from back end, which sends only these
> records
>
> Is this correct? And if so, can it make a big difference?
> Tony Toews's performance page mentions setting recordsources at runtime,
> but not necessarily limiting their size.
> Larry Linson seems a strong proponent of limiting size, but then others
> tell me no, "the whole table comes across anyway."
>
> By the way, my app will be an mdb backend on a LAN, ~10 concurrent users
> and 50,000 records.
>
> Thanks in advance
> -John
>

Albert D.Kallal

2005-08-04, 1:27 pm

"John Welch" < j+ohnw+elch@cal+cent
ral.com (remove +'s)> wrote in message
news:dco9l701gmp@ene
ws2.newsguy.com...

> i.e. getting input from user to create a WHERE clause in a query then
> setting form's recordsource to that query. It's obvious that this is
> important with an SQL server backend, because the server will be
> processing the query, but how important is it with an mdb backend?

You don't even have to set the forms recordsouce. All reports, and forms
have a "where" clause. You can use that..and save all the trouble of having
to set the forms record source. So, use the where clause to open the one
record.

strCustID = InputBox("What customer id")

docmd.OpenForm "frmCustomer",,,"CustomerID = " & strCustID

> Some people insist that an mdb backend must send the "whole table" across
> the wire to be processed by a query on the front end, and if that's true

No, the above does not happen. Remember, think of the mdb file sitting on
your hard disk. IF every time you grabbed a record off the disk drive, the
WHOLE table got loaded into memory, then ms-access would be slow with a tiny
of 75,000 records. The whole table does NOT get read into memory. Now, the
fact that you all of a sudden move the mdb file from your hard disk to a
network share...why would this change anything? (nothing changes in terms of
what gets read from the disk drive...).

>
> 1) The entire index is sent across the wire and into memory on local
> machine

no no. The above does not happen either!!! What happens is the only parts of
the index are sent as it is read. (again, the fact of the mdb file sitting
on your hard disk....or the computer down the hall DOES NOT CHANGE what gets
read from the mdb file). If you are familiar with a balanced binary tree,
then to find one record out of 1 million will likely results in about 4-9
records (of the index file) being read. So, traversing this binary tree does
not require the whole index be read in. You start at the top of the binary
tree, and then go left, or right down the branches.

> 2) Query on front end searches the index and identifies records that match
> the criteria

Yes, the above makes sense. Note that each record resides in a 2000 byte
memory page, or often called bucket. This is the SMALLEST size can be read
from disk. So, often that page will contain several records. If you got a
index, then hopefully those records are grouped together in that page (and,
when you do compact and repair, records are ordered by the primary
index...so huge gains in performance can occur after you compact and repair
since records are grouping into those pages).

> 3) Query requests these records from back end, which sends only these
> records

Yes. Better said is JET opens the back end file (does not care where it is),
and starts reading in the correct pages/chunks of that file (again, the fact
of the network makes NO difference.

>
> Is this correct? And if so, can it make a big difference?

Yes, it makes a huge differences. Remember, if a index can NOT be used, then
ALL records must be read into memory and scanned.

> Tony Toews's performance page mentions setting recordsources at runtime,
> but not necessarily limiting their size.

You don't have to set the recordsouce at runtime, but doing so certainly
forces you to think about what you are throwing into the form.

> Larry Linson seems a strong proponent of limiting size, but then others
> tell me no, "the whole table comes across anyway."
>

We have determined that both the whole table, and the whole index is NOT
read from the file (note I use the term "read from file", since this makes
no differences if a network is involved - sure, if we read the whole table
in, then the whole table gets transferred from the mdb file into memory (so,
if that mdb file is on a network..then obviously whatever gets read from the
mdb file gets transferred).

> By the way, my app will be an mdb backend on a LAN, ~10 concurrent users
> and 50,000 records.

50,000 records should not present any kind of noticeable delay. you got a
low user count, and a very small file...

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

Tony Toews

2005-08-04, 1:27 pm

"Albert D.Kallal" < PleaseNOOOsPAMmkalla
l@msn.com> wrote:

>Note that each record resides in a 2000 byte
>memory page, or often called bucket.

Actually in Jet 4.0 that's a 4 K page. In Jet 3.5 that was 2K.

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

John Welch

2005-08-05, 11:24 am

Thanks everyone, that was really helpful information.
-John

John Welch

2005-09-16, 8:24 pm

Albert, (and other MVPS) -are you sure that using a where clause with
docmd.openform does the same thing as setting a form's recordsource to a
query?
It seems to me that all it does is apply a filter to the form, i.e. loads
all records into form and then hides most of them until you turn off the
filter. If I turn off the filter, I see all the records - were they being
'hidden' or did they get fetched right after I turned off the filter?
thanks
-John

"Albert D.Kallal" < PleaseNOOOsPAMmkalla
l@msn.com> wrote in message
news:Oh1IjTRmFHA.3300@TK2MSFTNGP15.phx.gbl...
> "John Welch" < j+ohnw+elch@cal+cent
ral.com (remove +'s)> wrote in message
> news:dco9l701gmp@ene
ws2.newsguy.com...
>
>
> You don't even have to set the forms recordsouce. All reports, and forms
> have a "where" clause. You can use that..and save all the trouble of
> having to set the forms record source. So, use the where clause to open
> the one record.
>
> strCustID = InputBox("What customer id")
>
> docmd.OpenForm "frmCustomer",,,"CustomerID = " & strCustID
>
>
> No, the above does not happen. Remember, think of the mdb file sitting on
> your hard disk. IF every time you grabbed a record off the disk drive, the
> WHOLE table got loaded into memory, then ms-access would be slow with a
> tiny of 75,000 records. The whole table does NOT get read into memory.
> Now, the fact that you all of a sudden move the mdb file from your hard
> disk to a network share...why would this change anything? (nothing changes
> in terms of what gets read from the disk drive...).
>
>
> no no. The above does not happen either!!! What happens is the only parts
> of the index are sent as it is read. (again, the fact of the mdb file
> sitting on your hard disk....or the computer down the hall DOES NOT CHANGE
> what gets read from the mdb file). If you are familiar with a balanced
> binary tree, then to find one record out of 1 million will likely results
> in about 4-9 records (of the index file) being read. So, traversing this
> binary tree does not require the whole index be read in. You start at the
> top of the binary tree, and then go left, or right down the branches.
>
>
> Yes, the above makes sense. Note that each record resides in a 2000 byte
> memory page, or often called bucket. This is the SMALLEST size can be read
> from disk. So, often that page will contain several records. If you got a
> index, then hopefully those records are grouped together in that page
> (and, when you do compact and repair, records are ordered by the primary
> index...so huge gains in performance can occur after you compact and
> repair since records are grouping into those pages).
>
>
> Yes. Better said is JET opens the back end file (does not care where it
> is), and starts reading in the correct pages/chunks of that file (again,
> the fact of the network makes NO difference.
>
>
> Yes, it makes a huge differences. Remember, if a index can NOT be used,
> then ALL records must be read into memory and scanned.
>
>
> You don't have to set the recordsouce at runtime, but doing so certainly
> forces you to think about what you are throwing into the form.
>
>
> We have determined that both the whole table, and the whole index is NOT
> read from the file (note I use the term "read from file", since this makes
> no differences if a network is involved - sure, if we read the whole table
> in, then the whole table gets transferred from the mdb file into memory
> (so, if that mdb file is on a network..then obviously whatever gets read
> from the mdb file gets transferred).
>
>
> 50,000 records should not present any kind of noticeable delay. you got a
> low user count, and a very small file...
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@
msn.com
> http://www.members.shaw.ca/AlbertKallal
>
>

Douglas J. Steele

2005-09-16, 8:24 pm

I don't have a definitive answer for you, but I'm pretty sure there's a
difference between passing a filter to the form and passing it a Where
clause.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"John Welch" < john(remove)welch@ca
l(remove)central.com> wrote in message
news:dgf2ek06e9@enew
s2.newsguy.com...
> Albert, (and other MVPS) -are you sure that using a where clause with
> docmd.openform does the same thing as setting a form's recordsource to a
> query?
> It seems to me that all it does is apply a filter to the form, i.e. loads
> all records into form and then hides most of them until you turn off the
> filter. If I turn off the filter, I see all the records - were they being
> 'hidden' or did they get fetched right after I turned off the filter?
> thanks
> -John
>
> "Albert D.Kallal" < PleaseNOOOsPAMmkalla
l@msn.com> wrote in message
> news:Oh1IjTRmFHA.3300@TK2MSFTNGP15.phx.gbl...
>
>

Sponsored Links