EclipseLink @JoinFetch and @BatchFetch does not work which leads to inefficient SQL queries

[DimitarPetrov]

Hello,

We have an entity that has na @ElementCollection of @embeddable entities.

When a Query for the entity is made there are two SQL statements that are generated:

[EL Fine]: sql: 2022-07-29 15:34:33.98--ServerSession(517991921)--Connection(10098775)--Thread(Thread[http-nio-8080-exec-3,5,main])--SELECT DISTINCT name, system_number, description, base_url, product_type, id FROM tenants_apps WHERE (tenant_id = ?)
	bind => [3e64ebae-38b5-46a0-b1ed-9ccee153a0ae]
[EL Fine]: sql: 2022-07-29 15:34:42.849--ServerSession(517991921)--Connection(705602646)--Thread(Thread[http-nio-8080-exec-3,5,main])--SELECT DISTINCT t0.id, t1.key, t1.value FROM tenants_apps t0, ord_documentation_labels t1 WHERE (t1.application_id = t0.id) ORDER BY t0.id ASC

As you can see the second query for the embedded entity does not have any top-level entity dependant where clause. This means that the second query will load everything from the tables and not only embedded entities that match the selected top-level entities from the first query.

This behaviour can be changed via @BatchFetch and @JoinFetch eclipselink annotations.

However, they do not have any effect over the queries generated by the jpa processor. This leads to very inefficient SQL queries for large tables with many records in them and therefore very poor performance.