CODEWITHSUNDEEP
javaoracle-databaseperformancejpa
Loïc Gammaitoni
Loïc Gammaitoni

Reputation: 4171

JPA/Hibernate seems to convert a query with an in clause into multiple queries with = clauses

A solution we implemented in order to collect a large amount of heavy objects in reasonable time and without memory overflow (I'm speaking of objects with multiple fetchType.eager relations, to entities that themselves have eager fetched relations), was to first select ids of those objects, and then select the objects themselves based on these ids.

Coming the time to optimise our code, we noticed (using hibernate.show_sql=true) that the query we used to gather those objects (select a from A a where a.id in :ids) is translated by JPA/Hibernate into thousands of queries of the form select a from ...endless join list... where a.id = ?

The question is the following:

Why JPA/Hibernate transforms our initial query with an "in" clause into so many queries with "=" clauses. Isn't it inefficent ? If so, is there a way to prevent that ?

Here's how the query is called in our code:

    Query q = this.getContext().createQuery("select a from A a where a.id in :ids");
    q.setParameter("ids", idList);
    return (List<A>) q.getResultList();

Upvotes: 5

Views: 2334

Answers (3)

Lo&#239;c Gammaitoni
Lo&#239;c Gammaitoni

Reputation: 4171

Though I still can't explain why a query is generated for each ID provided in the in clause (provided in my original question), I found a solution to this problem on this blog (https://thoughts-on-java.org/fetch-multiple-entities-id-hibernate/). The solution consists in using Hibernate's session API as follows:

//get session object from entity manager
Session session = em.unwrap(Session.

MultiIdentifierLoadAccess<A> multiLoadAccess = session.byMultipleIds(A.class);
List<A> aObjects= multiLoadAccess.withBatchSize(1000).multiLoad(idList);
return aObjects;

Upvotes: 1

shalama
shalama

Reputation: 1335

Hi you should use setParameterList instead of setParameter. Also no need to use select in hibernate if you want to get the complete entity object. 

 Query q = this.getContext().createQuery("from A a where a.id in (:ids)");
q.setParameterList("ids", idList);
return (List<A>) q.getResultList();

Upvotes: 2

Tair
Tair

Reputation: 3819

If your idList is more that 1000 elements, you probably hit this Oracle-specific deoptimization https://hibernate.atlassian.net/browse/HHH-9299.

One way to solve this is to break idList into multiple chunks, execute a query for each and concatenate the results.

Upvotes: 0

Related Questions