How to avoid N+1 queries with Spring Data REST Projections?

Question

I have been prototyping my new application in Spring Data REST backed by Spring Data JPA & Hibernate, which has been a fantastic productivity booster for my team, but as the data model becomes more complex the performance is going down the tubes. Looking at the executed SQL, I see two separate but related problems:

1. When using a Projection with only a few properties to reduce the size of my payload, SDR is still loading the entire entity graph, with all the overhead that incurs. EDIT: filed DATAREST-1089

2. There seems to be no way to specify eager loading using JPA, as SDR auto-generates the repository methods so I can't add @EntityGraph to them. (and per DATAREST-905 below, even that doesn't work) EDIT: addressed in Cepr0's answer below, though this can only be applied to each finder method once. See DATAJPA-749

I have one key model that I use several different projections of depending on the context (list page, view page, autocomplete, related item page, etc), so implementing one custom ResourceProcessor doesn't seem like a solution.)

Has anyone found a way around these problems? Otherwise anyone with a non-trivial object graph will see performance deteriorate drastically as their model grows.

My research:

• How do I avoid n+1 queries with Spring Data Rest? (from 2013)
• https://jira.spring.io/browse/DATAJPA-466 ( Add support for lazy loading configuration via JPA 2.1 fetch-/loadgraph.)
• https://jira.spring.io/browse/DATAREST-905 ( No way to avoid loading all child relations in spring-data-rest?) (2016, unanswered)

Answer 1

To fight with 1+N issue I use the following two approaches:

@EntityGraph

I use '@EntityGraph' annotation in Repository for findAll method. Just override it:

@Override
@EntityGraph(attributePaths = {"author", "publisher"})
Page<Book> findAll(Pageable pageable);

This approach is suitable for all "reading" methods of Repository.

Cache

I use cache to reduce the impact of 1+N issue for complex Projections.

Suppose we have Book entity to store the book data and Reading entity to store the information about the number of readings of a specific Book and its reader rating. To get this data we can make a Projection like this:

@Projection(name = "bookRating", types = Book.class)
public interface WithRatings {

    String getTitle();
    String getIsbn();

    @Value("#{@readingRepo.getBookRatings(target)}")
    Ratings getRatings();
}

Where readingRepo.getBookRatings is the method of ReadingRepository:

@RestResource(exported = false)
@Query("select avg(r.rating) as rating, count(r) as readings from Reading r where r.book = ?1")
Ratings getBookRatings(Book book);

It also return a projection that store "rating" info:

@JsonSerialize(as = Ratings.class)
public interface Ratings {

    @JsonProperty("rating")
    Float getRating();

    @JsonProperty("readings")
    Integer getReadings();
}

The request of /books?projection=bookRating will cause the invocation of readingRepo.getBookRatings for every Book which will lead to redundant N queries.

To reduce the impact of this we can use the cache:

Preparing the cache in the SpringBootApplication class:

@SpringBootApplication
@EnableCaching
public class Application {

    //...

    @Bean
    public CacheManager cacheManager() {

        Cache bookRatings = new ConcurrentMapCache("bookRatings");

        SimpleCacheManager manager = new SimpleCacheManager();
        manager.setCaches(Collections.singletonList(bookRatings));

        return manager;
    }
}

Then adding a corresponding annotation to readingRepo.getBookRatings method:

@Cacheable(value = "bookRatings", key = "#a0.id")
@RestResource(exported = false)
@Query("select avg(r.rating) as rating, count(r) as readings from Reading r where r.book = ?1")
Ratings getBookRatings(Book book);

And implementing the cache eviction when Book data is updated:

@RepositoryEventHandler(Reading.class)
public class ReadingEventHandler {

    private final @NonNull CacheManager cacheManager;

    @HandleAfterCreate
    @HandleAfterSave
    @HandleAfterDelete
    public void evictCaches(Reading reading) {
        Book book = reading.getBook();
        cacheManager.getCache("bookRatings").evict(book.getId());
    }
}

Now all subsequent requests of /books?projection=bookRating will get rating data from our cache and will not cause redundant requests to the database.

More info and working example is here.