Skip to content

Library giving tools to detect N+1 queries and count the queries generated with Spring and Hibernate

License

Notifications You must be signed in to change notification settings

yannbriancon/spring-hibernate-query-utils

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

95 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

Contributors Forks Stargazers Issues MIT License


Spring Hibernate Query Utils

No more N+1 queries in your Spring applications
Spring Hibernate Query Utils: an easy way of detecting N+1 queries and counting queries in a Spring/Hibernate application

Report Bug · Request Feature

Table of Contents

About The Project

While investigating the performance problems in my SpringBoot application, I discovered the infamous N+1 queries problem that was killing the performance of my services. Check the article Eliminate Spring Hibernate N+1 Queries for more details.

After managing to fix this problem, I had to find a way to detect it and raise the alarm to avoid any developer to introduce new ones.

That is why I created Spring Hibernate Query Utils to provide an easy way of detecting N+1 queries and counting the queries generated in a Spring application using Hibernate.

If you develop Spring applications using Hibernate, you have probably also encountered performance issues caused by N+1 queries.

This library provides several benefits:

  • Kill the N+1 queries problem by throwing an exception when detecting it in your tests
  • Count the exact number of queries generated for each service or resource
  • Improve the onboarding of new developers by making them understand the impact of the N+1 queries problem
  • Improve the debugging by seeing which query is executed and when

Getting Started

Prerequisites

  • JDK 8 or more.

Installation

Maven

Add the dependency to your project inside your pom.xml file with the right version

<dependency>
    <groupId>com.yannbriancon</groupId>
    <artifactId>spring-hibernate-query-utils</artifactId>
    <version>X.X.X</version>
</dependency>

Usage

N+1 Queries Detection

Detection

The N+1 queries detection is enabled by default so no configuration is needed.

Each time N+1 queries are detected in a transaction, a log of level error will be sent.

Two types of N+1 queries are detected:

  • N+1 queries triggered on a getter caused by a field needed but not eager fetched on a specific query

  • N+1 queries triggered on a query caused by an entity field not configured to be fetched lazily

Here is an example catching the error log for the first type of N+1 queries:

@RunWith(MockitoJUnitRunner.class)
@SpringBootTest
@Transactional
class NPlusOneQueriesLoggingTest {

    @Autowired
    private MessageRepository messageRepository;

    @Mock
    private Appender mockedAppender;

    @Captor
    private ArgumentCaptor<LoggingEvent> loggingEventCaptor;

    @BeforeEach
    public void setup() {
        Logger root = (Logger) LoggerFactory.getLogger(Logger.ROOT_LOGGER_NAME);
        root.addAppender(mockedAppender);
    }

    @Test
    void hibernateQueryInterceptor_isDetectingNPlusOneQueriesWhenMissingEagerFetchingOnQuery() {
        // Fetch the 2 messages without the authors
        List<Message> messages = messageRepository.findAll();

        // The getters trigger N+1 queries
        List<String> names = messages.stream()
                .map(message -> message.getAuthor().getName())
                .collect(Collectors.toList());

        verify(mockedAppender, times(2)).doAppend(loggingEventCaptor.capture());

        LoggingEvent loggingEvent = loggingEventCaptor.getAllValues().get(0);
        assertThat(loggingEvent.getMessage())
                .contains("N+1 queries detected on a getter of the entity com.yannbriancon.utils.entity.User\n" +
                        "    at com.yannbriancon.interceptor.NPlusOneQueriesLoggingTest." +
                        "lambda$hibernateQueryInterceptor_isDetectingNPlusOneQueriesWhenMissingEagerFetchingOnQuery$0");
        assertThat(Level.ERROR).isEqualTo(loggingEvent.getLevel());
    }
}
Detection in test with fixtures

If a setup is present in your test to add the data necessary for testing, Hibernate will load all the data in its state. This will hide potential N+1 queries in the method you test.

To avoid this, a method is available to clear the Hibernate state and the N+1 queries detection state.

Here is an example:

@Test
void nPlusOneQueriesDetection_throwsExceptionWhenSessionIsCleared() {
    User author = new User("author");
    userRepository.saveAndFlush(author);
    Message newMessage = new Message("text", author);
    messageRepository.saveAndFlush(newMessage);

    // Test a method that should return a N+1 query
    // The method does not return an exception because we just created the message so it is loaded in the Session
    getMessageAuthorNameWithNPlusOneQuery(newMessage.getId());

    // Clear the session to be able to correctly detect the N+1 queries in the tests
    hibernateQueryInterceptor.clearNPlusOneQuerySession(entityManager);

    try {
        // Test a method that should return a N+1 query
        // This time the Session is empty and the N+1 query is detected
        getMessageAuthorNameWithNPlusOneQuery(newMessage.getId());
        assert false;
    } catch (NPlusOneQueriesException exception) {
        assertThat(exception.getMessage())
                .contains("N+1 queries detected on a getter of the entity com.yannbriancon.utils.entity.User\n" +
                        "    at com.yannbriancon.interceptor.NPlusOneQueriesExceptionTest" +
                        ".getMessageAuthorNameWithNPlusOneQuery");
    }
}

Configuration

Enable

By default the detection of N+1 queries is enabled for every profile.

To disable it, you can set the property spring-hibernate-query-utils.n-plus-one-queries-detection.enabled to false.

Error level

By default the detection of N+1 queries logs an error to avoid breaking your code.

However, my advice is to override the default error level to throw exceptions for your test profile.

Now you will easily detect which tests are failing and be able to flag them and set the error level to error logs only on those tests while you are fixing them.

To do this, you can configure the error level when N+1 queries is detected using the property spring-hibernate-query-utils.n-plus-one-queries-detection.error-level.

4 levels are available to handle the detection of N+1 queries:

  • INFO: Log a message of level info
  • WARN: Log a message of level warn
  • ERROR (default): Log a message of level error
  • EXCEPTION: Throw a NPlusOneQueriesException

Here are two examples on how to use it globally or for a specific test:

  • In application.properties:
spring-hibernate-query-utils.n-plus-one-queries-detection.error-level=INFO
  • In tests:
@SpringBootTest("spring-hibernate-query-utils.n-plus-one-queries-detection.error-level=INFO")
@Transactional
class NPlusOneQueriesLoggingTest {
    ...
}

Query Count

To start counting the generated queries, you need to instantiate a HibernateQueryInterceptor.

Three methods are available:

  • startQueryCount: Initializes the query count to 0 and allows the queries to increment the count.
  • getQueryCount: Returns the current query count for the Thread concerned as a Long.

The count is local to a Thread. This choice was made to have a consistent count for a running application and avoid other threads to alter the count.

Example in a test:

...
import com.yannbriancon.interceptor.HibernateQueryInterceptor;


@RunWith(SpringRunner.class)
@SpringBootTest
@Transactional
public class NotificationResourceIntTest {
    @Autowired
    private HibernateQueryInterceptor hibernateQueryInterceptor;

    @Test
    public void getNotification_isOk() throws Exception {
        // Initialize the query to 0 and allow the counting
        hibernateQueryInterceptor.startQueryCount();

        // Call the resource that we want to test
        MvcResult result = mvc.perform(get("/rest/notifications"))
                .andExpect(status().isOk())
                .andReturn();

        // Get the query count for this thread and check that it is equal to the number of query you expect,
        // Let's say 4 for the example.
        Assertions.assertThat(hibernateQueryInterceptor.getQueryCount()).isEqualTo(4);
    }
}

Changelog

See CHANGELOG for more information.

Contributing

See CONTRIBUTING for more information.

License

Distributed under the MIT License. See LICENSE for more information.

Contact

@YBriancon - [email protected]

Project Link: https://github.com/yannbriancon/spring-hibernate-query-utils

About

Library giving tools to detect N+1 queries and count the queries generated with Spring and Hibernate

Resources

License

Stars

Watchers

Forks

Packages

No packages published

Languages