A beginner’s guide to the Hibernate JPQL and Native Query Plan Cache
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
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.
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.
