I am working on a spring jpa application that uses hibernate 3.2.0.GA.

My question has to do with the join fetch option in a jpa query. The situation I have is there are MANY records in the database and the query could potentially return a large number of results.

Tuning on the sql seems to indicate the query prepares quickly and returns rows quickly. I have a one-to-many relationship. I am limiting the number of results that are returned using the setMaxRetulst option:


I want to display the child records of the one-to-many, so in my original query I used:



What I have found is this takes a really long time to return. If I switch this to not fetch join; the query runs in a reasonable about of time, although it runs N+1 queries to fetch the child records. I’m wondering if the “fetch” join requires that all the records be processed due to ordering issues? Is there a way (maybe a hint) to avoid this issue since in my case the records will be coming in order?

Do it in 2 queries:
load the main object (not the associated ones) and limit the number of results.

Then load the associated objects by a select ao from AssociatedObject as where id in (:associtedObjectsIds)

Or use fetch subselect (static mapping not HQL) which should do just that