I have a simple Table created in Azure SQL Database
Below Python script takes only 3ms to to execute the select query from Azure SQL Server
import os
import sys
import logging, logging.handlers
import getopt
import pyodbc
# set up logging
logging.getLogger().setLevel(logging.INFO)
console = logging.StreamHandler()
console.setFormatter(logging.Formatter('%(asctime)s %(name)-12s %(levelname)s %(message)s'))
console.setLevel(logging.INFO)
logging.getLogger().addHandler(console)
logger = logging.getLogger("purgeStaleTags")
server="sqlsrv-01.database.windows.net"
database="db"
username="user"
password = '[email protected]$$word'
driver="{ODBC Driver 17 for SQL Server}"
logger.info("Before Connect")
connection=pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password+';Encrypt=yes;TrustServerCertificate=no;')
logger.info("Before Cursor")
cursor=connection.cursor()
logger.info("After Cursor")
cursor.execute("SELECT item_id FROM offer_table WHERE offer_id = 23583");
records=cursor.fetchall()
logger.info(len(records))
logger.info("After Query")
Logs
>>> logger.info("Before Connect")
2022-01-05 19:00:30,638 purgeStaleTags INFO Before Connect
>>> connection=pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password+';Encrypt=yes;TrustServerCertificate=no;')
>>> logger.info("Before Cursor")
2022-01-05 19:00:30,871 purgeStaleTags INFO Before Cursor
>>> cursor=connection.cursor()
>>> logger.info("After Cursor")
2022-01-05 19:00:30,871 purgeStaleTags INFO After Cursor
>>> cursor.execute("SELECT item_id FROM offer_table WHERE offer_id = 23583");
<pyodbc.Cursor object at 0x7f05f45280b0>
>>> records=cursor.fetchall()
>>> logger.info(len(records))
2022-01-05 19:00:30,884 purgeStaleTags INFO 30
>>> logger.info("After Query")
2022-01-05 19:00:30,884 purgeStaleTags INFO After Query
But the below spring application takes 80ms to execute the select query from Azure SQL Server?
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 item_id FROM offer_table WHERE offer_id = 23583");
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;
}
}
Logs
INFO : 01.06.2022:0032 (31.802) [[]main] HikariDataSource: springHikariCP - Starting...
INFO : 01.06.2022:0032 (33.314) [[]main] HikariDataSource: springHikariCP - Start completed.
Execution Started: 2022/01/06 00:32:33.436
INFO : 01.06.2022:0032 (33.437) [[]main] HikariDataSource: springHikariCP - Starting...
INFO : 01.06.2022:0032 (33.927) [[]main] HikariDataSource: springHikariCP - Start completed.
Select Query Execution Started: 2022/01/06 00:32:34.043
Select Query Execution Completed: 2022/01/06 00:32:34.122
Where is the delay? How do I fix this so that the Spring application will not take more than 2ms.
8
1 Answer
There are many reasons why you might be experiencing those performance issues, including different drivers and configuration.
The one obvious difference reading your code is that in one case you are retrieving the connection, while in the other case you are creating the connection. HikariCP is a known connection pool implementation in Java, and it is optimized for production workloads. Your python code instead will create a connection at each execution, and if executed in a for loop will result in a DoS attack to the server.
I would try to remove the connection pool from the Java process and benchmark the code again.
1
Without HikariCP it is taking 400ms to execute.
–
Just checking. Have you tried timing more than one query? It’s possible that Azure does more setup and initialization that Python does, but that subsequent queries will be quicker.
Yes, I tried with 5 select queries. Python takes 12ms to 22ms and spring takes 350ms
Try to connect via
DriverManagerJDBC API instead of via HikakiCPDataSource. Maybe it’s HikariCP’s lazy loading or something (no idea). Microbenchmarking Java programs is hard: it’s generally recommended to run the code on repeat for a couple of minutes before measuring so that JVM will have a chance to compile Java bytecode into native code.As a sanity check – have you tried benchmarking against a local SQL server – do you see any noticeable difference in runtimes?
Use these timing methods for python and check time in your code and check if your not mistaken in timing option:
start_time=time.time()end_time=time.time()print('time: ' +str(end_time-start_time))