Performance1 - SpotBugsExtensionForSpringFrameWork/CS5098 GitHub Wiki

Bug Pattern Name: JPA_N_PLUS_ONE_PROBLEM
Short Description: Spring JPA N+1 Problem

Description

Recommended fetch plan in JPA is lazy loading which loads every association and collection on demand, lazily. This fetch plan will most likely result in too many SQL statements, each loading only one small piece of data (minimized SQL query should be our goal for efficiency). This will lead to n+1 selects problems. On the other side, using eager loading can make fewer SQL statements, the chunk of data loaded in memory would be so huge which can lead to less optimization for searching data (also consider Cartesian product problem).

The N+1 query problem happens when the data access framework executed N additional SQL statements to fetch the same data that could have been retrieved when executing the primary SQL query.

Example code

List<Item> items = em.createQuery("select i from Item i").getResultList();
// select * from ITEM
for( Item item :items) {
    assertNotNull(item.getSeller().getUsername());
    // select * from USERS where ID = ?
}

Code description: You see one SQL SELECT to load the Item entity instances. Then, while you iterate through all the items, retrieving each User requires an additional SELECT. This amounts to one query for the Item plus n queries depending on how many items you have and whether a particular User is selling more than one Item. Obviously, this is a very inefficient strategy if you know you’ll access the seller of each Item.
With what you know so far, you might be tempted to change the default fetch plan in your mappings and put a FetchType.EAGER on your seller or bids associations. But doing so can lead to our next topic: the Cartesian product problem.

Solution

  • Join Fetch
  • @EntityGraph

Reference List

⚠️ **GitHub.com Fallback** ⚠️