2

I'm currently using JPA (Eclipse link) batch queries to read an object from the database. Each object has several sub-collections, also reflected via JPA.

I use batch queries for the performance they offer, however there is a downside (perhaps avoidable, hence my question).

Given the following object definition (names have been changed to protect the innocent, but the structure remains the same).

@Entity
@Table(name = "TBL_ITEMX")
class ItemX
{
    @OneToMany(fetch = FetchType.EAGER)
    @JoinColumn(name = "ID_NO", referencedColumnName = "ID_NO")
    @BatchFetch(value = BatchFetchType.JOIN)
    private Collection<SubItem1> subItem1;

    @OneToMany(fetch = FetchType.EAGER)
    @JoinColumn(name = "ID_NO", referencedColumnName = "ID_NO")
    @BatchFetch(value = BatchFetchType.JOIN)
    private Collection<SubItem2> subItem2;

    @OneToMany(fetch = FetchType.EAGER)
    @JoinColumn(name = "ID_NO", referencedColumnName = "ID_NO")
    @BatchFetch(value = BatchFetchType.JOIN)
    private Collection<SubItem3> subItem3;

    ... getters/setters 
}

If I limit the query range with setFirstResult and setMaxResult for example

String qs = "select p from ItemX p"; 
Query qb = em.createQuery(qs);
qb.setFirstResult(0);
qb.setMaxResults(100);

This states that I only want the first 100 ItemX entries in the database, remember each ItemX has several collections. The query does limit ItemX between 0 and 100. However when the EntityManager processes the collection(s), it loads every database row, for each of the collections into memory (on the client).

Is there a way to limit this behavior, so that the batch query only pulls items for each collection that is required. Or alternatively ask the database to leave the resultant query on the server and provide a scrollable window of results? (I have tried scrollable results and that didn't help).

At the moment I can handle the query, but as we add more collections and data, the query is going to blow the JVM (and I don't really think a 64-bit JVM is the answer to this).

Any advice on how to limit the size of the sub-collection queries would be gladly received.

Thanks Rich

Rich
  • 3,722
  • 5
  • 33
  • 47
  • It is a bit strange, but since you are using batchfetchtype JOIN I guess eclipselink can't figure out how to limit the query/result appropriately. How you tried with persistnce logging turned on (level FINE or lower) to see the queries? How you tried with other batch types than JOIN (IN being a personal favourite). Which database are you using and is that more or less locked for the future? – esej Jun 22 '12 at 10:47
  • Thanks for the comments esej. I am using Oracle 11g, I'd say we're locked in for the foreseeable. I have FINE logging on and I can see the queries. I have tried IN, but the tables are slow, and IN is limited to 1000 per batch. So using IN does not offer much performance, however it does lower the memory footprint. Ideally I'd like JPA to create a temporary join table from the initial query, but I don't know to make JPA do this (if it's even possible). – Rich Jun 22 '12 at 10:56

1 Answers1

0

You need to use the IN batch fetch type if you want to use it with pagination.

James
  • 17,965
  • 11
  • 91
  • 146
  • I had previously tried batch IN fetch. It does give me the results I want, however the performance was terrible compared to batch join. Do you have any suggestion on increasing batch IN performance? I have tried multiple DB connections and batching at a maximum of 1000 per fetch. – Rich Jun 27 '12 at 15:46