Hibernate run IN (VALUES ...) rather than IN query

Question

I have a huge number of entities i need to load from the DB, i have a query like this

FROM ${entityName} x WHERE x.id IN :ids

the above generates SQL queries like this: ... where EntityName.id in (? , ? , ? , ? , ? , ? , ? , ? , ...) This is incredibly slow for large ids sets. Multiple posts here have suggested instead to run an IN VALUES query like this:

... where EntityName.id in (VALUES (?) , (?) , (?) , (?) , (?) , (?) , (?) , (?) , ...)

Here is my Java:

List<T> result = entityManager.createQuery(queryString, queryEntity)
    .setParameter("ids", batch)
    .getResultList()

Wondering how i can get hibernate to do that? Do i need to specify that in the HQL somehow? I tried this not expecting it to work, and it didn't. SELECT x FROM ${entityName} x WHERE x.id IN (VALUES :ids)

Is this even possible in HQL, or do i need to write a native query?

using postgreSql. And poste like these: https://stackoverflow.com/questions/17813492/postgres-not-in-performance, https://stackoverflow.com/questions/24647503/performance-issue-in-update-query — Bend, Apr 28 '23 at 13:12

score 0 · Answer 1 · answered Apr 28 '23 at 13:52

I mean, look, you can in principle do this extremely easily by just passing a native SQL query to createNativeQuery().

But you really need to ask yourself:

what am I even doing here?

If you have such a huge quantity of ids that there are too many for Postgres to handle them efficiently in an in predicate, then it's highly unlikely that pulling all that state into the JVM and materializing entity objects to represent all those rows is an efficient or reasonable thing to do.

If you absolutely have to do this, make sure you use StatelessSession.

But have you considered using a stored procedure or something like that?

Hibernate run IN (VALUES ...) rather than IN query

1 Answers1