title: Java Spring Boot batch inserts show-sql showing seperate inserts
body: <p>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.</p>
  <p><strong>What am I missing here??</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Amount of ingredients to insert</th>
  <th>time</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>3</td>
  <td>2.95 s</td>
  </tr>
  <tr>
  <td>10</td>
  <td>9.48 s</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>By setting show-sql=true I can see that all inserts are done seperately</p>
  <pre><code>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 (?, ?)
  </code></pre>
  <p><strong>Statistics of the insert:</strong></p>
  <p><em><strong>EACH INGREDIENT HAS 28 properties</strong></em></p>
  <pre><code>    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)
  </code></pre>
  <p><strong>Ingredient class:</strong></p>
  <pre><code>@Entity
  @Getter
  @Setter
  @RequiredArgsConstructor
  @ToString
  @Where(clause = &quot;is_active=1&quot;)
  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 = &quot;ingredient&quot;)
      @JsonIgnore
      private Set&lt;IngredientAmount&gt; 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&lt;IngredientProperty&gt; properties;
  
  }
  
  </code></pre>
  <p><strong>IngredientProperty class</strong></p>
  <pre><code>@Entity
  @Getter
  @Setter
  @RequiredArgsConstructor
  @ToString
  @Where(clause = &quot;is_active=1&quot;)
  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;
  }
  </code></pre>
  <p><strong>IngredientController</strong></p>
  <pre><code>    @PostMapping(&quot;/bulk&quot;)
      public ResponseEntity createOrUpdateInBulk(@RequestBody Set&lt;Ingredient&gt; ingredients) throws InterruptedException {
          return new ResponseEntity(this.ingredientService.updateAndCreateBulkIngredients(ingredients), HttpStatus.OK);
      }
  </code></pre>
  <p><strong>IngredientService</strong></p>
  <pre><code>    public boolean updateAndCreateBulkIngredients(Set&lt;Ingredient&gt; toUpdateIngredients) {
          Set&lt;Ingredient&gt; toAddOrUpdateIngredients = new HashSet&lt;&gt;();
          for (int i = 0; i &lt; toUpdateIngredients.size(); i++) {
              Ingredient ingredient = new ArrayList&lt;&gt;(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 &amp;&amp; i &gt; 0) {
                  this.ingredientRepository.saveAll(toAddOrUpdateIngredients);
                  toAddOrUpdateIngredients.clear();
              }
          }
          if (toAddOrUpdateIngredients.size() &gt; 0) {
              this.ingredientRepository.saveAll(toAddOrUpdateIngredients);
              toAddOrUpdateIngredients.clear();
          }
          return true;
      }
  </code></pre>
  <p><strong>Application.properties contains the following:</strong></p>
  <pre><code>
  
  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
  </code></pre>
  <p><em><strong>UPDATE</strong></em></p>
  <p>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!!!</p>
  <p>Added the following:</p>
  <pre><code>spring.datasource.url=${DATASOURCE_URL:jdbc:mysql://PATH:PORT?cachePrepStmts=true&amp;useServerPrepStmts=true&amp;rewriteBatchedStatements=true}
  </code></pre>
  <pre><code>?cachePrepStmts=true&amp;useServerPrepStmts=true&amp;rewriteBatchedStatements=true
  </code></pre>
  <p>This also changed the amount of batches:</p>
  <pre><code>    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)
  </code></pre>
  <p><em><strong>NEW QUESTION</strong></em></p>
  <p>Is this the maximum performance I get get?? Or are there more things I can do to increase performance?</p>