Decrease execution time for a given database-handling Python 2.7 program.
Question: The following function fetches data from a database:

    
    
    def findid(name,parent):
        conn = MySQLdb.connect (host = "localhost",
                           user = "arunkamaly",
                            passwd = "code",
                           db = "se")
        cursor=conn.cursor()
    
        cursor.execute(" select xbrl_id from se_xbrl_concepts where xbrl_name=%s;",name)
        name=cursor.fetchone()
        cursor.execute(" select xbrl_id from se_xbrl_concepts where xbrl_name=%s;",parent)
        pname=cursor.fetchone()
        cursor.close()
        if pname==None:
                return name[0],0
        return name[0],pname[0]
    

And here the function above is used, but also the insert method introduced:

    
    
    def prsentparse():
        conn = MySQLdb.connect (host = "localhost",\
                           user = "arunkamaly",\
                            passwd = "code",\
                           db = "se")
        cursor=conn.cursor()
    
        f = open(csvfile, 'rb')
        spamReader = csv.reader(f, delimiter=',', quotechar='"')
        for clist in spamReader:
                if clist[0]=='LinkRole' or clist[0] =='' or clist[0]=='prefix':
                        continue
                name=clist[0]+':'+clist[1]
    
                parent=clist[6].strip()
                xid,pid=findid(name,parent)
                prio=0 if clist[5].strip()=='' else clist[5]
                order=0 if clist[4].strip()=='' else clist[4]
                depth=0 if clist[3].strip()=='' else clist[3]
        #print clist
                #cursor.execute("INSERT INTO se_xbrl_presentation (xbrl_id,xbrl_parent_id,priority,order,depth) VALUES (%s,%s,%s,%s,%s);",(xid,pid,prio,order,depth) )
        #sql = "insert into se_xbrl_presentation (xbrl_id, xbrl_parent_id, priority, order, depth) values (" + xid + ", " + pid + ", " + prio + ", " + order + ", " + depth + ");"
        try:
            cursor.execute("INSERT INTO `se_xbrl_presentation`(`xbrl_id`, `xbrl_parent_id`, `priority`, `order`, `depth`) VALUES (%s,%s,%s,%s,%s);",(xid,pid,prio,order,depth) )
        except MySQLdb.Error,e:
            print "mysql Error %d:%s"%(e.args[0],e.args[1])
    conn.commit
    cursor.close()
    

This approach appears to be too slow. Could you please advise any performance-
increasing improvements?


Answer: You are opening a connection inside the findid function. That means that you
are opening a connection in every iteration of the for loop. Instead, pass the
connection that you already have to the findid function and you won't have to
open one every time.

Another thing that could be important: do you have an index in the xbrl_name
field of the se_xbrl_concepts table? You are doing two selects in each
iteration of the for loop.

