Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 4 posts ] 
Author Message
 Post subject: Optimize infamous HHH000104: firstResult/maxRes... in memory
PostPosted: Mon Oct 23, 2017 10:26 am 
Newbie

Joined: Wed Nov 26, 2014 5:56 am
Posts: 12
Another question that came up while adventuring through JPA criteria queries. Consider the following entities (pseudocode to make it more concise):

Code:
@Entity
class A
{
   @Id
   String id;

   @ElementCollection
   @CollectionTable(name="a_b", joinColumns=@JoinColumn(name="a_id"))
   @OrderColumn(name = "i")
   List<B> bs;
}

@Embeddable
class B
{
   // A bunch of basic fields.
}

Now, suppose we want to query for some As. For each so selected A also fetch the first B in the associated ordered collection of Bs. In other words, the number of Bs that we are fetching per an A is known a priori (as opposed to an undeterminable number of Bs; e.g., fetch all Bs associated to some A).

Code:
int first = ..., max = ...;
CriteriaQuery<A> q = builder.createQuery(A.class);
Root<A> r = q.from(A.class)
@SuppressWarnings("unchecked")
ListJoin<A, B> abJ = (ListJoin<A, B>) r.fetch(A_.bs);
predicates.add(builder.equal(abJ.index(), 0));
TypedQuery<A> tq = em.createQuery(q
   .select(r)
   .where(predicates.toArray(new Predicate[predicates.size()]))
);
setRange(tq, first, max);
List<A> vs = tq.getResultList();

The fetch join together with the range specification results in the infamous warning 'HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!'. However, because of the additional index predicate, I think Hibernate should be able to optimize query execution not to involve the in memory processing. More generally, for any kind of query where the number of associated entities that are to be fetched is fixed and known a priori, it should be possible to formulate a query that delegates all work to the database system such that in memory processing is optimized away. Do you agree, or am I missing something here? If it is possible, how about filing a feature request issue?


Top
 Profile  
 
 Post subject: Re: Optimize infamous HHH000104: firstResult/maxRes... in memory
PostPosted: Mon Oct 23, 2017 11:35 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1515
I wrote an article about how this can be done with Window Functions.

However, if this will be supported, it will only go into Hibernate 6. You can add a Jira issue of course.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Optimize infamous HHH000104: firstResult/maxRes... in memory
PostPosted: Tue Oct 24, 2017 4:57 am 
Newbie

Joined: Wed Nov 26, 2014 5:56 am
Posts: 12
vlad wrote:

I'm aware of that article. In fact, I read it before writing this posting. The point about using JPA criteria queries is that I want to remain portable (and that the actual query is again constructed dynamically based on user input). To be portable when using Window Functions one would (i) need to write a query for each DBMS that one wants to support, (ii) provided that it supports Window Functions.

Quote:
However, if this will be supported, it will only go into Hibernate 6. You can add a Jira issue of course.

I will.


Top
 Profile  
 
 Post subject: Re: Optimize infamous HHH000104: firstResult/maxRes... in memory
PostPosted: Tue Oct 24, 2017 5:57 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1515
Until we will support it natively in Hibernate 6, you can build the Window Functions query with jOOQ, as demonstrated in my book GitHub repository.

Another option is to fetch the ids first with a Criteria API query, and then provide the ids to a JPQL query with fetches the desired entities.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 4 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.