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:

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.

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:

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

for (Owner owner : owners) {
loadPetsAndVisits(owner);
}

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. Also, if you’d like to use XRebel in continuous integration setting to find performance regressions automatically, check out XRebel Hub, it does exactly that!

Dr Jevgeni Kabanov is the founder and CEO of ZeroTurnaround, a development tools company that focuses on productivity. He wrote the first version of the ZeroTurnaround flagship product, JRebel, a class-reloading JVM plugin. Jevgeni has been speaking at international conferences since 2005, including Devoxx, JavaZone, JAOO, QCon, TSSJS, JFokus and JavaOne, where he was named Rock Star in 2011 and 2012. Jevgeni also started the first Java conference in Estonia, Geekout. He has done research in programming languages, types and virtual machines, publishing several papers on topics ranging from category theoretical notions to typesafe Java DSLs.