python 5x slower than perl mySql query -

python 5x slower than perl mySql query -

i translating code perl python. if works same, there part of code 5x slower in python in perl , cannot figure out why.

both perl , python in same machine, mysql database.

the code queries db download columns of table , process each row. there more 5 1000000 rows process , big issue in retrieving info database python processing.

here attach 2 code samples: python:

import os import mysql.connector **<--- import mysqldb** import time outdict = dict() ## db parameters db = mysql.connector.connect **<----- mysqldb.connect( ...** (host=dbhost, user=username, # username passwd=passw, # password db=database) # name of info base of operations cur = db.cursor(prepared=true) sql = "select chr,pos,lengthofrepeat,copynum,region db.table_simplerepeat;" cur.execute(sql) print('\t ediva public omics start') s = time.time() sz = 1000 rows = cur.fetchall() row in rows: ## process out dict print time.time() - s cur.close() db.close()

while here comes perl equivalent script:

use strict; utilize digest::md5 qw(md5); utilize dbi; utilize threads; utilize threads::shared; $dbh = dbi->connect('dbi:mysql:'.$database.';host='.$dbhost.'',$username,$pass) or die "connection error!!\n"; $sql = "select chr,pos,lengthofrepeat,copynum,region db.table_simplerepeat\;"; ## prepare statement , query $stmt = $dbh->prepare($sql); $stmt->execute or die "sql error!!\n"; $c = 0; #process query result while (my @res = $stmt->fetchrow_array) { $edivastr{ $res[0].";".$res[1] } = $res[4].",".$res[2]; $c +=1; } print($c."\n"); ## close db connection $dbh->disconnect();

the runtime these 2 scripts is:

~40s perl script ~200s python script

i cannot figure out why happens [i tried using fetchone() or fetchmany() see if there memory issues runtime @ reduces 10% 200s].

my main problem understanding why there such relevant performance difference between 2 functionally equivalent code blocks.

any thought how can verify happening appreciated.

thanks!

update solution

peeyush'comment reply , i'd him post because allowed me find solution.

the problem python connector. changed mysqldb module c compiled module. made python code faster perl code.

i added changes in python code <---- "" show how easy has been gain performance.

the cursor.fetchall means load info in memory @ once, instead of doing when needed.

replace

row = cur.fetchall() row in rows:

by

for row in cur:

python mysql perl