Performance question

[Ray C]

This question concerns the subform control properties called Link Child
Fields and Link Master Fields. How does Access filter the data when using
these properties. I have a main form with several subforms. For each subform,
I set the proper fields for these properties and everything works fine.

However, with regards to performance. Does Access read all the records from
the underlying table and *then* perform the filtering using the fields set in
these properties?

My form is starting to get slow because of the volume of data. So I'm
wondering if I would improve the performance by removing the fields in these
properties and just have the subform's Recordsource do the filtering by
adding a WHERE clause to it so that the subform filters the records based on
the ID field of the main form.

Any help would be appreciated.
Thanks.

 

[Banana]

However, with regards to performance. Does Access read all the records from
the underlying table and *then* perform the filtering using the fields set in
these properties?

My form is starting to get slow because of the volume of data. So I'm
wondering if I would improve the performance by removing the fields in these
properties and just have the subform's Recordsource do the filtering by
adding a WHERE clause to it so that the subform filters the records based on
the ID field of the main form.

Any help would be appreciated.
Thanks.

Access actually doesn't load all data at once. Rather, it does a 'lazy
evaluation' by first retrieving the full set of keys it need to have to
be able to scroll the recordset, then when you browse to a record, it
then fetch the given record with cached key on demand as well refreshing
it/filling it background in small chunks to provide appearance of
responsive navigations.

Using a WHERE condition can help filter down on the initial set of keys
retrieved and is a good thing to do for all forms. It's always a good
idea to have some kind of limit of how many records is available for
forms; maybe restrict to only orders under 3 months old, only clients
who are active, only last 1000 transactions records... whatever works
for your need and providing a 'Archive' button which allows access to
older records at a cost to performance to that user.

FWIW, I always use a WHERE in all of my queries that I use for forms
recordsource.

I will need to check out whether Access also fetch all possible keys for
the subform or merely fetch all keys from the form and use that key, but
either way, that's another good reason to make sure the keys themselves
are small. Autonumbers are good, but if anyone tries to make a key out
of string or date types, then that may adversely affect the performance.

 

[golfinray]

Yes, it reads the table. If you have many subforms it will have to go and
read all those tables to get the data you want. Your form may be getting too
large. You might want to just build one form\subform, one form\subform, etc,
then put each on a tab.

 

[Banana]

Yes, it reads the table. If you have many subforms it will have to go and
read all those tables to get the data you want. Your form may be getting too
large. You might want to just build one form\subform, one form\subform, etc,
then put each on a tab.

I think we need to be careful in how we describe things. 'Read the
table' imply that Access eagerly fetch all data from all selected column
from the table for which form is bound to. That is not the case. It only
fetches the primary key of the given table and use that set of keys to
'scroll', fetching the complete row just in time.

With an Access backend, the fetching may be done in pages, as that's how
the data is organized. With a ODBC backend, the fetching would be done
in rows.

As explained before, having a good WHERE condition can reduce the set of
primary key Access needs to fetch for the scrolling. There are also
cases where a poor written query, especially with a ODBC backend, can
force unnecessary evaluation and significantly degrade performance.

As for the idea about tab, note that it does not mean those subform
nested in tabs doesn't get loaded only when the tab is selected. So a
form with 7 subforms on a tab control could have a big initial load time
because it has to fetch the keys and few initial records to satisfy the
8 forms in total. A common technique for this kind of design is to
actually implement only one subform 'above' the tab control (or use a
dummy buttons in lieu of tab controls) and changing the subform's
SourceObject property when the tab is changed, thus there's always two
form loaded, rather than eight.

 

[Klatuu]

There are a couple of issues here.
You don't want to change to filtered recordset for the subforms.

Yes, a large volume of data will degrade performance. You can help with
that by making sure that all the fields involved in the form's recordset
underlying table and the subform's underlying table are indexed. It makes
the search much faster.

Also, the place you are probably seeing a slow down is in the time it takes
to load the form. That is because it has to load the data form the main form
and all the subforms. You can speed this up by not setting the linking
fields in the subform control or the record source of the forms being used as
subforms. Strangely enough, subforms load before the form itself (even the
Access development team at Microsoft could not explain why when I asked
them). So you may experiment with do the assignment as the last things in
the Load event of the Form. It would be something like:

With Me
.SubformControl1.LinkedMasterFields = ....
.SubformControl2.LinkedMasterFields = ....
.SubformControl1.Form.Recordset = "SELECT ......"
.SubformControl2.Form.Recordset = "SELECT ......"
End With

The above is "air code", so you may have to monkey with the syntax