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.