PyODBC takes ~7 seconds to establish a connection with Azure SQL Server, is there a way to minimize this?
import os
import sys
import logging, logging.handlers
import getopt
import pyodbc
from database import *
# 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("testapp")
def connect():
return pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
def purgeStoreData(conn, div_id, purge_days, lookback_days, store_start, store_end):
store_list = get_store_list(conn, div_id, store_start, store_end)
cursor = conn.cursor()
for store in store_list:
logger.info("Store %s ...", store)
cursor.execute("some query")
if __name__ == "__main__":
try:
conn = connect()
purgeStoreData(conn, DIV_ID, PURGE_DAYS, LOOKBACK_DAYS, STORE_START, STORE_END)
logger.info("*** Completed succesfully")
finally:
conn.close()
Is there a way to display the network latency ?
Could you please try to connect using the ODBC Driver 17 for SQL Server? You may find this way faster.
import pyodbc
server = '<server>.database.windows.net'
database = '<database>'
username = '<username>'
password = '{<password>}'
driver= '{ODBC Driver 17 for SQL Server}'
with pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:
with conn.cursor() as cursor:
cursor.execute("SELECT TOP 3 name, collation_name FROM sys.databases")
row = cursor.fetchone()
while row:
print (str(row[0]) + " " + str(row[1]))
row = cursor.fetchone()
Microsoft drivers for Python are here.
Please add the following to the connection string also: "Encrypt=yes;TrustServerCertificate=no;". When a client first attempts a connection to SQL Azure, it sends an initial connection request. Consider this a "pre-pre-connection" request. At this point the client does not know if SSL/Encryption is required and waits an answer from SQL Azure to determine if SSL is indeed required throughout the session (not just the login sequence, the entire connection session). A bit is set on the response indicating so. Then the client library disconnects and reconnects armed with this information.
When you set "Encrypt connection" to "yes" you avoid the "pre-pre-connection" and the connection is faster.
In addition, please verify latency on your internet connection.