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 :[1], I tried :
- `usr_list= cursorSQL.fetchall()`
- `usr_list= list(cursorSQL.fetchall())`
- `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, since all the slow part appears at the fetch all() line, I would suggest it has more to do with the object returned than with the MySQL connection?
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*
[1]:
http://stackoverflow.com/questions/9402033/python-is-slow-when-iterating-over-a-large-list
[Original Message with better formating] :
http://stackoverflow.com/questions/42816271/python-is-slow-when-iterating-over-a-cursor