In SQL Server Profiler shows - Query Execution takes 1ms but in Spring App takes 30ms, where is the delay?
I have a simple Table created in Azure SQL Database
CREATE TABLE 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
CREATE CLUSTERED INDEX [clusteredindexdeleteme] ON DeleteMe
(
[ID] ASC,
[LastName] ASC,
[FirstName] ASC,
[Age] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
In SQL Server Profiler shows - Query Execution takes less than a 1ms
but in Spring App takes 30ms
INFO : 01.05.2022:1754 (40.419) [[]main] HikariDataSource: springHikariCP - Starting...
INFO : 01.05.2022:1754 (41.941) [[]main] HikariDataSource: springHikariCP - Start completed.
Execution Started: 2022/01/05 17:54:41.948
INFO : 01.05.2022:1754 (41.950) [[]main] HikariDataSource: springHikariCP - Starting...
INFO : 01.05.2022:1754 (42.139) [[]main] HikariDataSource: springHikariCP - Start completed.
Select Query Execution Started: 2022/01/05 17:54:42.225
Select Query Execution Completed: 2022/01/05 17:54:42.251
Java Application:
public class JDBCSample {
public static void main(String[] args) {
...
try {
Connection conn = dataSource().getConnection();
conn.setAutoCommit(false);
Statement statement = conn.createStatement();
ResultSet rs ;
try {
LocalDateTime executionStartTime = LocalDateTime.now();
System.out.println("Select Query Execution Started: " + dtf.format(executionStartTime));
rs = statement.executeQuery("SELECT ID from Delete Where ID = 50");
executionEndTime = LocalDateTime.now();
System.out.println("Select Query Execution Completed: " + dtf.format(executionEndTime));
}
catch (SQLException ex)
{
System.out.println("Error message: " + ex.getMessage());
return; // Exit if there was an error
}
} 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:sqlserver://....;encrypt=true;trustServerCertificate=false;");
hikariConfig.setUsername("...");
hikariConfig.setPassword("...");
hikariConfig.setMaximumPoolSize(1);
hikariConfig.setConnectionTestQuery("SELECT 1");
hikariConfig.setPoolName("springHikariCP");
HikariDataSource dataSource = new HikariDataSource(hikariConfig);
return dataSource;
}
}
Note: When I execute the same application against the local server installed on the Dev laptop, it is taking only 6ms.
Note: I am executing this application from Azure Kubernetes.
Where is the delay? How do I fix this so that the Spring application will not take more than 2ms.
From what you have shared here I assume the delay is in network latency.
Dev Java App to Dev Server takes 6 seconds.
Azure Kubernetes App to Azure SQL Database takes 30 ms. Which I think is reasonable if you include the network latency between app and server. I dont think you will get result in 2 ms. If you get, please let me know ;)
Try to check network latency in some ways like doing on SELECT 1
query and calculate turnaround time.
interesting read:
https://azure.microsoft.com/en-in/blog/testing-client-latency-to-sql-azure/
https://docs.microsoft.com/en-us/archive/blogs/igorpag/azure-network-latency-sql-server-optimization
https://github.com/RicardoNiepel/azure-mysql-in-aks-sample
There is a way to measure latency network in java