Table Relationships with Queries / Recordsources

[Guest]

If I defined table relationships then create a query or recordsource are the
relationships maintained in the query or recordsource if I manually removed
the joins within the query / recordsource. In other words even if the joins
are removed does the software have the knowledge to recognize these global
joins. Is there an article to support / not support this?

A coworker suggested I remove the joins within the query / recordsource to
speed up the form. When I view the data within the query / recordsource I
have 285 records when the joins are there and 85 454 975 when the joins are
not there. When the joins are not there the form does open MUCH faster but
after a while I get the error "Not enough space on temporary disk."

This issue arose when I had speed issues within a database. The general
recommendations to increase speed like indexing are already in place.

Thanks
Can

 

[Marshall Barton]

If I defined table relationships then create a query or recordsource are the
relationships maintained in the query or recordsource if I manually removed
the joins within the query / recordsource. In other words even if the joins
are removed does the software have the knowledge to recognize these global
joins. Is there an article to support / not support this?

A coworker suggested I remove the joins within the query / recordsource to
speed up the form. When I view the data within the query / recordsource I
have 285 records when the joins are there and 85 454 975 when the joins are
not there. When the joins are not there the form does open MUCH faster but
after a while I get the error "Not enough space on temporary disk."

This issue arose when I had speed issues within a database. The general
recommendations to increase speed like indexing are already in place.

Removing the joins just specifies a different kind of join
(i.e. crossproduct where even record in one table reates to
every record in the other table).

To improve performance analyze the query that works and try
(can be frustrating) to optimize the interactions. Most
often the biggest gain comes from making sure that you have
an index on each of the fields used in the join's ON clause,
the fields with criteria and the fields used in the ORDER BY
clause.