Slow execution of MS Access queries

[Anirudh_Avantsa]

Hello All,

We are using MS access as back end database and using python to import the data from excel. Any insert/update/delete statement is taking more than 5 minutes to execute and (some times more than 25 minutes, for calculations). Would like to know if we are doing any thing wrong using python code or does the ms access DB itself take so much time to execute. I'm a beginner in python and would appreciate your valuable inputs/suggestions in getting this resolved.

Regards,
Anirudh

[Larz60+]

Show your code, hard to say without.
Doubt that it's Python itself, expect you are creating some sort of bottleneck.

[nilamo]

Is it slow when queried directly, or only through the python odbc connector?
How large is the database? Access does have limitations, and you might be getting near them.

[Anirudh_Avantsa]

Hello All,

Below is the code that I'm using to fetch data from excel and import that into MS Access table and then do some transactions in Access DB. The below code took around 9 minutes to execute and does insert 4 records of two columns into a table. My requirement is to import 70 columns of 49000 records for each month which is taking me more than 48 minutes. If this is because of access, suggest any other database that I can go with. Thanks a ton in advance!

import pyodbc
import time
start_time = time.time()

#Reading the Workdayfile
exFile = r'C:\Users\U6066496\Desktop\Automations\Python-Access\Input_Files\Workday_Report_DummyNumbers.xlsx'
connEx = pyodbc.connect('DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ='+exFile,autocommit=True)
cursEx = connEx.cursor()
print("Reading data from excel...")
recEx_cnt = cursEx.execute("select count(*) from [Workday Report_DummyNumbers$]").fetchall()
print("Number of records", recEx_cnt)

#Inserting into DB
DBFile = r'C:\Users\U6066496\Desktop\Automations\Python-Access\New\Database.accdb'
connstr = (
r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
r'DBQ=C:\Users\U6066496\Desktop\Automations\Python-Access\New\Database.accdb;'
)
print("Connection being establised to Access database...")
connDB = pyodbc.connect(connstr)
curDB = connDB.cursor()
print("Cleaning the table...")
curDB.execute('Delete * from [Workday Report_DummyNumbers]');
print("Table has been cleaned...")
curDB.executemany('INSERT INTO [Workday Report_DummyNumbers] ([Employee ID],[Preferred Name],[Business Title],[Cost Center],[Company Code],[Company],[Worker Type],[Manager ID],[Manager Name],[HR Contact ID],[HR Contact Name],[Employee Status],[Leave Type],[First Day of Leave],[Termination Sub Category],[Time Type],[Employee Type],[Compensation Grade],[Hire Date],[Rehire Date],[Termination Date],[Base Pay Amount],[Target Percent],[Bonus Plan],[Base Pay Currency],[Compensation Plan Type],[Bonus Plan Effective Date],[Total Base Pay - Frequency]) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', excel_results)
curDB.commit();
recDB_cnt = curDB.execute("select count(*) from [Workday Report_DummyNumbers]").fetchall()
print("Number of records updated in access DB", recDB_cnt)
rec_res = curDB.execute("select distinct [Bonus Plan] from [Workday Report_DummyNumbers] where [Bonus Plan] not like 'AIP%' AND [Bonus Plan] not like '%AIP' AND [Bonus Plan] not like '%AIP%' AND [Bonus Plan] not like '%EIP%' AND [Bonus Plan] not like 'EIP%' AND [Bonus Plan] not like '%EIP'").fetchall();

empID_null_chk = ('select * from [Workday Report_DummyNumbers] where [Employee ID] is null')
emp_stat_chk = ('select [Employee ID] from [Workday Report_DummyNumbers] where [Employee Status] is null')
emp_pln_chk = ('select [Employee ID] from [Workday Report_DummyNumbers] where [Bonus Plan] is null')
emp_coc_chk = ('select [Employee ID] from [Workday Report_DummyNumbers] where [Company Code] is null')
emp_cc_chk = ('select [Employee ID] from [Workday Report_DummyNumbers] where [Cost Center] is null')

emp_ID_null = curDB.execute(empID_null_chk).fetchall();
emp_stat_null = curDB.execute(emp_stat_chk).fetchall();
emp_pln_null = curDB.execute(emp_pln_chk).fetchall();
emp_coc_null = curDB.execute(emp_coc_chk).fetchall();
emp_cc_null = curDB.execute(emp_cc_chk).fetchall();

if emp_ID_null != []:
print("Few Employee ID's are missing! Please check the file once and rerun the programme...")
print("Please Correct the file and re-run the program...")

print(emp_stat_null)
if emp_stat_null != []:
curDB.executemany("INSERT INTO [Workday Report_DummyNumbers_Errors] ([Employee ID],Comments) values (?, 'Emp_Status')", emp_stat_null)
print("Records inserted for missing Employee Statuses")

print(emp_pln_null)
if emp_pln_null != []:
curDB.executemany("INSERT INTO [Workday Report_DummyNumbers_Errors] ([Employee ID],Comments) values (?, 'Plan')", emp_pln_null)
print("Records inserted for missing Employee Bonus Plans")
curDB.commit();

print(emp_coc_null)
if emp_coc_null != []:
curDB.executemany("INSERT INTO [Workday Report_DummyNumbers_Errors] ([Employee ID],Comments) values (?, 'Company Code')", emp_coc_null)
print("Records inserted for missing Company Codes")
curDB.commit();

print(emp_cc_null)
if emp_cc_null != []:
curDB.executemany("INSERT INTO [Workday Report_DummyNumbers_Errors] ([Employee ID],Comments) values (?, 'Cost Center')", emp_cc_null)
print("Records inserted for missing Employee Cost Centers")
curDB.commit();

print("--- %s seconds ---" % (time.time() - start_time))
#Closing the connections
cursEx.close
curDB.close
print ("Done")

[nilamo]

Ok, but which part is slow? Inserting the records? Querying?
Are there indexes on the table? Have you tried deleting the indexes and re-adding it after the data has been inserted?

I'd recommend adding the print("--- %s seconds ---" % (time.time() - start_time)) line after every single database interaction, so you can narrow down exactly what is slow about it.

As to your other question, using Access certainly doesn't help. 49000 isn't very many rows, though, so this should be very fast. But then again, that's not your full code (you insert excel_results, which is a variable that doesn't even exist), so maybe you're doing something silly.

Start with timing it, and once you know what's actually slow, you can work on improving it.