How do I batchload many to many relations

[von Carlsen]

In our project we have entity A that has a many to many reference to entity B. Thousands of A entities normally reference the same B entity.
When I search 1000 A entities and on these use the shared B entity I have not found a way to avoid fetching the same B entity 1000 times from the database!
I thought it could be done with batchread but it dosn't seem to work. I have made a small project to illustrate the problem with an Employee that can have several Addresses like this:

Employee:
@ManyToMany
    public List<Address> getAddresses() {
        return addresses;
    }

And search for the employees like this:

Query query = em.createQuery("select e from Employee e"); query.setHint("eclipselink.batch", "e.addresses");

But the sql send to the database is:

SELECT t1.ID, t1.STREET, t0.Employee_ID FROM EMPLOYEE_ADDRESS t0, EMPLOYEE t2, ADDRESS t1 WHERE ((t0.Employee_ID = t2.ID) AND (t1.ID = t0.addresses_ID)

which will fetch the address info one time for each employee and then discard it in the entity manager when it finds out that it already has the Address loaded.

Is there another way of doing this.

[James Sutherland]

Batch reading uses a distinct for OneToOne to avoid duplicate data, but this is not possible for ManyToMany.

The issue is that the resulting objects must be matched to their owners, so the owner's ID (t0.Employee_ID) must also be selected, so the rows are not distinct, a row is required for each relationship.  So there is no way to avoid the select, at least from the join table (you could re-map it as a OneToMany to a join table object with a OneTOne, but unless your B object is very big, this probably will not be beneficial).

You will still benefit from not having to build the B objects.

von Carlsen wrote:

In our project we have entity A that has a many to many reference to entity B. Thousands of A entities normally reference the same B entity.
When I search 1000 A entities and on these use the shared B entity I have not found a way to avoid fetching the same B entity 1000 times from the database!
I thought it could be done with batchread but it dosn't seem to work. I have made a small project to illustrate the problem with an Employee that can have several Addresses like this:

Employee:
@ManyToMany
    public List<Address> getAddresses() {
        return addresses;
    }

And search for the employees like this:

Query query = em.createQuery("select e from Employee e"); query.setHint("eclipselink.batch", "e.addresses");

But the sql send to the database is:

SELECT t1.ID, t1.STREET, t0.Employee_ID FROM EMPLOYEE_ADDRESS t0, EMPLOYEE t2, ADDRESS t1 WHERE ((t0.Employee_ID = t2.ID) AND (t1.ID = t0.addresses_ID)

which will fetch the address info one time for each employee and then discard it in the entity manager when it finds out that it already has the Address loaded.

Is there another way of doing this.

[von Carlsen]

Thank you very much for your reply. My situation is that my B objects contains a huuuge blob and I had thought of the solution about splitting the B structure so that I only fetch a small part many times and let B reference a new entity with most of the original fields in B (including the blob) in a OneToOne lazyloaded relationship. I just wanted to be absolutely sure that there was no better way of doing it.

[James Sutherland]

You could also look into fetch groups in EclipseLink, or mark the blob basic mapping as LAZY.

von Carlsen wrote:

Thank you very much for your reply. My situation is that my B objects contains a huuuge blob and I had thought of the solution about splitting the B structure so that I only fetch a small part many times and let B reference a new entity with most of the original fields in B (including the blob) in a OneToOne lazyloaded relationship. I just wanted to be absolutely sure that there was no better way of doing it.

[Sebastien Tardif-2]

FYI: Thread-safe and refreshable implementation of read-only object have
been added to bug 245726

Also session object will not be corrupted if UOW instance are modified
by rogue code. Invalid session object are refreshed when requested via
UOW instead of getting invalid object like we get right now. Useful when
using EclipseLink JMS based invalidation or DB is modified outside the
application and you use time based invalidation.

So raise EclipseLink to the same correctness that Hibernate provide for
read-only object.

See http://bugs.eclipse.org/bugs/show_bug.cgi?id=245726

_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users