Skip Headers

# Avoiding Join-Reading Duplicate Data

Previous

Next

Join reading can result in returning duplicate data if a one-to-many or a shared one-to-one relationship is joined. Although TopLink correctly filters the duplicate results from the object result, the duplicate data still must be fetched from the database and can degrade performance, especially if multiple one-to-many relationships are joined. In general, batch reading can be used as a better alternative to join reading, as it does not require fetching duplicate data.

Oracle recommends that you use one-to-many joining with caution, because it does not scale well in many situations.

Because the main cost of a `ReadObjectQuery` is SQL execution, the performance of a one-to-many join in this case is usually better than a query without joining.

However, because the main cost of a `ReadAllObjectQuery` is row-fetching, which the duplicate data of a join increases, the performance of a one-to-many join in this case is less efficient than batch reading in many scenarios (even though one-to-many joining is more efficient than reading the objects one-by-one).

This is mainly due to the fact that a one-to-many join reads in duplicate data: the data for each source object will be duplicated for each target object. Depending on the size of the one-to-many relationship and the size of the source object's row, this can become very inefficient, especially if the source object has a Large Object (LOB).

If you use multiple or nested one-to-many joins in the same query, the problem is compounded: the source object's row is duplicated n*m times, and each target object n and m times respectively. This can become a major performance issue.

To handle empty collections, you must use outer joins, so the queries can easily become very database intensive. Batch reading has the advantage of only returning the required data, and does not require outer joins.

Oracle recommends that you use batch reading to optimize querying relationships in read-all applications.

For more information, see the following:

- "Batch and Join Reading"

- "Reading Case 2: Batch Reading Objects"