Analysis and optimization of a single query in EclipseLink

In this article, I decided to put together a few good practices that I learned in two years of working with the ORM framework EclipseLink based on a real example.

The article is designed for those who have already worked with the framework based on JPA, whether it is Hibernate or OpenJPA.





The project, examples of which I will give, is based on Spring.

Problem:

The following tables are available.

ARTICLES -> Article.java ( ID int, NAME varchar ); ARTICLE_ROLES ( ARTICLE_ID int, ROLE_ID int ); ROLES -> Role.java ( ID int, NAME varchar ); 

')

ROLES is a standard lookup table, with a small number of rows.



Accordingly, in the entity Article we define the connection via the JoinTable:

 @ManyToOne(optional = false, targetEntity = Role.class, fetch = FetchType.EAGER, cascade = { CascadeType.MERGE, CascadeType.REFRESH }) @JoinTable(name = "ARTICLE_ROLES", joinColumns = {@JoinColumn(name = "ARTICLE_ID", referencedColumnName="ID", nullable = false}, inverseJoinColumns = {@JoinColumn(name = "ROLE_ID", referencedColumnName = "ID", nullable = false)}) public Role getRole() { return role; } 

Now we define the query - getAllArticles as follows:

 @NamedQuery(name = "Article. getAllArticles", query = "SELECT s FROM Article s") 

And after a week with ten thousand articles in the database, we begin to receive complaints about poor performance. Problem.

Problem analysis:

First of all, let's use PerformanceMonitor of EclipseLink to measure how many database queries actually go through JPA.

The easiest way to enable it is through persistence.xml

 <persistence> … <properties> … <property name="eclipselink.profiler" value="PerformanceMonitor"/> </properties> </persistence-unit> </persistence> 

But persistence.xml can be common for tests and applications.

But they have different beans.xml. So for tests it is enough to register in it:

 <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"> … <property name="jpaPropertyMap"> <map> <entry key="eclipselink.profiler" value="PerformanceMonitor" /> </map> </property> </bean> 

Now let's use our profiler in the test.

 PerformanceMonitor profiler = (PerformanceMonitor)em.unwrap(Session.class).getProfiler(); 

PerformanceMonitor contains a Map in which it stores all the information, starting with the total number of queries to the database, and ending with the time for each.

We are interested in two specific parameters: Counter: ReadAllQuery and Counter: ReadObjectQuery.

Get them and compare before and after.

 Long before = profiler.getOperationTimings.get("Counter:ReadAllQuery") + profiler.getOperationTimings.get("Counter:ReadObjectQuery "); em.createNamedQuery("Article.getAllArticles").getResultList(); Long after = profiler .getOperationTimings.get("Counter:ReadAllQuery") + profiler.getOperationTimings.get("Counter:ReadObjectQuery "); 

To discover that the difference is not 1, as one would expect, but 10001. Oh.



The fact is that despite fetch = FetchType.EAGER, when using JoinTable, JPA decides to generate a query for each line in order to get the corresponding Role object.

Solution, first version:

Add a Hint indicating JPA how to bring data

 @NamedQuery(name = "Article. getAllArticles", query = "SELECT s FROM Article s", hints = { <b>@QueryHint(name = QueryHints. FETCH, value = "s.role")</b>) 

Consider the syntax of this hint.

The part to the daughter must match the alias of the object in the text of the query.

If you mistakenly use another letter, Hint will not work, silently, without throwing an error.

The part after the dot must match the name of the member inside the Article.

 public class Article { … Role <b>role</b>; … } 

Everything is fine, now the database reaches exactly one request. But we suddenly discover that the number returned by the query is now not ten thousand, as before, but only nine. Problem.

Solution, second version.

The fact is that QueryHints.FETCH rewrites the query for using JOINs. But if in JOIN TABLE there is no corresponding line (the article does not have the necessary role defined), then the main line will not return either.

Fortunately, in this case there is QueryHints.LEFT_FETCH.



The final decision will look like this:

 @NamedQuery(name = "Article. getAllArticles", query = "SELECT s FROM Article s", hints = { @QueryHint(name = QueryHints.LEFT_FETCH, value = "s.role")) 

One query to the database, all objects, without the need to change the text query as such.