I have a JPQL named query which reads up a list of orders from a Derby database:
SELECT DISTINCT o FROM Order o LEFT JOIN FETCH o.orderLines
The Order entity contains a List of OrderLines:
@OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL, mappedBy = "order")
private List<OrderLine> orderLines;
Running the query with Toplink Essentials as JPA provider (in Glassfish v2-b24) it doesn't seem to scale very well when the number of rows in the order and orderline tables increases (my test data consists of 2 order lines per order). With 1000 orders in the database it takes approximately 5 seconds to run the query. With 10 000 orders it takes approximately 500 seconds.
Using Hibernate as the JPA implementation the same figures are < 1 second for 1000 and around 2 seconds for fetching 10 000 orders.
The SQL statements generated seems very similar for Toplink Essentials and Hibernate.
Is there a known performance problem with Toplink Essentials and LEFT JOIN FETCH queries?
[Message sent by forum member 'sofiajonsson' (sofiajonsson)]
http://forums.java.net/jive/thread.jspa?messageID=204272