How to improve statement caching efficiency with IN clause parameter padding
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Introduction
Recently, I stumbled on the following Twitter thread:
A lesser known jOOQ feature that developers don't know about, but DBAs will rejoice!
— jOOQ (@JavaOOQ) April 11, 2018
Automatic IN-list padding (to prevent contention on execution plan caches / too many hard parses)https://t.co/CNa7kd5rAr
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(); }
When running the following test case:
assertEquals( 3, getPostByIds(entityManager, 1, 2, 3).size() ); assertEquals( 4, getPostByIds(entityManager, 1, 2, 3, 4).size() ); assertEquals( 5, getPostByIds(entityManager, 1, 2, 3, 4, 5).size() ); assertEquals( 6, getPostByIds(entityManager, 1, 2, 3, 4, 5, 6).size() );
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
<property> name="hibernate.query.in_clause_parameter_padding" value="true" </property>
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.
Cool, right?
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
And there is more!
You can earn a significant passive income stream from promoting all these amazing products that I have been creating.
If you're interested in supplementing your income, then join my affiliate program.
Conclusion
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.
