5

I have the following code that runs very slowly (6.5sec for iterating over 57,390 rows) : 

import mysql.connector

cnx=mysql.connector.connect(host=***, ***)
cursorSQL = cnx.cursor()

for dt in date_vect:
        cursorSQL.execute(query2, ((dt[0] + '-00:00:00'), (dt[0] + '-23:59:59'))) 
          #Takes about 0.25sec per iteration
        usr_list = list(cursorSQL.fetchall())  
          #takes about 6.20sec per iteration

As recommended here : Stackoverflow : Python is slow when..., I tried :

  • usr_list= cursorSQL.fetchall()
  • usr_list= list(cursorSQL.fetchall())

And as @postoronnim suggested, I also tried :

  • usr_list= cursorSQL.fetchmany(57390)

With no success.

However, there is some caching effect, since the same iteration takes only 0.5sec when ran a 2nd time over an iteration already ran on, then slows back to 6.5sec.

  1. Any Idea where that might come from?
  2. Could you confirm that it has nothing to do with my database, since all the import from MySQL is done at the cursor.execute line, and fetchall() slowness is just due to list processing?
  3. Could you explain why there is a caching effect?

Thanks.

Specs : Python 3.5 | 8-core i7 | 16go Ram

General Grievance
  • 4,555
  • 31
  • 31
  • 45
ylnor
  • 4,531
  • 2
  • 22
  • 39
  • 1
    what is the operation performed in the loop? – Keerthana Prabhakaran Mar 15 '17 at 17:08
  • '' cursorSQL.execute() '' queries a mysql table and returns a 57,000 rows object. *date_vect* is a vector of days dates. Then ''fetchall()'' transforms this object into a list. Is that clear? – ylnor Mar 15 '17 at 17:09
  • sorry, I didn't read your question properly. I thought the loop to iterate the fetched data takes higher time. Now I get it. – Keerthana Prabhakaran Mar 15 '17 at 17:14
  • No worries. Any idea? – ylnor Mar 15 '17 at 17:25
  • 2
    execute method is what's slowing you down, you need to use executemany. It reduces the overhead by preparing the database for the operation. – postoronnim Mar 15 '17 at 17:27
  • @postoronnim :are you sure, because execute() method only takes .25sec, and that's the fetchall() methods that takes 6.20sec to run... I don't get it. – ylnor Mar 15 '17 at 17:36
  • Actually, you are right. I confused it with fetchmany, you can specify the size parameter with it to allocate resources. Try this syntax: rows = cursor.fetchmany(size=1). Just put in however many rows you expect. – postoronnim Mar 15 '17 at 17:42
  • @postoronnim: Thanks a lot, but unfortunately, when I tried cursorSQL.fetchmany(size=57390), the line took 6.2 sec to run as before... Is my code correct? – ylnor Mar 15 '17 at 17:49
  • It's not your code, something else is going on. Did you try the suggestion from jkerian in the post you referenced? Apparently, you are not getting an object you are expecting - if that does not work that it could be a driver issue, in which case I would just post it on the Database Administrators forum with probably a better chance getting an answer. – postoronnim Mar 15 '17 at 18:34

0 Answers0