org.hibernate.hql.ast.QueryTranslatorImpl list ATTENTION: firstResult/maxResults specified with collection fetch; applying in memory

Question

I'm facing a problem, I have a query in JPA. as I have some collections I need to use left join fetch or inner join fetch

My problem is in using the setFirstResult and setMaxResult in order to bring back a precise number of result. every time i see the whole result is bring back AND only AFTER the maxResult is used.

Is there any way to make the maxResult before ?

Thanks a lot !

here it is more information :

my problem is when i use that :

startIndex = 0;
maxResults = 10;
query.setFirstResult(startIndex);
query.setMaxResults(maxResults);

I see this message in my log :

7 juin 2011 09:52:37 org.hibernate.hql.ast.QueryTranslatorImpl list ATTENTION: firstResult/maxResults specified with collection fetch; applying in memory!

I see the 200 result coming back (in log) and after in the HashSet i have finally the 10 result i ask.

its seems in memory is bring back the 200 result and after the maxResults is applied in memory.

I'm searching if there is any way to be able to fetch and limit the number of result.

I used a workaround, I make a first query to ask the id of my order , without any fetch, used the maxResult. everything work perfectly it's used the limit instruction. After I use my "big" query with the fetch and limit the result inside the list of id bring back in the first one.

here it is my full query without my workaround (notice that there is no limit generated as talk by @Bozho ):

select o from Order  o
   left join fetch o.notes note
   left join fetch o.orderedBy orderedBy
   left join fetch orderedBy.address addressOrdered 
   left join fetch orderedBy.language orderedByLg 
   left join fetch orderedByLg.translations orderedByLgTtrad
   left join fetch o.deliveredTo deliveredTo 
   left join fetch deliveredTo.address addressDelivered 
   left join fetch deliveredTo.language deliveredToLg
   left join fetch deliveredToLg.translations 
   left join fetch o.finalReceiptPlace finalReceiptPlace
   left join fetch finalReceiptPlace.address addressFinalReceiptPlace 
   left join fetch finalReceiptPlace.language finalReceiptPlaceLg 
   left join fetch finalReceiptPlaceLg.translations
   inner join fetch o.deliveryRoute delivery
   left join fetch delivery.translations
   inner join fetch o.type orderType
   left join fetch orderType.translations 
   inner join fetch o.currency currency
   left join fetch currency.translations
   left join fetch o.attachments 
   left join fetch note.origin orig
   left join fetch orig.translations
   left join fetch o.supplier sup  
   left join fetch sup.department dep 
   left join fetch o.stateDetail stateD
   inner join fetch stateD.state stat  
where 1=1 and o.entryDate >= :startDat

Answer 1

TL;DR Hibernate doesn't know how many rows of the flattened, joined query it needs to get the specified number of the Order objects, so it has to load the whole query in memory. See below for an explanation.

To understand why Hibernate does this, you need to understand how Hibernate does the ORM (Object-Relational Mapping) involved for JPA Entities.

Consider a simplified set of Entities for your order. Class Order contains 2 fields: number and customerId and a list of order lines. Class OrderLine contains productCode and quantity fields, as well as a uid key and a reference to the parent Order.

These classes may be defined thus:

@Entity
@Table(name = "ORDER")
public class Order {
    @ID
    @Column(name = "NUMBER")
    private Integer number;
    @Column(name = "CUSTOMER_ID")
    private Integer customerId;
    @OneToMany(mappedBy = "order", fetch = FetchType.LAZY)
    @OrderBy
    private List<OrderLine> orderLineList;

    .... // Rest of the class
}

@Entity
@Table(name = "ORDER_LINE")
public class OrderLine
{
    @ID
    @Column(name = "UID")
    private Integer uid;
    @Column(name = "PRODUCT_CODE")
    private Integer productCode;
    @Column(name = "QUANTITY")
    private Integer quantity;
    @Column(name = "ORDER_NUMBER")
    private Integer orderNumber;
    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "ORDER_NUMBER", referencedColumnName = "NUMBER", insertable = false, updatable = false)
    private Order order;

    .... // Rest of the class
}

Now, if you performed the following JPQL query on these Entities:

SELECT o FROM Order o LEFT JOIN FETCH o.orderLineList

then Hibernate performs this query as a 'flattened' SQL query similar to the following:

SELECT o.number, o.customer_id, ol.uid, ol.product_code, ol.quantity, ol.order_number
FROM order o LEFT JOIN order_line ol ON order_line.order_number = order.number

which would give a result like this:

| o.number | o.customer_id | ol.uid | ol.product_code | ol.quantity |
|==========|===============|========|=================|=============|
| 1        | 123           | 1      | 1111            | 5           |
| 1        | 123           | 2      | 1112            | 6           |
| 1        | 123           | 3      | 1113            | 1           |
| 2        | 123           | 4      | 1111            | 2           |
| 2        | 123           | 5      | 1112            | 7           |
| 3        | 123           | 6      | 1111            | 6           |
| 3        | 123           | 7      | 1112            | 5           |
| 3        | 123           | 8      | 1113            | 3           |
| 3        | 123           | 9      | 1114            | 2           |
| 3        | 123           | 10     | 1115            | 9           |
...etc

which Hibernate would use to 'reconstruct' Order objects with attached lists of OrderLine sub-objects.

However, since the number of order lines per order is random, there is no way for Hibernate to know how many rows of this query to take to get the specified maximum number of Order objects required. So it has to take the whole query and build up the objects in memory until it has the right amount, before discarding the rest of the result set. The log warning it produces alludes to this:

ATTENTION: firstResult/maxResults specified with collection fetch; applying in memory!

We're only just discovering now that these queries can have a significant impact on server memory use, and we've had issues with our server falling over with out of memory errors when these queries are attempted.

By the way, I will say now that this is mostly just theory on my part and I have no idea how the actual Hibernate code works. Most of this you can glean from the logs when you have Hibernate logging the SQL statements it generates.

---

UPDATE: Recently I have discovered a little 'gotcha' with the above.

Consider a third Entity called Shipment which is for one or more Lines of an Order.

The Shipment entity would have a @ManyToOne association to the Order entity.

Let's say you have 2 Shipments for the same Order which has 4 Lines.

If you perform a JPQL query for the following:

SELECT s FROM Shipment s LEFT JOIN s.order o LEFT JOIN FETCH o.orderLineList

You would expect (or at least I did) to get 2 shipment objects back, each with a reference to the same Order object, which itself would contain the 4 Lines.

Nope, wrong again! In fact, you get 2 Shipment objects, each referring to the same Order object, which contains 8 Lines! Yes, the Lines get duplicated in the Order! And yes, that is even if you specify the DISTINCT clause.

If you research this issue here on SO or elsewhere (most notably the Hibernate forums), you'll find that this is actually a feature not a bug, according to the Hibernate powers that be. Some people actually want this behaviour!

Go figure.

Answer 2

It depends on the DB engine used...

I've the same problem with Derby DB setFirstResult / setMaxResults are not used.

ROW_NUMBER() OVER()... seems to be the solution.

Unfortunally I don't find how I can generate such requests with JPA without spécifing the whole request in SQL.

Answer 3

setMaxResult(10) wraps query with another one limiting result, eg for oracle:
select * from ("your query here") where rownum<10

I found out this today during investigation of query which took 64 seconds, now, it takes 1 second.

...the same for criteria.setProjection(Projections.rowCount()).setMaxResults(10)