Improve performance in JPA

Question

I am trying to improve the following code removing any potential bottlenecks:

public List<PostDTO> getOrderDetails(int pageNumber) {
    List<Order> orders = entityManager
            .createQuery(“SELECT o FROM Order o”)
            .setFirstResult((pageNumber - 1) * 20)
            .setMaxResults(20)
            .getResultList();
    List<OrderDTO> result = new ArrayList(orders.size());

    for(Order order : orders) {
        OrderDTO orderDto = new OrderDTO();
        orderDto.setId(order.getId());
        orderDto.setTitle(order.getDate());
        orderDto.setTopicName(order.getDetail().getProductId());
        result.add(orderDto);
    }

    return result;
}

As per the following thread: What is the "N+1 selects problem" in ORM (Object-Relational Mapping)?, there could be a N+1 select issue, so I am using a join fetch this way:

    List<Order> orders = entityManager
            .createQuery(“SELECT o FROM Order o join fetch o.detail”)
            .setFirstResult((pageNumber - 1) * 20)
            .setMaxResults(20)
            .getResultList();
    List<OrderDTO> result = new ArrayList(orders.size());

Is this a good improvement? Is there another way to improve how I am querying using JPQL?

Looks good; the best way to know is to run it, command Hibernate to log the actual SQL queries and, if still in doubt, test the performance of the code. — Nikos Paraskevopoulos, Apr 09 '21 at 15:10
Join fetch over a 1:M means M times more rows being returned in your fetch. Performance of that entirely depends on the data being returned - a huge clob in the Order table being returned M times might make things much worse than just issuing 20 extra queries for details outside of the main page. You will need to test and experiment to figure out what is best for your usage. — Chris, Apr 09 '21 at 19:28

score 1 · Answer 1 · answered Apr 14 '21 at 11:46

There is even a better way. Just select the attributes/columns that you actually need.

I think this is a perfect use case for Blaze-Persistence Entity Views.

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

A DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:

@EntityView(Order.class)
public interface OrderDTO {
    @IdMapping
    Long getId();
    Date getDate();
    @Mapping("detail.productId")
    Integer getProductId();
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

OrderDTO a = entityViewManager.find(entityManager, OrderDTO.class, id);

The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

Page<OrderDTO> findAll(Pageable pageable);

The best part is, it will only fetch the state that is actually necessary!

Improve performance in JPA

1 Answers1