Ndtyjky

I have to process a huge amount of data distributed over 20 tables (~5 million records in summary) and I need to efficently load them.

I'm using Wildfly 14 and JPA/Hibernate.

Since in the end, every single record will be used by the business logic (in the same transaction), I decided to pre-load the entire content of the required tables into memory via simply:

em.createQuery("SELECT e FROM Entity e").size();

After that, every object should be availabe in the transaction and thus be available via:

em.find(Entity.class, id);

But this doesn't work somehow and there are still a lot of calls to the DB, especially for the relationships.

How can I efficiently load the whole content of the required tables including
the relationships and make sure I got everything / there will be no further DB calls?

What I already tried:

• 
  FetchMode.EAGER: Still too many single selects / object graph too complex


• 
  EntityGraphs: Same as FetchMode.EAGER


• 
  Join fetch statements: Best results so far, since it simultaneously populates the relationships to the referred entities


• 
  2nd Level / Query Cache: Not working, probably the same problem as em.find
  

One thing to note is that the data is immutable (at least for a specific time) and could also be used in other transactions.

Edit:

My plan is to load and manage the entire data in a @Singleton bean. But I want to make sure I'm loading it the most efficient way and be sure the entire data is loaded. There should be no further queries necessary when the business logic is using the data. After a specific time (ejb timer), I'm going to discard the entire data and reload the current state from the DB (always whole tables).

Keep in mind, that you'll likely need a 64-bit JVM and a large amount of memory. Take a look at Hibernate 2nd Level Cache. Some things to check for since we don't have your code:

1. 
   @Cacheable annotation will clue Hibernate in so that the entity is cacheable


2. Configure 2nd level caching to use something like ehcache, and set the maximum memory elements to something big enough to fit your working set into it


3. Make sure you're not accidentally using multiple sessions in your code.

If you need to process things in this way, you may want to consider changing your design to not rely on having everything in memory, not using Hibernate/JPA, or not use an app server. This will give you more control of how things are executed. This may even be a better fit for something like Hadoop. Without more information it's hard to say what direction would be best for you.

I understand what you're asking but JPA/Hibernate isn't going to want to cache that much data for you, or at least I wouldn't expect a guarantee from it. Consider that you described 5 million records. What is the average length per record? 100 bytes gives 500 megabytes of memory that'll just crash your untweaked JVM. Probably more like 5000 bytes average and that's 25 gB of memory. You need to think about what you're asking for.

If you want it cached you should do that yourself or better yet just use the results when you have them. If you want a memory based data access you should look at a technology specifically for that. http://www.ehcache.org/ seems popular but it's up to you and you should be sure you understand your use case first.

If you are trying to be database efficient then you should just understand what your doing and design and test carefully.

Basically it should be a pretty easy task to load entire tables with one query each table and link the objects, but JPA works different as to be shown in this example.

The biggest problem are @OneToMany/@ManyToMany-relations:

@Entity

public class Employee {

    @Id

    @Column(name="EMP_ID")

    private long id;

    ...

    @OneToMany(mappedBy="owner")

    private List<Phone> phones;

    ...

}

@Entity

public class Phone {

    @Id

    private long id;    

    ...

    @ManyToOne

    @JoinColumn(name="OWNER_ID")

    private Employee owner;

    ...

}

FetchType.EAGER

If defined as FetchType.EAGER and the query SELECT e FROM Employee e Hibernate generates the SQL statement SELECT * FROM EMPLOYEE and right after it SELECT * FROM PHONE WHERE OWNER_ID=? for every single Employee loaded, commonly known as 1+n problem.

I could avoid the n+1 problem by using the JPQL-query SELECT e FROM Employee e JOIN FETCH e.phones, which will result in something like SELECT * FROM EMPLOYEE LEFT OUTER JOIN PHONE ON EMP_ID = OWNER_ID.

The problem is, this won't work for a complex data model with ~20 tables involved.

FetchType.LAZY

If defined as FetchType.LAZY the query SELECT e FROM Employee e will just load all Employees as Proxies, loading the related Phones only when accessing phones, which in the end will lead into the 1+n problem as well.

To avoid this it is pretty obvious to just load all the Phones into the same session SELECT p FROM Phone p. But when accessing phones Hibernate will still execute SELECT * FROM PHONE WHERE OWNER_ID=?, because Hibernate doesn't know that there are already all Phones in its current session.

Even when using 2nd level cache, the statement will be executed on the DB because Phone is indexed by its primary key in the 2nd level cache and not by OWNER_ID.

Conclusion

There is no mechanism like "just load all data" in Hibernate.

It seems there is no other way than keep the relationships transient and connect them manually or even just use plain old JDBC.