javahibernatevert.xquarkus

Batch insert entities to DB (Quarkus, Hibernate)


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?


Solution

  • 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();