How to detect and fix the N+1 problem for Java Spring framework applications

We’re going to demonstrate how to identify the N+1 Query anti-pattern using the AppMap plugin for IntelliJ, and the Spring PetClinic sample application as an example.

The N+1 antipattern happens when a query is executed for every result of a previous query. If left unchecked, N+1 queries result in the gradual slowdown of the performance of an application, as they result in multiple expensive trips to the database.

N+1 anti-pattern

Detecting N+1 queries is tricky, because they aren’t revealed by any mainstream testing strategy.

To detect the N+1 query problems, we need to look at the number of SQL queries that are performed in a realistic situation, such as an integration test, and compare this number with a reasonable expectation.

Here’s a brief demo that shows how AppMap can be used to quickly identify the N+1 anti-pattern in a Spring application and validate a fix.

(If you prefer to read vs. watch, a written version of the demo follows the video):

0:15 Illustration of the N+1 anti-pattern
0:52 We’ll demonstrate the problem in the Spring PetClinic application — a popular sample Spring framework application implemented in Java.
AppMap works by recording the code execution paths of your app. We’ll be recording the application when it runs in the dev environment.
1:12 First, we’ll run the AppMap Installer to configure the project:
```
  $ npx @appland/appmap install-agent
```
1:19 Next, we’ll add the appmap Java agent to the run configuration and start the application in IntelliJ. When the app has started, we’re ready to record an AppMap.
1:35 We’ll start the AppMap recording right in the IDE with Tools -> AppMap -> Start AppMap Recording, then open the list of vets in the PetClinic application running locally on port 8080.
1:42 Now we’ll stop the recording, give the new AppMap a name and the AppMap now opens in the IDE.
1:50 In the left sidebar of the AppMap itself, we can see the packages, classes and functions that are exercised as part of the test, as well as the SQL queries.
2:00 We see about a half dozen queries in the AppMap. We could now run a detailed analysis of the results. But since our example is very simple, we can conclude the result by working with the AppMap Diagrams directly.

2:15 The SQL sequence shows the hallmark sign of the N+1 anti-pattern: a SQL SELECT fetching all vet records followed by a sequence of SELECTs, each fetching the specialty details for one vet. That’s 1+N total queries that should be replaced with just one or two queries fetching the data set and its detailed records in as few trips to the database as possible.

  select
    vet0_.id as id1_5_,
    vet0_.first_name as first_na2_5_,
    vet0_.last_name as last_nam3_5_
  from
    vets vet0_

  select
    specialtie0_.vet_id as vet_id1_4_0_,
    specialtie0_.specialty_id as specialt2_4_0_,
    specialty1_.id as id1_2_1_,
    specialty1_.name as name2_2_1_
  from
    vet_specialties specialtie0_
    inner join specialties specialty1_ on specialtie0_.specialty_id = specialty1_.id
  where
    specialtie0_.vet_id = ?

The second SQL command was executed six times by the Spring framework.

2:43 Now that we’ve identified that our data model indeed suffers from the N+1 anti-pattern, let’s fix and validate it. The Vet model reveals that a many-to-many relationship exists between the Vet and Specialty entities:

  @Entity
  @Table(name = "vets")
  public class Vet extends Person {

     @ManyToMany(fetch = FetchType.EAGER)
     @JoinTable(name = "vet_specialties", joinColumns = @JoinColumn(name = "vet_id"),
             inverseJoinColumns = @JoinColumn(name = "specialty_id"))
     private Set<Specialty> specialties;

3:00 The easiest way to fix the N+1 issue is to tell the framework to fetch the details with a subselect using the Fetch annotation:

  @Entity
  @Table(name = "vets")
  public class Vet extends Person {

     @ManyToMany(fetch = FetchType.EAGER)
     @Fetch(FetchMode.SUBSELECT)
     @JoinTable(name = "vet_specialties", joinColumns = @JoinColumn(name = "vet_id"),
             inverseJoinColumns = @JoinColumn(name = "specialty_id"))
     private Set<Specialty> specialties;

3:14 Let’s record a new AppMap and compare the SQL commands in the Trace.

3:23 Success! As you can see, the sequence of 6 SELECTs has been replaced by one SELECT that fetches all related specialties in a single trip to the database. We’re done with Vets and can move on to the next data entity.

  select
    vet0_.id as id1_5_,
    vet0_.first_name as first_na2_5_,
    vet0_.last_name as last_nam3_5_
  from
    vets vet0_

  select
    specialtie0_.vet_id as vet_id1_4_1_,
    specialtie0_.specialty_id as specialt2_4_1_,
    specialty1_.id as id1_2_0_,
    specialty1_.name as name2_2_0_
  from
    vet_specialties specialtie0_
    inner join specialties specialty1_ on specialtie0_.specialty_id = specialty1_.id
  where
    specialtie0_.vet_id in (
      select
        vet0_.id
      from
        vets vet0_
    )

We’ve demonstrated how to find and eliminate the hard-to-detect N+1 pattern with AppMap in just a few minutes. As a result, the application is now better prepared for heavy production load.

Questions or feedback? We’d love to hear from you – reach out in Slack or Twitter!

Posted 04 Oct 2021 by Petr and Lizzie