5 tips to write efficient queries with JPA and Hibernate

---

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Join the Persistence Hub!

---

A lot of developers seem to have a love-hate relationship with JPA and Hibernate. They love it because it makes it easy to implement most use cases and they hate it because it also makes it easy to implement very inefficient queries.

In the past, I was one of these developers myself. I liked to use Hibernate, but I also struggled with slow database queries and other performance issues. That changed over the years when I spend more and more time working with and teaching about Hibernate.

When you analyze enough inefficient queries, you recognize that you just have to follow a few simple recommendations to avoid most of them.

5 tips to write efficient queries

1. Choose a projection that fits your use case

This tip is as obvious as it is important: You should always use a projection that fits your use case.

It’s pretty obvious, isn’t it?

All students are nodding their heads when I recommend this in my Hibernate performance tuning training. But that most often changes, when we talk about the details. You have to decide for each use case, which information it needs and which operations it has to perform and choose accordingly.

Entities are a good fit if you have to update or remove a record. They might also be ok for use cases which need to read (almost) all entity attributes. But keep in mind, that the persistence context has to manage the entities which creates an overhead compared to a DTO projection.

DTO’s are a good fit for use cases that only need to read a record if they provide all required and no additional properties. That often requires you to create a new DTO when you implement a new use case. That is where most discussions start. You can’t reuse the same DTO and data access services for all use cases if you want to optimize for efficiency.

But don’t worry, this doesn’t have to be a black and white decision. Most development teams decide to do a little bit of both. They accept minor inefficiencies in their database access and create DTOs that are quite a good but not an optimal fit for multiple use cases to improve reusability. That’s totally fine. You just have to be aware of it so that you can change it if you into performance issues.

2. Avoid eager fetching in your mapping definition

From a performance point of view, choosing the right FetchTypes for your entity associations is one of the most important steps. The FetchType defines when Hibernate performs additional queries to initialize an association. It can either do that when it loads the entity (FetchType.EAGER) or when you use the association (FetchType.LAZY).

It doesn’t make any sense to perform additional queries to load data before you know that you need it. You should use FetchType.LAZY by default and apply the next tip if a use case uses an entity association.

3. Initialize all required associations in your query

The FetchType.LAZY avoids additional queries which initialize associations which you don’t use in your use case. That’s obviously a good thing but what do you do if your use case needs one of these associations?

The easiest thing you can do is to call the getter method of the association. Hibernate will then perform another query to fetch the required information from the database. This is the easiest but also the most inefficient approach to initialize a lazy entity association. When you do that for multiple entities, you will face another performance issue, called the n+1 select issue.

You can easily avoid that by initializing all required associations within the query that loads your entities. You can either do that with a query-independent EntityGraph or with a simple JOIN FETCH clause in your JPQL or Criteria Query.

4. Use pagination when you select a list of entities

When you fetch huge lists of entities or DTOs, you should always ask yourself, if you really need all of them. If you show them to your user, the answer is most often: NO!

Humans can’t handle lists with hundreds of elements. Most UIs, therefore, split them into multiple chunks and present each of them on a separate page.

In these cases, it doesn’t make any sense to fetch all entities or DTOs in one query. The UI doesn’t need them, and it just slows down your application. It’s much better to use the same pagination approach in your query and fetch only the records that are shown in the UI. You can do that by setting appropriate values for firstResult and maxResult on the Query interface.

5. Log SQL statements

If you apply all of the previous tips, you already avoid the most common pitfalls. But from time to time, you will still create inefficient queries without recognizing it. JPA and Hibernate hide all SQL statements behind JPQL, the EntityManager, and the Criteria API. That makes them easy to use, but it also gets hard to understand all implications of your API calls.

You should, therefore, always check the executed SQL statements when you apply any changes to your code. The easiest way to do that is to activate the logging of SQL statements in your development configuration. You can do that by setting the log level of org.hibernate.SQL to DEBUG.

You can also activate Hibernate Statistics to get more detailed information about the current session. It shows you a lot of useful information like the number of performed queries, the time spent on them and the number of cache interactions.

All these information allow you to identify inefficient queries during development so that you can fix them before they cause trouble in production.

Summary

JPA and Hibernate make it easy to implement most use cases, but they also make it easy to create inefficient queries. You can avoid most of these issues when you follow a few simple recommendations. You should:

• use a projection that selects only the information you need,
• use FetchType.LAZY to fetch associations only when you need them,
• initialize all required associations when you load an entity,
• use pagination when you work with huge lists of entities or DTOs and
• check the generated SQL statements when you make any changes to your code.

These were my 5 tips to write efficient database queries with Hibernate. If you like this post, you should also have a look at my Hibernate Performance Tuning Online Training in which I also show you how to improve write operations, use caching to avoid unnecessary queries and manage concurrency.