0

I am going through this Doc, to improve the performance of my insert query in Azure SQL, it says Bulk Copy API does not support Date field is there any way via which i can improve my inserts in Azure SQL. I heavy write requirements.

Below is the code i am using:

   public void saveAllJdbc(List<JdbcDetail> jdbcDetails){

       String sql = String.format("INSERT INTO %s  VALUES (?, ?, ?,?)", "my-table");

       try (Connection connection = hikariDataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql);
        ){
            int counter = 0;

            for (JdbcDetail row : jdbcDetails) {
                statement.clearParameters();
                statement.setInt(1, row.getQuantity());
                statement.setDate(2, Date.valueOf(row.getDate()));
                statement.setFloat(3, row.getId());
                statement.setInt(4, row.getNum());
              
                statement.addBatch();
                if ((counter + 1) % 10000 == 0) {
                   statement.executeBatch();
                  statement.clearBatch();
                }

                counter++;
            }

       } catch (Exception e) {
            e.printStackTrace();
        }
    }
Marcin_S
  • 529
  • 3
  • 14
  • You almost checked all options by using parameterized query as well connection pooling so there could be two more things you can check , one is just look for a right batch size by experimenting the batch size and second thing by default JDBC commit each individual statement. you can disable that auto commit and they give it a try. Thanks also you can look into this https://stackoverflow.com/questions/3784197/efficient-way-to-do-batch-inserts-with-jdbc – rahularyansharma May 20 '23 at 11:12

0 Answers0