51

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

 5 years ago
source link: https://www.tuicool.com/articles/hit/jaE3qqv
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

(Last Updated On: July 4, 2018)

Follow @vlad_mihalcea

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

6FvqYj7.png!web

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

ziEjqa6.png!web

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.

IzMJFjb.jpg!web3m2Yrem.jpg!web

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.


About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK