Performance of Fetching Data with JPQL

Jakarta Persistence API (formerly Java Persistence API) is a standard approach to access a database in the Java world. Even though its usage is simple, developers sometimes struggle to achieve best performance. In this blog post, I’m going to showcase different JPQL techniques in fetching aggregated data along with their execution times.

Example JPA Model

In our example we focus on Employee and how to calculate a sum of salaries of employees by country.

@Entity 
class Employee {
    @Id @GeneratedValue
    private Long id;
    private String firstName;
    private String lastName;
    private BigDecimal salary;
    @OneToOne @JoinColumn(name = "address_id")
    private Address address;
    @Temporal(TemporalType.DATE)
    private Date startDate;
    @Temporal(TemporalType.DATE)
    private Date endDate;
    @ManyToOne @JoinColumn(name = "manager_id")
    private Employee manager;  
    // …
}

Sum of Salaries by Country – Select All

TypedQuery<Employee> query = em.createQuery(
    "SELECT e FROM Employee e", Employee.class);
List<Employee> list = query.getResultList();

// calculate sum of salaries by country
// map: country->sum
Map<String, BigDecimal> results = new HashMap<>();
for (Employee e : list) {    
    String country = e.getAddress().getCountry();    
    BigDecimal total = results.get(country);    
    if (total == null) total = BigDecimal.ZERO;    
    total = total.add(e.getSalary());    
    results.put(country, total);
}

Sum of Salaries by Country – Select Join Fetch

TypedQuery<Employee> query = em.createQuery(
    "SELECT e FROM Employee e 
     JOIN FETCH e.address", Employee.class);
List<Employee> list = query.getResultList();

// calculate sum of salaries by country
// map: country->sum
Map<String, BigDecimal> results = new HashMap<>();
for (Employee e : list) {    
    String country = e.getAddress().getCountry();    
    BigDecimal total = results.get(country);    
    if (total == null) total = BigDecimal.ZERO;    
    total = total.add(e.getSalary());    
    results.put(country, total);
}

Sum of Salaries by Country – Projection

Query query = em.createQuery(
    "SELECT e.salary, e.address.country 
     FROM Employee e");
List<Object[]> list = (List<Object[]>) query.getResultList();

// calculate sum of salaries by country
// map: country->sum
Map<String, BigDecimal> results = new HashMap<>();
for (Object[] e : list) {    
    String country = (String) e[1];    
    BigDecimal total = results.get(country);    
    if (total == null) total = BigDecimal.ZERO;    
    total = total.add((BigDecimal) e[0]);    
    results.put(country, total);
}

Sum of Salaries by Country – Aggregation JPQL

Query query = em.createQuery(
    "SELECT SUM(e.salary), e.address.country 
     FROM Employee e 
     GROUP BY e.address.country");
List<Object[]> list = (List<Object[]>) query.getResultList();

// already calculated!

Performance Comparison

For our performance comparison we use databases populated with 100000 employees and databases are located in different regions to illustrate the impact of latency on the execution times.

	Local DB (ping: ~0.05ms)	North California (ping: ~38ms)	EU Frankfurt (ping: ~420ms)
(1) Select All  (N+1 Problem!)	26756ms	~2-3 hours	~20-24 hours
(2) Select Join Fetch	4854ms	18027ms	25096ms
(3) Projection	653ms	2902ms	5006ms
(4) Aggregation JPQL	182ms	353ms	1198ms