How can we test for the N+1 problem in JPA/Hibernate?

Question

I have a N+1 problem, and I’d like to write some kind of automated regression test because it impacts performance very much.

I thought about spying the EntityManager and verifying its method createQuery() is called only once, but Hibernate don’t use it to initialize lazy relationships, thus it didn’t work. I could also try to shut down the JPA transaction between my repository and my service (or detach my entity) and look out for exceptions, but it’s really an ugly idea.

To give us a frame, let’s say we have a very simple parent-child model:

@Entity
public class Parent {
    …
    @OneToMany(fetch = FetchType.LAZY, mappedBy = "parent")
    private Collection<Child> children;
}

@Entity
public class Child {
    …
    @ManyToOne
    private Parent parent;
}

And a very simple service:

public class MyService {
    …
    public void doSomething(Long parentId) {
        Parent parent = …; /* retrieve Parent from the database */
        doSomeOtherThing(parent.getChildren());
    }
}

Parent retrieval from database could use the two following queries:

SELECT parent FROM Parent parent WHERE parent.id = :id;
SELECT parent FROM Parent parent JOIN FETCH parent.children WHERE parent.id = :id;

How may I write a test that crashes when I retrieve my Parent entity with the first query, but not the second?

Duplicate https://stackoverflow.com/questions/97197/what-is-the-n1-selects-problem-in-orm-object-relational-mapping — Sambit, Apr 26 '19 at 17:55
@Sambit I am not asking for asolution to the N+1 problem, but for a way to detect it automatically. I’ve read the question you’re referring to, but couldn’t find an answer there. — Rémi Birot-Delrue, Apr 26 '19 at 18:05
You could try to check whether `getChildren()` actually returns a `PersistentCollection` and if so whether `wasInitialized()` returns true or not. If that persistent collection is not initialized then any access is likely to cause lazy loading (there might be cases where this doesn't happen, e.g. when calling `size()` and depending on your mapping - e.g. whether it is extra lazy or not, but it is very likely). — Thomas, Apr 26 '19 at 18:06

score 3 · Answer 1 · answered Apr 26 '19 at 19:35

3

As option you can verify count of queries (fetch, updates, inserts) in the test

 repository.findById(10L);

 SessionFactory sf = em.getEntityManagerFactory().unwrap(SessionFactory.class);
 Statistics statistics = sf.getStatistics();

 assertEquals(2L, statistics.getQueryExecutionCount());

See hibernate statistic

answered Apr 26 '19 at 19:35

kolomiets

87
2

As mentioned in [one SO answer](https://stackoverflow.com/a/9985425/8556340), you should 'enable' statistics first, otherwise nothing will be recorded – Roeniss Nov 25 '21 at 17:54

score 1 · Answer 2 · answered Apr 26 '19 at 19:44

1

Refer to following solution, which relies on wrapping your DataSource https://vladmihalcea.com/how-to-detect-the-n-plus-one-query-problem-during-testing/

answered Apr 26 '19 at 19:44

Lesiak

22,088
2
41
65

score 1 · Answer 3 · answered Mar 26 '21 at 16:48

Another option available to you is to clear the EntityManager after your initial fetch, but before referencing any of the potentially lazy loaded fields on your entity. This effectively disconnects the proxies in place to perform lazy loading and should cause your test to throw an exception if JOIN FETCH wasn't used in the initial query.

Your test would end up looking something like the following (written in Kotlin)

class MyRepositoryTest @Autowired constructor(
    myRepository: MyRepository,
    entityManager: EntityManager
) {
    @Test
    fun `When load children will eagerly fetch`() {
        val parent = myRepository.loadParent()

        entityManager.clear()

        // This line should throw if children are being lazily loaded
        assertThat(parent?.children, equalTo(listOf(Child(1), Child(2))))
    }
}

score 0 · Answer 4 · answered Apr 27 '19 at 05:15

I suppose by "regression test" you mean an actual test probably started by JUnit.

A general way to handle that in a Unit-Test could be:

configure hibernate.show_sql to true
intercept the log-messages like described in intercept.
scan the log-file for
- specific queries, you want to be avoided
- number of similar queries

score 0 · Answer 5 · answered Oct 10 '20 at 14:42

After running the query to retrieve a "parent" entity, using PersistenceUnitUtil, you can assert that "children" have or have not been eagerly loaded:

PersistenceUnitUtil pu = em.getEntityManagerFactory().getPersistenceUnitUtil();
assertTrue(pu.isLoaded(parent, "children"));

Mickaël B. · Answer 6 · 2022-04-20T13:54:18.847

I've written a little library that can assert the count of SQL queries by type (SELECT, INSERT, ..) generated by Hibernate in your Spring tests, this way, you can be warned whenever the SQL statements change in your tests, and prevent N+1 selects. you can take a look here at the project

A test example that demonstrates the purpose:

@Test
@Transactional
@AssertHibernateSQLCount(selects = 1)  // We want 1 SELECT, will warn you if you're triggering N+1 SELECT
void fetch_parents_and_children() {
    parentRepository.findAll().forEach(parent ->
            parent.getChildren().size()
    );
}

How can we test for the N+1 problem in JPA/Hibernate?

6 Answers6