Optimizing Azure SQL Performance: Bulk Inserts and Commit Control in JDBC

 There are still some ways you can improve the performance of your inserts. Here are a few suggestions:

1. Use Parameterized Queries: You're already using parameterized queries, which is good. It helps with query performance and prevents SQL injection. Make sure the column types in your table match the data types you're setting in the Prepared Statement.

2. Increase Batch Size: Instead of executing the batch every 10,000 rows, you can try increasing the batch size to a larger number. This can help reduce the number of round trips to the database and improve performance. Experiment with different batch sizes to find the optimal value for your specific scenario.

3. Use Connection Pooling: You can use HikariCP for connection pooling, which is recommended for efficient connection management. Connection pooling helps reuse existing connections instead of creating new connections for each insert, which can significantly improve performance.

4. Disable Auto-Commit: By default, JDBC commits each individual statement execution, which can be costly for bulk inserts. You can try disabling auto-commit mode before executing the batch and commit explicitly after the batch completes. This can help reduce the overhead of committing after every single statement.

Here's an updated version of your code incorporating these suggestions:

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;

        connection.setAutoCommit(false);  // Disable auto-commit

        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();
                connection.commit();  // Explicitly commit the batch
            }

            counter++;
        }

        statement.executeBatch();  // Execute any remaining statements in the batch
        connection.commit();  // Commit the final batch

        connection.setAutoCommit(true);  // Re-enable auto-commit

    } catch (Exception e) {
        e.printStackTrace();
    }
}

By increasing the batch size and disabling auto-commit, you should see improvements in the performance of your insert operations in Azure SQL. Remember to experiment with different batch sizes to find the optimal value for your specific scenario.