JPA and @OneToMany – How to Optimize?

In this article I want to explain an issue concerning the JPA OneToMany relationship which I was faced with in one of my own projects. Working with object -orientated languages like java we often model relationships between object in various cases. One of such relationships is the OnToMany relationship. For example an object ‘Server’ may have a relationship to an object ‘Configuration’. To make the ‘Configuration’ object generic we can model the JPA object in Java like this:

We use only the default behavior of JPA. The Server Object we created now is something typical what we have often in object orientated languages. Its the concept of a Map interface. A Server object can have one or many Configuration objects.

The Database Schema

Now lets take a look on the database schema which is generated by the JPA OR-Mapper automatically:

What’s happen here is, that JPA constructs a so called Join-Table to map the relationship between the server and the configuration. A Join-Table is no bad idea, but selects can become complicated. Assume we have the following data:

What we need here is a JOIN to select the different key-value pairs. A sub-select is not possible, because our Configuration object has no reference to the Server Objects (per default we use a unidirectional relationship).

It looks very strange because JPA has to use the Join-Table to join the Server with the Configuration. And the statement contains two self-join clauses because we are interested in two key-value pairs. Even if the statement looks weird it doesn’t need to be a big problem. A performance issue only occurs if we have a lot for objects in our database. For example if we have more than 7000 configuration objects, this statement can aggregate internal a resultset with 49.000.000 records.

The Solution

To reduce the complexity of the select statements we can avoid the generation of the join table by defining a JoinColumn:

This will reduce the complexity and the response time from the database.

But one important benefit of the new JoinColumn is that the @OneToMany relationship is now bidirectional. It is possible to navigate from the configuration table back to the server table. For this reason it is now possible to avoid the inner joins and use sub-selects instead. See the following JPQL example:

SELECT server FROM Server server
WHERE server.type='virtual'
AND server.id IN (
SELECT t.serverId FROM Configuration AS t
WHERE t.itemName="os" AND t.itemValue="Linux"
)
AND server.id IN (
SELECT t.serverId FROM Configuration AS t
WHERE t.itemName="harddisk" AND t.itemValue="SSD"
)