Speed-up your Query with Parameter Padding

---

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Join the Persistence Hub!

---

Most databases put a lot of effort into analyzing SQL statements and finding the optimal execution plan. This often makes the difference between a slow and a blazing fast statement. But this analysis can also be rather complex. That’s why a lot of databases try to cache and reuse the created execution plans.

Hibernate and Spring Data JPA help using this cache efficiently. Because some queries get generated, you don’t need to worry about creating them always in the same way. You can take it one step further by using bind parameters. This separates the static from the variable parts of your statement and makes it easier to identify identical queries.

TypedQuery<Book> q = em.createQuery("SELECT b FROM Book b WHERE b.title = :title", Book.class);
q.setParameter("title", "Hibernate Tips - More than 70 solutions to common Hibernate problems");
q.getResultList();

19:46:38,893 DEBUG [org.hibernate.SQL] - 
    select
        book0_.id as id1_0_,
        book0_.title as title2_0_ 
    from
        Book book0_ 
    where
        book0_.title=?

This works well for all bind parameters except for Lists used in IN clauses. When using a List as a bind parameter value, Hibernate has to generate a bind parameter for each element in that List.

TypedQuery<Book> q = em.createQuery("SELECT b FROM Book b WHERE b.id IN (:ids)", Book.class);
q.setParameter("ids", Arrays.asList(new Long[]{1L, 2L, 3L}));
q.getResultList();

The varying number of bind parameter values can create a huge number of different statements. Your database needs to analyze and prepare an execution plan for each of them. This reduces the efficiency of its statement cache.

19:49:05,198 DEBUG [org.hibernate.SQL] - 
    select
        book0_.id as id1_0_,
        book0_.title as title2_0_ 
    from
        Book book0_ 
    where
        book0_.id in (
            ? , ? , ?
        )

IN Clause Parameter Padding

In version 5.2.18, Hibernate introduced a simple but very efficient feature to reduce the number of generated statements. It’s called parameter padding. You can activate it by setting the property hibernate.query.in_clause_parameter_padding in your persistence.xml to true.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<persistence>
    <persistence-unit name="my-persistence-unit">
        ...
		
        <properties>
            <property name="hibernate.query.in_clause_parameter_padding" value="true" />
			
            ...
        </properties>
    </persistence-unit>
</persistence>

Hibernate then no longer generates SQL statements with the exact number of required bind parameters. It pads the bind parameters to the next power of 2.

If your List contains 2 elements, Hibernate generates an IN clause with 2 bind parameters. If your List has 3 or 4 items, Hibernate generates an IN clause with 4 bind parameters. And if your List contains 5 to 8 elements, Hibernate generates an IN clause with 8 bind parameters.

TypedQuery<Book> q = em.createQuery("SELECT b FROM Book b WHERE b.id IN (:ids)", Book.class);
q.setParameter("ids", Arrays.asList(new Long[]{1L, 2L, 3L}));
q.getResultList();

If you execute the same test case as before, Hibernate generates a statement with 4 instead of the previously used 3 bind parameter values.

19:53:25,757 DEBUG [org.hibernate.SQL] - 
    select
        book0_.id as id1_0_,
        book0_.title as title2_0_ 
    from
        Book book0_ 
    where
        book0_.id in (
            ? , ? , ? , ?
        )
19:53:25,760 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [1] as [BIGINT] - [1]
19:53:25,761 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [2] as [BIGINT] - [2]
19:53:25,761 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [3] as [BIGINT] - [3]
19:53:25,761 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [4] as [BIGINT] - [3]

Hibernate then, of course, needs to provide a value for each bind parameter. For each of the padded bind parameters, Hibernate reuses the last element’s value in the provided List.

As you can see, this drastically reduces the number of different statements that your database needs to analyze. This improves the effectiveness of the database-internal cache and can improve the performance of your queries.

When not to use it

Parameter padding might seem like a great feature but there are a few situations in which you should avoid it:

1. If your database doesn’t cache execution plans, you will not benefit from reducing the number of different statements. The additional bind parameters then only increase the complexity of the statement and might slow down the query.
2. If your List contains only a very small number of elements, the parameter padding will not have a huge effect and you might consider deactivating it.
3. If your List contains a very large number of elements, parameter padding might drastically increase the number of parameters in your IN clause. A List with 129 elements, for example, gets padded to 256 parameters. In these situations, you should check if the overhead of the padded parameters starts to outweigh the benefits of the more efficient database cache.

Conclusion

When using an IN clause with a varying number of bind parameters, your database can have a hard time caching the required execution plans.

Since Hibernate 5.2.18, you can reduce the number of queries by setting the hibernate.query.in_clause_parameter_padding property to true. Hibernate then pads the bind parameters in the IN clause to the next power of 2. This reduces the number of different statements, especially if you often call the query with a huge number of elements in the IN clause.