Enhance JPA query performance with projections and Records

Use projections in Spring Boot's JPA to enhance application performance by optimizing database queries. It compares the performance of fetching all fields of an Product entity with 50 attributes versus using a projection to retrieve only a subset of necessary fields. The Product entity represents a database table with potentially large rows

Database access is a critical performance bottleneck and efficient queries are essential for system performance. Projections are presented as a valuable tool for improving query performance without altering the database structure.

we’ll use a Product entity with 50 attributes, which would make each row of this table several bytes in size.

@Entity
@Getter
@Setter
public class Product {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  private String name;
  private String description;
  private String category;
  private String brand;

  private Double price;
  private Double discount;

  private Integer stockQuantity;

  private String sku;

  private Double weight;

  private String dimensions;
  private String color;
  private String size;
  private String material;
  private String manufacturer;
  private String supplier;
  private String warranty;

  private Double rating;

  private Integer reviews;

  private String imageUrl;
  private String thumbnailUrl;
  private String releaseDate;
  private String expiryDate;
  private String barcode;
  private String countryOfOrigin;

  private Boolean isActive;
  private Boolean isFeatured;

  private String metaTitle;
  private String metaDescription;
  private String metaKeywords;
  private String createdAt;
  private String updatedAt;
  private String deletedAt;

  private Boolean availableOnline;
  private Boolean availableInStore;

  private Double shippingCost;
  private Double taxRate;

  private String returnPolicy;
  private String userManualUrl;
  private String safetyInformation;
  private String videoUrl;

  private Boolean assemblyRequired;

  private String assemblyTime;
  private String relatedProducts;
  private String tags;

  private Boolean customizable;

  private String leadTime;

  private Integer minimumOrderQuantity;
  private Integer maximumOrderQuantity;
}        

A simple repository for it looks like this:

@Repository
public interface ProductRepository extends CrudRepository<Product, Long> {
}        

A simple service class and controller to test the execution are:

@RestController
@RequiredArgsConstructor
public class ProductController {
  private final ProductService productService;

  @GetMapping("/entity")
  @ResponseStatus(HttpStatus.OK)
  public void withEntity() {
    productService.queryWithEntity();
  }
}


@Service
@RequiredArgsConstructor
public class ProductService {
  private final ProductRepository productRepository;

  public void queryWithEntity() {
    for (var product : productRepository.findAll()) {
      ...
    }
  }
}        

When calling this API, JPA translates the entity to a query as follows:

select p1_0.id, p1_0.assembly_required, p1_0.assembly_time,
       p1_0.available_in_store, p1_0.available_online, p1_0.barcode,
       p1_0.brand,p 1_0.category, p1_0.color, p1_0.country_of_origin,
       p1_0.created_at, p1_0.customizable, p1_0.deleted_at, p1_0.description,
       p1_0.dimensions, p1_0.discount, p1_0.expiry_date, p1_0.image_url,
       p1_0.is_active, p1_0.is_featured, p1_0.lead_time, p1_0.manufacturer,
       p1_0.material, p1_0.maximum_order_quantity, p1_0.meta_description,
       p1_0.meta_keywords, p1_0.meta_title, p1_0.minimum_order_quantity,
       p1_0.name, p1_0.price, p1_0.rating, p1_0.related_products,
       p1_0.release_date, p1_0.return_policy, p1_0.reviews,
       p1_0.safety_information, p1_0.shipping_cost, p1_0.size, p1_0.sku,
       p1_0.stock_quantity, p1_0.supplier, p1_0.tags, p1_0.tax_rate,
       p1_0.thumbnail_url, p1_0.updated_at, p1_0.user_manual_url,
       p1_0.video_url, p1_0.warranty, p1_0.weight 
  from product p1_0        

As we can notice here, the select statements contain all fields from the database, even if we do not need all of them.

To increase performance, we can query just some fields from the table using a projection. To do this in Spring Boot, we can use a Java record listing only the fields that we need:

public record ProductProjection(
    Long id, String name, String description, String category, String brand) {}        

Using the projection in the repository is as simple as it should be. We just specify the return type as the projection:

@Repository
public interface ProductRepository extends CrudRepository<Product, Long> {
  List<ProductProjection> findAllBy();
}        

The following are the implementations for the service and the controller:

@Service
@RequiredArgsConstructor
public class ProductService {
  private final ProductRepository productRepository;

  public void queryWithEntity() {
    for (var product : productRepository.findAll()) {
      ...
    }
  }

  public void queryWithProjection() {
    for (var product : productRepository.findAllBy()) {
      ...
    }
  }
}


@RestController
@RequiredArgsConstructor
public class ProductController {
  private final ProductService productService;

  @GetMapping("/entity")
  @ResponseStatus(HttpStatus.OK)
  public void withEntity() {
    productService.queryWithEntity();
  }

  @GetMapping("/projection")
  @ResponseStatus(HttpStatus.OK)
  public void withProjection() {
    productService.queryWithProjection();
  }
}        

When calling the projection API, JPA translates it to the following query:

select p1_0.id, p1_0.name, p1_0.description, p1_0.category, p1_0.brand 
  from product p1_0        

As we can see here, JPA is smart enough to include only the fields needed for the projection in the query.

To compare the performance of both implementations (with the entity and with the projection), let’s use the Java Microbenchmark Harness (JMH) to measure the average response time in milliseconds. We will use a database with 100 records, where the select query retrieves all of them.

Benchmark               Mode  Cnt  Score   Error  Units
MyBenchmark.entity      avgt  500  5.613 ± 0.047  ms/op
MyBenchmark.projection  avgt  500  1.522 ± 0.019  ms/op        

We can clearly see that using the projection makes the query more than three times faster than not using it. Therefore, it is definitely worth using projections if your database table: Contains many fields Contains large fields Contains fields that are rarely read Is frequently queried