Java Spring Boot batch inserts show-sql showing seperate inserts

Question

I am using Spring Boot and am trying to insert Ingredients in bulk by uploading an Excel file in the frontend application. The Spring Boot API recieves a list of Ingredients but it is very slow on inserting. Updates go fast, but inserts go very slow.

What am I missing here??

Amount of ingredients to insert	time
3	2.95 s
10	9.48 s

By setting show-sql=true I can see that all inserts are done seperately

Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)

Statistics of the insert:

EACH INGREDIENT HAS 28 properties

    15800 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    1316900 nanoseconds spent preparing 13 JDBC statements;
    144703300 nanoseconds spent executing 10 JDBC statements;
    9137380500 nanoseconds spent executing 110 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    9164292200 nanoseconds spent executing 1 flushes (flushing a total of 280 entities and 10 collections);
    0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)

Ingredient class:

@Entity
@Getter
@Setter
@RequiredArgsConstructor
@ToString
@Where(clause = "is_active=1")
public class Ingredient {

    @Id
    private String id;

    @PrePersist
    public void prePersist() {
        if (this.id == null) {
            this.id = UUID.randomUUID().toString();
        }
    }

    @ManyToOne
    private IngredientCategory category;

    @OneToMany(mappedBy = "ingredient")
    @JsonIgnore
    private Set<IngredientAmount> ingredientAmounts;

    @Column(length = 200)
    @Size(max = 200)
    private String unit;

    @Column(length = 1000)
    @Size(max = 1000)
    private String name;
    private boolean isActive;

    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
    private Set<IngredientProperty> properties;

}

IngredientProperty class

@Entity
@Getter
@Setter
@RequiredArgsConstructor
@ToString
@Where(clause = "is_active=1")
public class IngredientProperty {
        @Id
    private String id;

    @PrePersist
    public void prePersist() {
        if (this.id == null) {
            this.id = UUID.randomUUID().toString();
        }
    }
    
    private String name;
    private String value;
    private String unit;
    private boolean isActive;
}

IngredientController

    @PostMapping("/bulk")
    public ResponseEntity createOrUpdateInBulk(@RequestBody Set<Ingredient> ingredients) throws InterruptedException {
        return new ResponseEntity(this.ingredientService.updateAndCreateBulkIngredients(ingredients), HttpStatus.OK);
    }

IngredientService

    public boolean updateAndCreateBulkIngredients(Set<Ingredient> toUpdateIngredients) {
        Set<Ingredient> toAddOrUpdateIngredients = new HashSet<>();
        for (int i = 0; i < toUpdateIngredients.size(); i++) {
            Ingredient ingredient = new ArrayList<>(toUpdateIngredients).get(i);
            Ingredient existing = this.ingredientRepository.getIngredientById(ingredient.getId());
            if (existing != null) {
                existing.getProperties().clear();
                existing.getProperties().addAll(ingredient.getProperties());
                existing.setUnit(ingredient.getUnit());
                toAddOrUpdateIngredients.add(existing);
            } else {
                toAddOrUpdateIngredients.add(ingredient);
            }
            if (i % batchSize == 0 && i > 0) {
                this.ingredientRepository.saveAll(toAddOrUpdateIngredients);
                toAddOrUpdateIngredients.clear();
            }
        }
        if (toAddOrUpdateIngredients.size() > 0) {
            this.ingredientRepository.saveAll(toAddOrUpdateIngredients);
            toAddOrUpdateIngredients.clear();
        }
        return true;
    }

Application.properties contains the following:

spring.datasource.driverClassName= com.mysql.cj.jdbc.Driver
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.jdbc.batch_size=5
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true
spring.jpa.properties.hibernate.batch_versioned_data=true
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.generate_statistics=true

UPDATE

Adding the following to application.properties massively increased the performance. I can still see that inserts are done seperately, but inserting 10 ingredients with each 28 IngredientProperty's now takes 800ms instead of 8.5s!!!

Added the following:

spring.datasource.url=${DATASOURCE_URL:jdbc:mysql://PATH:PORT?cachePrepStmts=true&useServerPrepStmts=true&rewriteBatchedStatements=true}

?cachePrepStmts=true&useServerPrepStmts=true&rewriteBatchedStatements=true

This also changed the amount of batches:

    15900 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    171364400 nanoseconds spent preparing 13 JDBC statements;
    129932100 nanoseconds spent executing 10 JDBC statements;
    166547600 nanoseconds spent executing 3 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    229440900 nanoseconds spent executing 1 flushes (flushing a total of 280 entities and 10 collections);
    0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)

NEW QUESTION

Is this the maximum performance I get get?? Or are there more things I can do to increase performance?