# Optimizing generated sql queries when using parameters of type list

**URL:** https://discourse.hibernate.org/t/optimizing-generated-sql-queries-when-using-parameters-of-type-list/4809
**Category:** Hibernate ORM
**Created:** [November 25, 2020, 10:56am UTC](https://discourse.hibernate.org/t/optimizing-generated-sql-queries-when-using-parameters-of-type-list/4809 "2020-11-25T10:56:35Z")
**Posts on this page:** 6
**Page:** 1

<div class="post-metadata">

### Author: ![gonzalad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/gonzalad/32/1292_2.png) [@gonzalad](https://discourse.hibernate.org/u/gonzalad)
#### Post date: [November 25, 2020, 10:56am UTC](https://discourse.hibernate.org/t/optimizing-generated-sql-queries-when-using-parameters-of-type-list/4809/1 "2020-11-25T10:56:35Z")

</div>

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:

```auto
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.

```auto
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:

```auto
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

```auto
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:

```auto
 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

---

<div class="post-metadata">

### Author: ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)
#### Post date: [November 25, 2020, 11:16am UTC](https://discourse.hibernate.org/t/optimizing-generated-sql-queries-when-using-parameters-of-type-list/4809/2 "2020-11-25T11:16:22Z")

</div>

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](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.

---

<div class="post-metadata">

### Author: ![MeysamKarimiJRepo](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/meysamkarimijrepo/32/2577_2.png) [@MeysamKarimiJRepo](https://discourse.hibernate.org/u/MeysamKarimiJRepo)
#### Post date: [July 21, 2023, 10:45am UTC](https://discourse.hibernate.org/t/optimizing-generated-sql-queries-when-using-parameters-of-type-list/4809/3 "2023-07-21T10:45:39Z")

</div>

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

---

<div class="post-metadata">

### Author: ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)
#### Post date: [July 21, 2023, 3:16pm UTC](https://discourse.hibernate.org/t/optimizing-generated-sql-queries-when-using-parameters-of-type-list/4809/4 "2023-07-21T15:16:27Z")

</div>

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

---

<div class="post-metadata">

### Author: ![MeysamKarimiJRepo](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/meysamkarimijrepo/32/2577_2.png) [@MeysamKarimiJRepo](https://discourse.hibernate.org/u/MeysamKarimiJRepo)
#### Post date: [September 27, 2023, 1:59pm UTC](https://discourse.hibernate.org/t/optimizing-generated-sql-queries-when-using-parameters-of-type-list/4809/5 "2023-09-27T13:59:24Z")

</div>

> [@MeysamKarimiJRepo](#):
>
> clause\_parameter\_padding

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.

---

<div class="post-metadata">

### Author: ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)
#### Post date: [September 27, 2023, 4:17pm UTC](https://discourse.hibernate.org/t/optimizing-generated-sql-queries-when-using-parameters-of-type-list/4809/6 "2023-09-27T16:17:00Z")

</div>

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