Batch insert entities to DB (Quarkus, Hibernate)

user_1136504 0
Viewed 1848

First off: I'm not used to Quarkus or Hibernate (I'm pretty much all .net)

Problem:

My service receives a list of ~10k (Would guess thats the most common number). This comes via a resource endpoint, and it takes +10s for it to complete, Far to long. And the service is unresponsive.

*Endpoint -> Service/Business -> DAO*

@Override
public void create(FooBusiness foo) {

    var statuses = new ArrayList<StatusDto>();

    for(var i = 1; i < foo.getFromList().size(); i++){

        var bar = foo.getFromList().get(i);
        statuses.add(new StatusDto(bar.x, bar.y));
    }
    statusDao.create(statuses);
}

The statusDao.Create() is annotated with @Transactional:

DAO is @ApplicationScoped

And this EM is:

@PersistenceContext
EntityManager entityManager;

statusDao.Create():

@Transactional
public List<StatusDto> create(List<StatusDto> dto) {

    for(var i = 0; i < dto.size(); i++){

        var status = dto.get(i);
        status.setCreatedTimestamp(LocalDateTime.now());
        entityManager.persist(status);
    }

    entityManager.flush();

    return dto;
}

I've been reading a lot of posts about this, and many of them suggests this property, and split the persist loop to be the same as the batch size: quarkus.hibernate-orm.jdbc.statement-batch-size

Problem is, when I add it to the application.properties i get this varning:

Cannot resolve configuration item 'statement-batch-size'

I've spent almost a day trying to find solutions on how to speed things up, anything obvious that I've missed here?

And/or:

Can I wrap the call from the service to the dao in some sort of magical fire and forget call built into Quarkus or Vert.x?

vert.x quarkus hibernate java
2 Answers

Hibernate keeps all entities that you persist in the persistence context so you will acquire more and more memory which might lead to bad performance. If you do not need these entities anymore as it seems, you can flush and clear them out in e.g. batches of 50 items.

for (var i = 0; i < dto.size();) {
    var status = dto.get(i);
    status.setCreatedTimestamp(LocalDateTime.now());
    entityManager.persist(status);
    i++;
    if ((i % 50) == 0) {
        entityManager.flush();
        entityManager.clear();
    }
}
entityManager.flush();
user_4124460

It's hard to answer this question definitively unless you've identified the precise cause of the poor response time. It could in principle be due to:

  1. the latency associated with many requests to the database server,
  2. the overhead of pinning many entity objects in memory in a Hibernate stateful session, or even
  3. the cost of receiving and parsing the incoming data.

Let's assume it's not 3.

  • If it's 2, then, indeed, JDBC batching would help, and you just need to figure out how to make that config property work.
  • But my guess is that Christian is correct, and the problem is the accumulation of data in the persistence context. If this guess is correct then there are two possible solutions: one is to use a StatelessSession, which was designed with this kind of usage in mind, and the other is to use flush() and clear() as described by Christian.

I would recommend using StatelessSession, unless the problem is actually a combination of 2+3, in which case you need both batching, and persistence context management, and then in that case do what Christian suggests.

user_28897600
Related