A beginner’s guide to the Hibernate JPQL and Native Query Plan Cache

Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

Introduction

Every JPQL query must be compiled prior to being executed, and, because this process might be resource-intensive, Hibernate provides a QueryPlanCache for this purpose.

For entity queries, the query String representation is parsed into an AST (Abstract Syntax Tree). For native queries, the parsing phase cannot compile the query, so it only extracts information about the named parameters and query return type.

Configuration

The query plan cache is shared by both entity and native queries, and its size is controlled by the following configuration property:

<property 
    name="hibernate.query.plan_cache_max_size" 
    value="2048"
/>

By default, the QueryPlanCache stores 2048 plans which is sufficient for many small and medium-sized enterprise applications.

For native queries, the QueryPlanCache stores also the ParameterMetadata which holds info about parameter name, position, and associated Hibernate type. The ParameterMetadata cache is controlled via the following configuration property:

<property 
    name="hibernate.query.plan_parameter_metadata_max_size" 
    value="128"
/>

If the application executes more queries than the QueryPlanCache can hold, there is going to be a performance penalty due to query compilation.

Testing time

Assuming we have the following entities in our application:

@Entity(name = "Post")
@Table(name = "post")
public class Post {

    @Id
    private Long id;

    private String title;

    @OneToMany(
        mappedBy = "post",
        cascade = CascadeType.ALL,
        orphanRemoval = true
    )
    private List<PostComment> comments = new ArrayList<>();

    public void addComment(PostComment comment) {
        comments.add(comment);
        comment.setPost(this);
    }
    
    //Getters and setters omitted for brevity
}

@Entity(name = "PostComment")
@Table(name = "post_comment")
public class PostComment {

    @Id
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    private Post post;

    private String review;

    //Getters and setters omitted for brevity
}

We are going to measure the compile phase for the following JPQL and native queries:

protected Query getEntityQuery1(EntityManager entityManager) {
    return entityManager.createQuery(
        "select new " +
        "   com.vladmihalcea.book.hpjp.hibernate.fetching.PostCommentSummary( " +
        "       p.id, p.title, c.review ) " +
        "from PostComment c " +
        "join c.post p")
    .setFirstResult(10)
    .setMaxResults(20)
    .setHint(QueryHints.HINT_FETCH_SIZE, 20);
}

protected Query getEntityQuery2(EntityManager entityManager) {
    return entityManager.createQuery(
        "select c " +
        "from PostComment c " +
        "join fetch c.post p " +
        "where p.title like :title"
    );
}

protected Query getNativeQuery1(EntityManager entityManager) {
    return entityManager.createNativeQuery(
        "select p.id, p.title, c.review * " +
        "from post_comment c " +
        "join post p on p.id = c.post_id ")
    .setFirstResult(10)
    .setMaxResults(20)
    .setHint(QueryHints.HINT_FETCH_SIZE, 20);
}

protected Query getNativeQuery2(EntityManager entityManager) {
    return entityManager.createNativeQuery(
        "select c.*, p.* " +
        "from post_comment c " +
        "join post p on p.id = c.post_id " +
        "where p.title like :title")
    .unwrap(NativeQuery.class)
    .addEntity(PostComment.class)
    .addEntity(Post.class);
}

The measurements are going to be done as follows:

protected void compileQueries(
        Function<EntityManager, Query> query1,
        Function<EntityManager, Query> query2) {

    LOGGER.info("Warming up");

    doInJPA(entityManager -> {
        for (int i = 0; i < 10000; i++) {
            query1.apply(entityManager);
            
            query2.apply(entityManager);
        }
    });

    LOGGER.info(
        "Compile queries for plan cache size {}", 
        planCacheMaxSize
    );

    doInJPA(entityManager -> {
        for (int i = 0; i < 2500; i++) {
            long startNanos = System.nanoTime();
            
            query1.apply(entityManager);
            
            timer.update(
                System.nanoTime() - startNanos, 
                TimeUnit.NANOSECONDS
            );

            startNanos = System.nanoTime();
            
            query2.apply(entityManager);
            
            timer.update(
                System.nanoTime() - startNanos, 
                TimeUnit.NANOSECONDS
            );
        }
    });

    logReporter.report();
}

And, the JUnit test methods can simply call the compileQueries method like this:

@Test
public void testEntityQueries() {
    compileQueries(
        this::getEntityQuery1, 
        this::getEntityQuery2
    );
}

@Test
public void testNativeQueries() {
    compileQueries(
        this::getNativeQuery1, 
        this::getNativeQuery2
    );
}

The plan cache size is going to be varied using the @Parameterized JUnit feature:

private final int planCacheMaxSize;

public PlanCacheSizePerformanceTest(
        int planCacheMaxSize) {
    this.planCacheMaxSize = planCacheMaxSize;
}

@Parameterized.Parameters
public static Collection<Integer[]> rdbmsDataSourceProvider() {
    List<Integer[]> planCacheMaxSizes = new ArrayList<>();
    
    planCacheMaxSizes.add(new Integer[] {1});
    planCacheMaxSizes.add(new Integer[] {100});
    
    return planCacheMaxSizes;
}

@Override
protected void additionalProperties(
        Properties properties) {
    properties.put(
        "hibernate.query.plan_cache_max_size",
        planCacheMaxSize
    );

    properties.put(
        "hibernate.query.plan_parameter_metadata_max_size",
        planCacheMaxSize
    );
}

So, we will vary the QueryPlanCache and the ParameterMetadata cache size from 1 to 100. When the plan cache size is 1, the queries are always going to be compiled while when the plan cache size is 100, the query plans will be served from the cache.

When running the aforementioned unit tests, we’ll get the following results.

JPQL entity query plan cache performance

As you can clearly see, JPQL entity queries can greatly benefit the query plan cache, and that’s why you should make sure the hibernate.query.plan_cache_max_size can accommodate the vast majority of entity queries your application needs to run.

The QueryPlanCache affects both JPQL and Criteria API queries since Criteria queries are translated to JPQL.

Native query plan cache performance

While not as spectacular as for JPQL queries, the query plan cache can speed up native queries as well so make sure you set the hibernate.query.plan_parameter_metadata_max_size configuration property right.

Named queries

There is no obvious performance gain for using named queries over dynamic ones because, behind the scenes, a named query is able to cache only its definition (e.g. NamedQueryDefinition), and the actual query plan cache is available for both dynamic and named queries.

The most important settings that you need to take into consideration are the ones that govern the Hibernate query plan cache.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

For entity queries, the plan cache can really make a difference in terms of performance. For native queries, the gain is less significant.

The plan cache storing both entity and native queries, it is important to adjust its size so that it can accommodate all queries being executed. Otherwise, some entity queries might have to be recompiled, therefore increasing the current transaction response time.

FREE EBOOK

5 Comments on “A beginner’s guide to the Hibernate JPQL and Native Query Plan Cache

  1. Query Plan cache can easily cause OOM when using large IN clauses. Some of our HQLQueryPlan objects show a retained size of over 2MB

    *2048 plans = 4Gb worst case.

  2. Hi Vlad

    We see an issue where we are using lists to populate an in clause, i.e. using spring JPA we have something like this:

    findByUuidIn(List uuids)

    This is fine with small lists but if we start using lists with element counts in the thousands we start to see memory issues where the QueryPlanCache starts getting very large. We end up with one cache record with 1 parameter, another with 2 etc…

    One obvious solution is to limit the cache size to something much smaller however this means that the a lot of our lookups will cache miss as we have large numbers of queries. Ideally I’d like to keep the cache as we use it for hundreds of other queries but not for a small number of queries which use large lists for in clauses.

    Something like:

    @QueryHint(name = “org.hibernate.cacheable”, value = “false”)

    Would be ideal but I dont think something like that exists for the QueryPlanCache.

    Are there any workarounds I have not thought of?

    Thanks
    Eoin

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.