Optimizing generated sql queries when using parameters of type list

Hibernate ORM
1

Hello,

Environment

I’m using Hibernate 5.4.12 with postgres 13.0

Question

When using a parameter of type list in a JPQL query, I see that the generated sql query declares a parameter for every item in the list.

i.e. if my list parameter has 14 items, I’ll get:

column in (
                ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
            )

I think this makes the db compile the sql statement again and again whenever the size of the list changes (perhaps I’m wrong here ?).

Is there always have a single item in the generated SQL ?

i.e.

column in (?)

I think it’s possible in plain SQL (i.e. with Oracle with ARRAY type, donno for postgres)

My use case

I’m using this kind of jpql query on postgresql:

select new SomeDto(res.externalId, res.type, t.code) FROM PolicyEntity p
                join  p.resources res
                where
                  concat(res.externalId, '@', res.type) in :resources
                  and u.idUtilisateur = :username"

The java code is like

List<String> resources = Arrays.asList(
  "2",
   "3",
   ...
);
TypedQuery<Row> query = entityManager.createQuery(jpql, Row.class);
query.unwrap(Query.class).setParameterList("resources", resources);

And I see the generated SQL is similar to:

 select
        resourcee1_.external_id as col_0_0_,
        resourcee1_.type as col_1_0_,
   from
        policy policyenti0_ 
    inner join
        resource resourcee1_ 
            on policyenti0_.id_resource=resourcee1_.id 
   where
        (
            (
                ressourcee1_.external_id||'@'||ressourcee1_.type
            ) in (
                ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
            )
        )

thanks,
Adrian

2

You can use parameter padding for a better reuse of statements by enabling the configuration hibernate.query.in_clause_parameter_padding: https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#configurations-query

Generally, it’s better to have different queries and thus also plans for different amount of parameters. Oracle and many other databases usually choose a more optimal plan for fewer parameters.

3

Hello @beikov ,
Is there any trace log that shows hibernate.query.in_clause_parameter_padding is working ?
Actually we configured and enabled that, But it doesn’t work!
Our version is 5..
Thanks

4

You’ll see that the SQL pads parameters to a number which is a power of two.

1 Like
5

In the Hibernate QueryParameterBindingsImpl internal class, there is a limitation that clause parameter binding optimization won’t work if the next power of two is more than 1000.

6

The padding will obviously stop at the maximum amount of supported parameters as reported by the Dialect.