This blog article is part of a “series of blog articles” about common pitfalls using JPA and ways to avoid them. In this article, we describe a potential performance problem when loading relationships in a loop.
The following code pattern easily runs into performance problems. It retrieves a list of entity instances from the database and for each entity accesses a number of attributes including lazy loaded relationships.
List<Employee> emps = // Retrieve employees from DB
for (Employee e : emps) {
showEmployeeDetails(
e.getFirstname(), e.getLastname(),
e.getDepartment() == null ?
e.getDepartment().getName() : null,
e.getInsurance() == null ?
e.getInsurance().getCarrier() : null);
}
In case the relationships department and insurance in Employee are defined as lazy loaded, each iteration will execute two database queries.
It is better to load all the required data with a single database query. The query does not return entities, instead it retrieves just the required fields and navigates the relationships as part of the query:
List<Object[]> empDetails = em.createQuery(
"SELECT e.firstname, e.lastname, d.name, i.carrier " +
"FROM Employee e LEFT OUTER JOIN e.department d " +
"LEFT OUTER JOIN e.insurance i").getResultList();
for (Object[] empDetail : empDetails) {
showEmployeeDetails(empDetail[0], empDetail[1],
empDetail[2], empDetail[3]);
}
Recommendation: Use a JPQL query to load specific fields including fields of associated entities.
You can find an example of this pitfall and its solution in the class LoadingRelationshipsExperiment in this project: https://github.com/akquinet/jpapitfalls.git.
You must log in to post a comment.