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

(Last Updated On: July 4, 2018)

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 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.

Subscribe to our Newsletter

* indicates required
10 000 readers have found this blog worth following!

If you subscribe to my newsletter, you'll get:
  • A free sample of my Video Course about running Integration tests at warp-speed using Docker and tmpfs
  • 3 chapters from my book, High-Performance Java Persistence, 
  • a 10% discount coupon for my book. 
Get the most out of your persistence layer!

Advertisements

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

    1. You can enable logging for the Query Cache Plan implementation. I think we should add this to Statistics as well.

  1. Thanks Vlad!. Very helpful topic. We are getting out off memory error due to large IN data set about 3000-4000 ( 15 byte string id ). Is Parameter meta data holds value or only
    “holds info about parameter name, position, and associated Hibernate type”.

    Or Is there any way to by pass QueryPlanCache for particular query ( may be using HINTS ) like search functionality that generate less unique query that have big IN data set in where condition.

    Right now we are doing batch for IN queries. But this is also not useful in such query have two IN query in where condition.

    Please suggest.

    1. Most likely you get the OOM because of how much data you fetch, not because of the IN parameters. Use pagination as you almost never need 4000 records either in the UI or in your batch processing tasks.

      1. Thanks for reply. We are using pagination size of 10 but we required big IN query for filtering data by role based.
        Example :
        Entity – Driver, Vendor, VendorDriverAssoc
        Vendor user – Only his drivers visible
        OrgAdmin user – All vendors driver visible, may be 10 , 20 , 500 , 4000 vendors that attached to org admin.

        Query on driver search – XYZ.
        We search on driver entity and filtering that drivers by putting big IN query (vendorId) in where condition.

        We can avoid big IN query but required more business logic ( right now we are doing).

        We got OOM error after deployment of one day or frequently searches happen.

      2. But why would the OOM be caused by the Query Plan Cache unless you set that to millions of entries?

      3. I am not understand correctly but i saw objects org.hibernate.hql.inetrnal.ast.tree.Node , NamedParameterDescriptor,NamdedParameterInformationImpl,NamedParameterSpecification and ParameterNode
        after heap dump. Can’t understand, can you please explain. If we reduced the parameter_metadata_max_size then it’s works. But we don’t want to reduced this size for performance reason.

      4. What’s the size of the parameter_metadata_max_size configuration parameter?

      5. On prod deployment is default and on development machine i make it to 1 ( Only hitting search query.)

      6. Try to profile it and get an report and then open a new thread on the Hibernate forum: discourse.hibernate.org

  2. Very interesting and helpful tip, Vlad!

    If I’m not wrong in the old versions of Hibernate there were differences in performance between Named Queries and Dynamic ones. This difference was because the dynamic queries needed to be compiled at every single execution. (Although it wasn’t as important as the quality of the query itself.)

    Was that true?

    1. I don’t recall if the cache was added later. But it’s been like this for quite some time.

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.