The latest expert opinions, articles, and guides for the Java professional.

XRebel and EclipseLink: Solving N+1 SELECTs problem with an interactive profiler

Although Hibernate is the most popular ORM implementation used in the Java world, EclipseLink is now the JPA reference implementation. It’s derived from Oracle TopLink ORM and has multiple differences from Hibernate, including claims to better performance. Therefore we decided to give it a whirl and try the same Spring Petclinic sample application with EclipseLink as the JPA provider.

We set up EclipseLink with load-time weaving and it worked mostly out of the box, with only a couple of hours of massaging the details. Starting the application with XRebel enabled and navigating to the “Find owners” page reveals a problem right away.

XRebel is an interactive profiler that gives insight into your application and uncovers issues that are otherwise not detected. XRebel runs in the background and notifies you 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, 31 queries to render a simple page is overkill. To find out what is happening, let’s click on the toolbar and see the drilldown.

EclipseLink sends 31 queries where Hibernate does 21. This seems a lot for such a simple page. On top of that some of the queries seem weird – e.g. there is a single query to the Owners table that returns 10 rows and then 10 individual queries to the same table that for exactly the same data already selected. These 10 queries to the Owners table is the main reason why EclipseLink queries more than Hibernate.

One key difference between EclipseLink and other JPA providers is the shared object cache. While other JPA providers include session object cache and session query cache, the shared object cache of EclipseLink survives as long as the server is up. Therefore by just refreshing the page a couple of times we reduce the number of issued queries drastically – first to 11 and after the next refresh to a single query:

However, relying on cache is generally a bad idea, since it may not behave same way in production, where data volumes are much larger. We recommend disabling the EclipseLink cache in development to properly reveal the amount and complexity of queries going to the database. To do that just follow the instructions. To fix the N+1 SELECTs issue you can use FetchType.LAZY and the join fetch hint the same way as in Hibernate.

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


No Responses

No comments yet.

RSS feed for comments on this post.

Leave a comment