Imagine a bacon-wrapped Ferrari. Still not better than our free technical reports.
See all our reports

How to solve Spring JDBC Templates N+1 SQL SELECTs Issues with XRebel

While the infamous N+1 SELECTs problem is increasingly associated with ORM tools, it can still happen in your app even if you avoid ORM like the plague. Spring JDBC Template is a popular way to use Plain Old SQL in Java in a way that provides some abstraction over SQL parameters, looping over rows and mapping to DTOs.

Spring JDBC Templates encourage writing individual methods for processing particular types of data. This can lead to issues, as developers can call those methods without knowing that they query the database. The classical N+1 SELECTs issues arise when one query is issued to the database, and then another query is issued for every returned row, typically to load the associated data. The solution is to use JOINs and other SQL features to avoid multiple queries.

Let’s look at an example – just downloading the Spring Petclinic and enabling XRebel to run with it right away shows us an issue:

Screenshot 2014-08-28 15.48.35

XRebel is an always-on, interactive profiler that runs in the background and notifies the user when and if an issue is detected. In this case, it has raised an alert that too many queries are issued to render this page. As you can imagine, 37 queries to render a simple page is overkill. To find out what is happening, let’s click on the toolbar and see the drilldown.

Screenshot 2014-08-28 15.52.31

Looking at the drilldown we can see several things:

  • 37 queries are issued from the method JdbcOwnerRepositoryImpl.findByLastName that also issues an SQL SELECT to the Owners table that returns 10 rows.
  • That method calls JdbcOwnerRepositoryImpl.loadOwnersPetsAndVisits that in turn issues 10 queries to the Pets table and calls JdbcOwnerRepositoryImpl.getPetTypes and JdbcVisitRepositoryImpl.findByPetId
  • Those methods in turn issue 13 queries to the Types table and another 13 queries to the Visits table. XRebel conveniently groups same queries under one group.

Looking at the JdbcOwnerRepositoryImpl.findByLastName method we find that it issues a single Hibernate query:

List owners = this.namedParameterJdbcTemplate.query(
  "SELECT id, first_name, last_name, address, city, telephone FROM owners WHERE last_name like :lastName",

Looking at loadOwnersPetsAndVisits() method we can see a classical instance of the N+1 SELECT:

for (Owner owner : owners) {

This can be solved by rewriting the original query to join the Pets, Types and Visits to the selected Owners. So even though we mention above that the N+1 Selects problem is more associated with ORM tools, it is entirely possible to have it with Plain Old SQL, since folks can call methods without knowing that they issue queries to the database.

Interactive profilers like XRebel are great at uncovering such issues before they go to production and cause slowdowns or outages.