Lesson Learned #354: Why is Python using a lot of CPU of Azure SQ Database?

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

Today, we got a service request that our customer reported a high CPU usage in Azure SQL Database. Following I would to share with you my lessons learned here. 

 

Our customer has the following Python code that is running a query against Azure SQL Database but every execution is taking too much time and the CPU is getting 100%. 

 

 

import pyodbc
import time

def ConnectToTheDB():
    try:
        print('Connecting to the DB')
        start_time = time.time()    
        conn = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};server=tcp:servername.database.windows.net,1433;UID=username;PWD=password;database=DBName");  
        print("Connected to the Database %s seconds ---" % ((time.time() - start_time)) )
        return conn
    except BaseException as e:
        print("An error occurred connecting to the DB - " + format(e))
        return 

conn = ConnectToTheDB()
cursor = conn.cursor()

SQL = "select count(*) from Table WHERE TextToSearch = ?"
cursor.setinputsizes([(pyodbc.SQL_WVARCHAR, 200, 0)])
nLoop=1
while  nLoop<1000:
    nLoop=nLoop+1
    start_time = time.time()    
    cursor.execute(SQL,"Value:" + str(nLoop))
    row = cursor.fetchone() 
    totalODBC=0
    while row: 
        totalODBC=totalODBC+1
        print("-(ODBC)--- Loop:%d Rows Returned:%d %s seconds ---" % (nLoop,totalODBC,(time.time() - start_time)) )
        row = cursor.fetchone()
conn.close()

 

 

Jose_Manuel_Jurado_0-1684954176234.png

 

Using SQL Server Profiler extension of Azure Data Studio we found that the following query that Python is executing. 

 

 

declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,N'@P1 nvarchar(200)',N'select count(*) from PerformanceVarcharNVarchar WHERE TextToSearch = @P1',N'Value:3'
select @p1

 

 

Jose_Manuel_Jurado_0-1684955379075.png

 

Checking the query that Python is executing, we saw that the execution plan reported a type conversion in the exclamation mask in the Select operator. 

 

 

declare @p1 int
set @p1=Null
exec sp_prepexec @p1 output,N'@P1 nvarchar(200)',N'select count(*) from PerformanceVarcharNVarchar WHERE TextToSearch = @P1',N'Value:3'
select @p1

 

 

Jose_Manuel_Jurado_3-1684954749678.png

 

So, comparing the data type that Python is sending to the driver with the column of the table, we saw a data type mismatch, because the column data type is Varchar and the parameter data type using by Python is nVarchar, hence, forcing to Azure SQL Database to run a conversion implicit. The code change was cursor.setinputsizes([(pyodbc.SQL_WVARCHAR, 200, 0)]) by cursor.setinputsizes([(pyodbc.SQL_VARCHAR, 200, 0)]).

 

Changing the parameter of Python to the same data type that the column of the table has, the performance was high and the CPU usage was very low. 

 

 

import pyodbc
import time

def ConnectToTheDB():
    try:
        print('Connecting to the DB')
        start_time = time.time()    
        conn = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};server=tcp:servername.database.windows.net,1433;UID=username;PWD=password;database=DBName");  
        print("Connected to the Database %s seconds ---" % ((time.time() - start_time)) )
        return conn
    except BaseException as e:
        print("An error occurred connecting to the DB - " + format(e))
        return 

conn = ConnectToTheDB()
cursor = conn.cursor()

SQL = "select count(*) from Table WHERE TextToSearch = ?"
cursor.setinputsizes([(pyodbc.SQL_VARCHAR, 200, 0)])
nLoop=1
while  nLoop<1000:
    nLoop=nLoop+1
    start_time = time.time()    
    cursor.execute(SQL,"Value:" + str(nLoop))
    row = cursor.fetchone() 
    totalODBC=0
    while row: 
        totalODBC=totalODBC+1
        print("-(ODBC)--- Loop:%d Rows Returned:%d %s seconds ---" % (nLoop,totalODBC,(time.time() - start_time)) )
        row = cursor.fetchone()
conn.close()

 

 

Jose_Manuel_Jurado_4-1684955055185.png

 

Enjoy!

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.