If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Performance question
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. |
#2
|
|||
|
|||
Performance question
Ray C wrote:
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. |
#3
|
|||
|
|||
Performance question
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. -- Milton Purdy ACCESS State of Arkansas "Ray C" wrote: 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. |
#4
|
|||
|
|||
Performance question
golfinray wrote:
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. |
#5
|
|||
|
|||
Performance question
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 -- Dave Hargis, Microsoft Access MVP "Ray C" wrote: 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. |
#6
|
|||
|
|||
Performance question
I think I know what you mean by "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", but if I did not I might think you are saying all fields should be indexed. As I understand, indexing helps performance when applied to fields used for sorting, filtering, and so forth, including linking fields in subform tables, but there is a limit to the number of fields that can be indexed. Klatuu wrote: 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 This question concerns the subform control properties called Link Child Fields and Link Master Fields. How does Access filter the data when using [quoted text clipped - 13 lines] Any help would be appreciated. Thanks. -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|