I have this code below that needs to query multiple database tables.

import mysql.connector
import itertools, time
cnx = mysql.connector.connect(host="host", port=3306, user="user", passwd="pass", db="factory")

cursor = cnx.cursor()

cursor.execute("select distinct(batch) from batchdata where equipment = 'DUV001'")
batch = cursor.fetchall()
cursor.execute("select distinct(settingname) from runsettings where runnumber = 1 and equipment = 'DUV001'")
settings = cursor.fetchall()

start =  time.time()

settings = list(itertools.chain(*settings))
batches = list(itertools.chain(*batch))

lstoflst = [] 
for batch in batches:
    cursor.execute("select distinct(wafer) from batchdata where batch=%s and equipment = 'DUV001'",(batch,))
    wafers = cursor.fetchall()
    wafers = list(itertools.chain(*wafers))

for wafer in wafers:
    waflist = {}
    for i in settings:
        cursor.execute("select float8value from runsettings where settingname = %s and wafer = %s",(i,wafer))
        vals = cursor.fetchall()
        waflist[i] = vals[0][0]


print time.time() - start 

I know there are a lot of things to improve like:

  • Error handling
  • Database connection
  • prepared statements on the query

But I want to focus first on the performance of this code. It works fine, but its taking at least 5 to 10 seconds minimum before it can finish running the loop.

In what way can I improve the performance of the code above?

  • 1
    \$\begingroup\$ Ever heard of SQL joins? \$\endgroup\$ – 301_Moved_Permanently Nov 24 '15 at 9:18
  • \$\begingroup\$ Yes I know joins, Ive overlooked using joins, as you can see i was doing the long way. And i think its a rule of thumb to make the program works first before doing any refactoring/optimization. Thanks! @Mathias \$\endgroup\$ – ellaRT Nov 24 '15 at 11:05
  • \$\begingroup\$ I wouldn't call using res = n*m an optimization over res, i = 0,0; while i < m: res += n; i += 1. Neither for joins, it's simply using the right tool. \$\endgroup\$ – 301_Moved_Permanently Nov 24 '15 at 11:13
  • \$\begingroup\$ Yes I agree with you. But thats just my way of making it work, thats why im here to ask advise from guys like you to help me improve the way i did it. \$\endgroup\$ – ellaRT Nov 24 '15 at 11:15

Your program reminds me of a Java applet that I wrote during my first summer internship. I spent a significant chunk of that summer writing code to query several database tables and match values from one table with values from another table. The performance sucked, but it was tolerable. Then the applet was deployed to an overseas office — and it took minutes to display one screen.

The lesson is, any time you execute queries in a loop, and the number of those queries depends on how much data you have, you're going to have a bad time. The overhead of issuing a query and retrieving the results is significant. What you should do instead is issue one query (or a small fixed number of queries) that gets all of the information you want.

I later learned that all of those troubles could have been avoided by writing a proper SQL query in the first place, namely a query.

Here, it looks like you are interested in constructing lstoflst, which contains the runsettings used for run 1 of each wafer that was in a batch that went through equipment DUV001. The program should look more or less like this:

cnx = …
cursor = cnx.cursor()
    SELECT batchdata.wafer
         , runsettings.settingname
         , runsettings.float8value
        FROM runsettings
            INNER JOIN batchdata
                ON batchdata.equipment = runsettings.equipment
                AND batchdata.wafer = runsettings.wafer
            runsettings.equipment = 'DUV001' AND
            runsettings.runnumber = 1
        ORDER BY batchdata.wafer;
wafer_groups = itertools.groupby(cursor.fetchall(), key=operator.itemgetter(0))
lstoflst = [dict(row[1:] for row in group[1]) for group in wafer_groups]
  • \$\begingroup\$ Thank you so much for a very detailed explanation. I will try using joins. Geez, sometimes the query can be so simple yet i make it complicated. Thanks again. I will updated about the performance once i apply the join query. @200_success \$\endgroup\$ – ellaRT Nov 24 '15 at 11:12
  • 4
    \$\begingroup\$ Using operator.itemgetter(0) instead of the lambda should be faster since the former is implemented in pure C. \$\endgroup\$ – 301_Moved_Permanently Nov 24 '15 at 11:17
  • \$\begingroup\$ With this answer, the query time was reduced from 5-10 seconds to 0.213999986649. Thanks! @200_success \$\endgroup\$ – ellaRT Nov 25 '15 at 1:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.