Spring Boot + Hibernate - Insert query getting slow down

Question

I am working on one spring boot application. Here I have 100,000 records that are inserted into db by different process. and its inserting one by one. I can't do batch insert.

So in starting some of the task performing well and not taking too much time ,but application process some and database is growing slowly - 2, insert time is increasing.

How can I speed up the process or avoid to get it slow?

Answer 1

The quickest way for inserts would be to use a prepared Statement.

Inject the jdbcTemplate and use its batchUpdate method and set the batch size. It's lightning fast.

If you think you cannot use the batch insert, which is hard for me to understand, then set the batch size to 1.

However, the most optimal batch size is certainly larger than that and depends on the insert statement. You have to experiment a bit with it.

Here an example for you with a class called LogEntry. Substitute class, table, columns and attributes by your class, table, columns and attributes and place it into your repository implementation.

Also make sure you set the application properties as mentioned here https://stackoverflow.com/a/62414315/12918872

Regarding the id Generator either set a sequence id generator (also shown in that link) or like in my case, just generate it on your own by asking for the maxId of your table at the beginning and then counting up.

  @Autowired
  private JdbcTemplate jdbcTemplate;

   public void saveAllPreparedStatement2(List<LogEntry> logEntries) {
    int batchSize = 2000;
    int loops = logEntries.size() / batchSize;
    for (int j = 0; j <= loops; j++) {
      final int x = j;
      jdbcTemplate.batchUpdate("INSERT INTO public.logentries(\r\n"
          + "    id, col1, col2, col3, col4, col5, col6)\r\n"
          + "    VALUES (?, ?, ?, ?, ?, ?, ?);\r\n", new BatchPreparedStatementSetter() {
            public void setValues(PreparedStatement ps, int i) throws SQLException {
              int counter = x * batchSize + i;
              if (counter < logEntries.size()) {
                LogEntry logEntry = logEntries.get(counter);
                ps.setLong(1, (long) logEntry.getId());
                ps.setString(2, (String) logEntry.getAttr1());
                ps.setInt(3, (int) logEntry.getAttr2());
                ps.setObject(4, logEntry.getAttr3(), Types.INTEGER);
                ps.setLong(5, (long) logEntry.getAttr4());
                ps.setString(6, (String) logEntry.getAttr5());
                ps.setObject(7, logEntry.getAttr6(), Types.VARCHAR);
              }
            }

            public int getBatchSize() {
              if (x * batchSize == (logEntries.size() / batchSize) * batchSize) {
                return logEntries.size() - (x * batchSize);
              }
              return batchSize;
            }
          });
    }
  }

Answer 2

Some advices for you :

1. It is not normal if you say the inserting time is getting increasing if more records are inserted. From my experience , most probably it is due to some logic bugs in your program such that you are processing more unnecessary data when you are inserting more records. So please revise your inserting logic first.

2. Hibernate cannot batch insert entities if the entity are using IDENTITY to generate is ID . You have to change it to use SEQUENCE to generate the ID with the pooled or pooled-lo algorithm.

3. Make sure you enable the JDBC batching feature in the hibernate configuration

4. If you are using PostgreSQL , you can add reWriteBatchedInserts=true in the JDBC connection string which will provide 2-3x performance gain.

5. Make sure each transaction will insert a batch of entities and then commit but not each transaction only insert one entity.

For more details about points (2), (3) and (4) , you can refer to my previous answers at this.