Spring application : Azure SQL Server Insert is slow

Question

I have a spring batch application that uses Azure SQL Server as a database.

I have a hyperscale Azure SQL server instance with 20 vCores

Below is my table structure

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Schema].[DeleteMe]') AND type in (N'U'))
DROP TABLE [Schema].[DeleteMe]
GO

CREATE TABLE [Schema].[DeleteMe](
[ID] [int] NOT NULL,
[LastName] [char](255) NOT NULL,
[FirstName] [char](255) NOT NULL,
[Age] [int] NOT NULL,
[DOJ] [datetime2](0) NOT NULL,
[Role] [varchar](255) NULL
) ON [PRIMARY]
GO

It is taking ~10 seconds to insert 2800 records

public class JDBCSample {
    ...

    public static void main(String[] args) {
        ...

        // Open a connection
        try {
            ...

            JdbcTemplate jdbcTemplate = new JdbcTemplate();
            // Direct SQL Server Datasource
            //jdbcTemplate.setDataSource(ds);

            // Hikari Connection Pooling
            jdbcTemplate.setDataSource(dataSource());

            String deleteSQL = "Delete from DeleteMe";
            jdbcTemplate.update(deleteSQL);

            /* Using PreparedStatement*/
            /**/
            Connection conn = dataSource().getConnection();
            conn.setAutoCommit(true);
            PreparedStatement stmt = conn.prepareStatement("insert into DeleteMe VALUES(?, 'vijas', 'asdf', 36, '2020-10-30', 'safd')");

            try
            {
                for (int id = 1; id <= 2800; id++) {
                    stmt.setInt(1, id);
                    stmt.addBatch();
                }

                stmt.executeBatch();
            }
            catch (SQLException e) {
                System.out.println("Error message: " + e.getMessage());
                return; // Exit if there was an error
            }

            LocalDateTime endTime = LocalDateTime.now();
            System.out.println(dtf.format(endTime));

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

    @Bean(destroyMethod = "close")
    public static DataSource dataSource(){
        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        hikariConfig.setJdbcUrl("jdbc:....");
        hikariConfig.setUsername("....");
        hikariConfig.setPassword("....");

        hikariConfig.setMaximumPoolSize(10);
        hikariConfig.setConnectionTestQuery("SELECT 1");
        hikariConfig.setPoolName("springHikariCP");

        /*
        hikariConfig.addDataSourceProperty("dataSource.cachePrepStmts", "true");
        hikariConfig.addDataSourceProperty("dataSource.prepStmtCacheSize", "5000");
        hikariConfig.addDataSourceProperty("dataSource.prepStmtCacheSqlLimit", "4096");
        hikariConfig.addDataSourceProperty("dataSource.useServerPrepStmts", "true");

        hikariConfig.addDataSourceProperty("hibernate.jdbc.batch_size", "5000");
        hikariConfig.addDataSourceProperty("hibernate.order_inserts", "true");
        hikariConfig.addDataSourceProperty("hibernate.order_updates", "true");
        hikariConfig.addDataSourceProperty("hibernate.jdbc.batch_versioned_data", "true");
        */

        HikariDataSource dataSource = new HikariDataSource(hikariConfig);

        return dataSource;
    }
}

Note: I have tried the below parameters to the JDBC properties

statementPoolingCacheSize=10;disableStatementPooling=false;enablePrepareOnFirstPreparedStatementCall=true;sendStringParametersAsUnicode=false;

I want to reduce the execution time further to less than a second. How do I achieve this? this is a blocker for Production deployment.

Answer 1

Found that auto commit is causing the delay - replaced autocommit with connection.commit()

public class JDBCSample {
    ...

    public static void main(String[] args) {
        ...

        // Open a connection
        try {
            ...

            JdbcTemplate jdbcTemplate = new JdbcTemplate();
            // Direct SQL Server Datasource
            //jdbcTemplate.setDataSource(ds);

            // Hikari Connection Pooling
            jdbcTemplate.setDataSource(dataSource());

            String deleteSQL = "Delete from DeleteMe";
            jdbcTemplate.update(deleteSQL);

            /* Using PreparedStatement*/
            /**/
            Connection conn = dataSource().getConnection();
            conn.setAutoCommit(false);
            PreparedStatement stmt = conn.prepareStatement("insert into DeleteMe VALUES(?, 'vijas', 'asdf', 36, '2020-10-30', 'safd')");

            try
            {
                for (int id = 1; id <= 2800; id++) {
                    stmt.setInt(1, id);
                    stmt.addBatch();
                }

                stmt.executeBatch();
                conn.commit();
            }
            catch (SQLException e) {
                System.out.println("Error message: " + e.getMessage());
                return; // Exit if there was an error
            }

            LocalDateTime endTime = LocalDateTime.now();
            System.out.println(dtf.format(endTime));

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

    @Bean(destroyMethod = "close")
    public static DataSource dataSource(){
        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        hikariConfig.setJdbcUrl("jdbc:....");
        hikariConfig.setUsername("....");
        hikariConfig.setPassword("....");

        hikariConfig.setMaximumPoolSize(10);
        hikariConfig.setConnectionTestQuery("SELECT 1");
        hikariConfig.setPoolName("springHikariCP");

        HikariDataSource dataSource = new HikariDataSource(hikariConfig);

        return dataSource;
    }
}