not able to get performance boost with python concurrent.futures.ThreadPoolExecutor in SQL Server query

Question

I am querying SQL Server for the list of fields both with threading and without threading.

import pyodbc
import datetime
import concurrent.futures

server = 'xx.xxx.xxx.xxx,1433'
database = 'db'
username = 'user'
password = 'password'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database + ';UID='+username+';PWD='+password + ';MARS_Connection=yes' + ';Max_Pool_Size=100000')

filter_list = ["department_id", "employee_id", "city", "country", "state", "zip_cope", "department_name", "employee_name", "employee_experience"]
t1 = datetime.datetime.now()
result_list = []
def query_executor(field):
  try:
    
    ft1=datetime.datetime.now()
    cursor = cnxn.cursor()
    result = cursor.execute("""SELECT DISTINCT TOP 1000 [{}] from EMPLOYEE_DETAILS""".format(field))
    print(field)
    result_list1 = [filter_item[0] for filter_item in result if filter_item[0]]
    # print("#############################################")
    return {"name": field, "filter_data": result_list1}
  except Exception as e:
    print(e)
  finally:
    print("#############################################")
    print(datetime.datetime.now()-ft1)
    print("#############################################")
    cursor.close()

# with threading
with concurrent.futures.ThreadPoolExecutor() as executor:
  result = [executor.submit(query_executor, field) for field in filter_list]

  for f in concurrent.futures.as_completed(result):
    result_list.append(f.result())

          
print(result_list)
t2 = datetime.datetime.now()
print("#############################################")
print('with threading time taken')
print(t2-t1)
print("#############################################")


#without threading
for f in filter_list:
  result_list.append(query_executor(f))

          
print(result_list)
t2 = datetime.datetime.now()
print("#############################################")
print('without threading time taken')
print(t2-t1)
print("#############################################")

While running, I comment either of one to see the time taken individually with and without threading. But I don't see much time difference. In fact sometime threading one gets slow.

Am I doing something wrong? How will I get the performance boost? filter_list list can grow even bigger sometime, which may lead to slow response.

Thanks in advance!

Answer 1

等待大神答复