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.

A beginner’s guide to the Hibernate JPQL and Native Query Plan Cache @vlad_mihalceahttps://t.co/9vf3a4Ty5V pic.twitter.com/mhTDFM9Ifr

— Java (@java) August 29, 2019

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 might not be sufficient for large 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.

I'm running an online workshop on the 11th of October about High-Performance SQL.

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.

Insert details about how the information is going to be processed

DOWNLOAD NOW