Skip to Content

SAP HANA Cloud Portal: Analyzing and optimizing queries in EclipseLink

In this short article I would like to aggregate a few best practices I learned during my two years of experience working with EclipseLink ORM framework on SAP HANA Cloud Portal.

The topic discussed assumes that you had some previous experience with JPA based framework, like Hibernate, OpenJPA or EclipseLink.

EclipseLink is the JPA framework of choice in Netweaver Cloud Portal due to it many advantages. It’s performance oriented and highly customizable, two features that are very important in our work on this project.

The example project is based on Spring framework.

The problem:

We have three tables:

     ARTICLES ->


          ID int,

          NAME varchar




          ARTICLE_ID int,

          ROLE_ID int


     ROLES ->


          ID int,

          NAME varchar


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

In Article entity we define the relation using JoinTable annotation:

@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 will declare a new query, getAllArticles as follows:

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

Everything looks good, until after a week or two, when we reach 10K articles, we start to receive complaints regarding system performance.

Problem analysis:

To start analyzing the problem, we should first reproduce it in tests.

As a start, let’s use native EclipseLink PerformanceMonitor to count how many queries are actually hitting the DB:

The easiest way to declare it is through persistence.xml:



                    <property name=“eclipselink.profiler” value=“PerformanceMonitor”/>




But persistence.xml is shared between both production code and tests.

Luckily, we have different beans.xml for those projects. Let’s declare it there, using JpaPropertyMap

       <bean id=“entityManagerFactory”



        <property name=“jpaPropertyMap”>


                    <entry key=“eclipselink.profiler” value=“PerformanceMonitor” />




Please note that you shouldn’t try to declare the PerformanceMonitor using @PersistenceContext annotation:


protected EntityManager em;

As this will simply not work.

Now let’s use the newly declared profiler in our tests:

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

PerformanceMonitor contains a map, that stores total number of queries of each kind, as well as many other useful statistics.

But we are interested just in two of its values: Counter:ReadAllQuery and Counter:ReadObjectQuery.

Let’s compare the sum of those two before and after our query:

Long before = profiler.getOperationTimings.get(“Counter:ReadAllQuery”) + profiler .getOperationTimings.get(“Counter: ReadObjectQuery);

  1. Em.createNamedQuery(“Article.getAllArticles”).getResultList();

Long after = profiler.getOperationTimings.get(“Counter:ReadAllQuery”) + profiler .getOperationTimings.get(“Counter: ReadObjectQuery);

We see, that instead of issuing a single query, our DB is bombarded with 10001 queries. Ouch.

Although we specified fetch = FetchType.EAGER, in annotation, when JoinTable is used, JPA may decide to issue a query for every line returned. Not good.

Solution, take one:

Let’s add a hint, describing how JPA should load the data

@NamedQuery(name = “Article. getAllArticles, query = “SELECT s FROM Article s”, hints = {

             @QueryHint(name = QueryHints. FETCH, value = “s.role”))

The syntax is as follows:

The part before the dot should comply with alias in JPQL query.

If you’ll use another letter, the hint won’t work, and you won’t get any error. Be aware of that.

The part after the dot should comply with the name of field in the same entity:

public class Article {


       Role role;



Everything looks good, now only one query is issued to the DB. But we suddenly discover, that instead of returning 10K rows, the query now returns only 9K. Look’s like we have a problem in our solution.

Solution, take two:

The issue is that QueryHints.FETCH uses JOIN when rewriting the query. In case that our join table doesn’t contain a role for every article, the article itself won’t be returned by the query.

Luckily for us, there’s another hint, QueryHints.LEFT_FETCH, that we can use in this case:

The solution now should look like this:

@NamedQuery(name = “Article. getAllArticles, query = “SELECT s FROM Article s”, hints = {

@QueryHint(name = QueryHints.LEFT_FETCH, value = “s.role”))

This will result in only one query, returning all data as expected.

As I side note, customers of Netweaver Cloud Platform may also use EclipseLink as their persistence layer while developing their applications that are consumable in Netweaver Cloud Portal, and this is strongly advised.

Be the first to leave a comment
You must be Logged on to comment or reply to a post.