Threads in python

Question

I am beginar in python script. I want read msaccess database records and write into XML file. Access database table have more than 20000 records.

Now i am able to do but , it is taking 4 to 5 minutes. So i implement threading concept. But threading also taking more than 5 to 6 minutes. Because each thread open datasource reading records from tables and close datasource.

I don't know how to solve the problems.

CODE:

class ConfigDataHandler(Thread):
 def __init__(self, dev):
 Thread.__init__(self)
 self.dev = dev
 def run(self): 
 db_source_path = r'D:\sampleDB.mdb'
 db_source = win32com.client.Dispatch(r'ADODB.Connection')
 db_source.ConnectionString = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;
 DATA SOURCE=' + db_source_path + ';'
 db_source.Open()
 query = """ SELECT * from table"""
 source_rs = win32com.client.Dispatch(r'ADODB.Recordset')
 source_rs.Open(query, db_source, 3, 1)
 while not source_rs.EOF :
 f_units.append(source_rs.fields("Name").Value))
 source_rs.MoveNext()
 source_rs.Close()
 db_source.Close() 
 out = render(f_units)
 open("D:/test.xml", "w").write(out)
d_list = get_dev_list()
for d in d_list: 
 current = ConfigDataHandler(d)
 current.start()

• Pratik Deoghare: Can you please paste your code snippet here?
• S.Lott: Have you used a profiler to see where the time is being spent? I'll bet that 80% of the time is in the ODBC connection to Jet. Can you profile your program to gather some facts?

Answer 1

As mentioned please paste your code snippet. First - threads have a synchronisation overhead which is causing multi-threads to run slower.

Second - the msaccess/JET database is very slow and not really suited to multi-threaded use. You might like to consider SQL Server instead - SQL Server Express is free.

Third - it is probably the database slowing down the processing. What indexes do you have? What queries are you making? What does "explain" say?

• S.Lott: +1: Threads don't make an I/O bound program "magically" go faster.
• teambob: Thanks for posting your code. I can immediately see a few problems. self.dev is not used. You probably want to use self.dev in a WHERE clause for the SQL query. It would be much faster not to reopen the database each time (self.db_source?) and not reopen the XML file each time. Also opening the same file in 'w' mode will erase its contents each time.

Answer 2

1. Undo the threading stuff.

2. Run the profiler on the original unthreaded code.

3. Replace the AODB business with ordinary ODBC.

4. Run the new code through the profiler.

5. Post your results for further discussion.