Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

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

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!


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)
等待大神答复

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...