How to optimize insert of Hibernate object graphs with many @ManyToOne?

A

Alex R

Guest
I have lots of objects with lots of @ManyToOne relationships and I need to insert lots of them into the database.

My insert process has hit a bottleneck in this code, which ensures only a single instance of enum-like @ManyToOne entities are created and re-used:

public <U extends AutoEnum<?>> U saveIfAbsent(Session session, Class<U> clazz, U obj) {
if (obj == null || obj.getValue() == null) {
return null;
}
Optional<U> loadOptional = session.byNaturalId(clazz).using("value", obj.getValue()).loadOptional();
if (loadOptional.isPresent()) {
return loadOptional.get();
} else {
session.save(obj);
return obj;
}
}


The problem here is millions of round-trips to the database, which are just a few nanoseconds each, really add up to several hours of waiting for the process to finish.

So then I tried to cache the values using a ConcurrentHashMap:

private Map<String,Object> cache = new ConcurrentHashMap<>();

public <U extends AutoEnum<?>> U saveIfAbsent(Session session, Class<U> clazz, U obj) {
if (obj == null || obj.getValue() == null) {
return null;
}

@SuppressWarnings("unchecked")
U tmp = (U) cache.computeIfAbsent(clazz.getName() + ":" + obj.getValue(), key_ -> {
Optional<U> loadOptional = session.byNaturalId(clazz).using("value", obj.getValue()).loadOptional();
if (loadOptional.isPresent()) {
return loadOptional.get();
} else {
session.save(obj);
return obj;
}
});
return tmp;
}


That unfortunately fails with:

...SqlExceptionHelper : ERROR: insert or update on table "mytable" violates foreign key constraint


I believe what's happening there is that a "cached" object is returned which hasn't been committed to the database yet, as the thread that's doing the write loses the race to the thread that's re-using the object. Doh.

Is it possible to optimize this process so that a database round-trip is not incurred for every call to saveIfAbsent()?

Continue reading...
 
Top