JPA and the N+1 select problem

Warning to readers: This blog entry contains references to articles only available in Danish. So if you keep on reading be prepared to weep if you want to follow my suggestions ;-)

Lately I've been working hard on Lund&Bendsens intranet and processes involved around it. I've been using JBoss Seam for the most part and overall I'm quite thrilled about this choice of web framework. One of the cool parts about Seam is the way it integrates with Java Persistence API (JPA)/Hibernate and handles my persistence context even when I'm rendering the views for the intranet.

At the same time when I have been developing the intranet features in Seam and JPA, my colleague Kenn Sano wrote an excellent article about the N+1 Select Problem in JPA. Here's what it all comes down to (In Danish):

"Man kan [med JPA] "vandre rundt" i en objektgraf og på magisk vis hentes data, som stilles til rådighed i takt med, at vi traverserer - dvs. objekters tilstand indlæses fra databasen alt imens vi bevæger os rundt i objektgrafen. Hvis man ikke er opmærksom på, hvordan JPA fungerer, kan det resultere i mange SQL-kald mod databasen, hvilket kan have stor negativ indvirkning på performance."

I was doing this plentifully in Seam. When presenting a list of courses or a list of students, Seam lets me easily traverse the items in the list by using EL-expressions such as "#{course.location.address}" which involved several N+1 performance penalties. For instance on the list of all planned courses, including their locations, enrolled students etc. I observed a whole of N*4+1 query penalty. So there's no doubt you need to be aware of the impact of your querying strategy.

Note: I'm not blaming JBoss Seam for this behavior ... Seam makes everything a whole lot easier and when everything is easy you just tend to forget to think yourself ;-) Anyways - go read the article if you're interested in JPA and understand Danish.