This jOOQ feature is indeed really useful since it reduces the number of SQL statements that have to be generated when varying the IN clause parameters dynamically.
Starting with Hibernate ORM 5.2.18, it’s now possible to use IN clause parameter padding so that you can improve SQL Statement Caching efficiency.
In this article, I’m going to explain how this new mechanism works and why you should definitely consider it when using a relational database system that supports Execution Plan caching.
Default behavior
Now, considering we have the following JPA entity:
@Entity(name = "Post")
@Table(name = "post")
public class Post {
@Id
private Integer id;
private String title;
//Getters and setters omitted for brevity
}
And, let’s say we want to load multiple Post entities by their identifiers using the following JPA entity query:
List<Post> getPostByIds(
EntityManager entityManager,
Integer... ids) {
return entityManager.createQuery(
"select p " +
"from Post p " +
"where p.id in :ids", Post.class)
.setParameter("ids", Arrays.asList(ids))
.getResultList();
}
Hibernate will execute the following SQL statements:
Query:["
SELECT p.id AS id1_0_, p.title AS title2_0_
FROM post p
WHERE p.id IN (? , ? , ?)
"],
Params:[
1, 2, 3
]
Query:["
SELECT p.id AS id1_0_, p.title AS title2_0_
FROM post p
WHERE p.id IN (?, ?, ?, ?)
"],
Params:[
1, 2, 3, 4
]
Query:["
SELECT p.id AS id1_0_, p.title AS title2_0_
FROM post p
WHERE p.id IN (? , ? , ? , ? , ?)
"],
Params:[
1, 2, 3, 4, 5
]
Query:["
SELECT p.id AS id1_0_, p.title AS title2_0_
FROM post p
WHERE p.id IN (? , ? , ? , ? , ? , ?)
"],
Params:[
1, 2, 3, 4, 5, 6
]
Each invocation generates a new SQL statement because the IN query clause requires a different number of bind parameters.
However, if the underlying relational database provides an Execution Plan cache, these 4 SQL queries will generate 4 different Execution Plans.
Therefore, in order to reuse an already generated Execution Plan, we need to use the same SQL statement String value for multiple combinations of IN clause bind parameters.
In clause parameter padding
If you enable the hibernate.query.in_clause_parameter_padding Hibernate
And rerun the previous test case, Hibernate will generate the following SQL queries:
Query:["
SELECT p.id AS id1_0_, p.title AS title2_0_
FROM post p
WHERE p.id IN (?, ?, ?, ?)
"],
Params:[
1, 2, 3, 3
]
Query:["
SELECT p.id AS id1_0_, p.title AS title2_0_
FROM post p
WHERE p.id IN (?, ?, ?, ?)
"],
Params:[
1, 2, 3, 4
]
Query:["
SELECT p.id AS id1_0_, p.title AS title2_0_
FROM post p
WHERE p.id IN (? , ? , ? , ? , ? , ? , ? , ?)
"],
Params:[
1, 2, 3, 4, 5, 5, 5, 5
]
Query:["
SELECT p.id AS id1_0_, p.title AS title2_0_
FROM post p
WHERE p.id IN (? , ? , ? , ? , ? , ? , ? , ?)
"],
Params:[
1, 2, 3, 4, 5, 6, 6, 6
]
Therefore, this time, only 2 Execution Plans are needed since both the first two queries and the last two ones have the same number of bind parameter values.
This is possible because Hibernate is now padding parameters until the next power of 2 number. So, for 3 and 4 parameters, 4 bind parameters are being used. For 5 and 6 parameters, 8 bind parameters are being used.
If you’re using Oracle or SQL Server, then you can benefit from Execution Plan caching. The IN clause parameter padding feature increases the chance of reusing an already generated Execution Plan, especially when using a large number of IN clause parameters.
It is a shame that you are not working for Red Hat anymore. I was already wondering why there are no new commits from you. You did solve some quit old issues and added nice little features like the ‘in parameter padding’.
I did as you sugested and provided a Pull Request.
All the best
I’m running my own company now which offers High-Performance Java Persistence training and consulting.
Is there a reason this wasn’t turned on by default?
Hello Vlad,
Does API re-pad with OR statement, e.g … IN ( (?,?…?) OR (?,?…?) … ) for exceeding in-clause limit( Oracle has default 1000 limit)?
I haven’t tested it. Anyway, it’s not efficient even if it works.
I did create an issue:
https://hibernate.atlassian.net/browse/HHH-14119
Since I’m no longer working for Red Hat, you should try to provide a Pull Request as the core team might review it and merge it.
It is a shame that you are not working for Red Hat anymore. I was already wondering why there are no new commits from you. You did solve some quit old issues and added nice little features like the ‘in parameter padding’.
I did as you sugested and provided a Pull Request.
All the best
I’m running my own company now which offers High-Performance Java Persistence training and consulting.
Is there a reason this wasn’t turned on by default?
Backward compatible is the reason.
Hello Vlad,
why is this only working for parameters and not for criteria query with literals.
Working: typedQuery.setParameter(“ids”, parameterList);
Not Working: criteriaQuery.where(root.get(“id”).in(parameterList));
I thought both produces prepared statements.
Best regards,
Oliver
Check out this article and switch to
LiteralHandlingMode.BIND
.We already use LiteralHandlingMode.BIND and it is not working.
I did not dig any further but at
https://github.com/hibernate/hibernate-orm/blob/06c78087ff66f6d2f24408169da5510f2e88a549/hibernate-core/src/main/java/org/hibernate/query/internal/QueryParameterBindingsImpl.java#L509-L511
the parameterListBindingMap is null. So it never gets to the code which does the parameter padding.
It could be a bug. You should create a Jira issue for it.