Child/Parent relationship

[JonoB]

As a general efficiency topic, I have the following question:

Assuming two tables in a one to many relationship, fully normalised, and a main form and sub form based on these tables.

The 'usual' way of linking these is to create a parent/child relationhip between the main and sub form, thereby allowing the sub form to only show those records that are linked to the main form. However, from an effiency point of view, does this not mean that the subform is retireiving ALL records and filtering out those that match the main form? If so, then this would be quite inefficient.

An alternative would be to only return those records in the subform by setting the criteria in the underlying query of the subform, resulting in only the relevant records being returned via the query. By doing this, the parent/child relationhip between the main form and subform can be deleted. Would this be faster and more efficient?

Thanks for the help.

 

[fiep]

JonoB

This will only be faster if your back-end is not an Access database. And you will probably have to use a pass-through query as well. As I understand it, Access will always get all information needed for a qeury and then filter this in the memory of the computer of the user.

Maybe somebody else has more information for you.

W.

 

[RickSpr]

JonoB said: "...does this not mean that the subform is retireiving ALL records and filtering out those that match the main form?"

It doesn't necessarily mean that. Access might actually do what you're proposing to do in code. Or it might choose a technique based on the environment, which would be more efficient than what you propose.

There's no simple formula for efficiency here. Factors which affect the choice include:
1. Whether the tables are large or small.
2. Whether the link fields are indexed.
3. Whether the tables are remote or local.
4. Whether the tables are Jet or something else.
5. How much memory is available for holding indexes and keysets or equivalent.
6. What the ratio of parent rows to related child rows is.

The developers of Access presumably have attempted at least some degree of discretion in choosing how to synchronize forms and subforms. You, having complete information about the tables in a particular application, might be able to make a better choice. But you aren't likely to make a better choice just by blindly adopting one technique for all applications.

In the final analysis, the wise approach is to only attempt to override Access when you actually have a problem, and to test your alternate technique in its target environment.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[JonoB]

Thanks for that.....

I have since had someone run a test on 1,000,000 records and there was no performance change between going parent/child or query criteria

 

[RickSpr]

That's cool to know. Thanks for updating.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein