mySQL access speed

[Hans Müller]

Hello,

I have some programs doing a lot sql IO in some mySQL databases.
This works very fine and the DBAPI is quite simple to understand.

Now I came to the point where I had to insert millions of lines into a table.
My first aproach was to insert the data using executemany().
That's not bad and fairly simple to use.
But it isn't very fast.
For executemany I have some hundred thousend lines in a list of tuples.
I joined() these lines to form an insert into table values (....) statement and
blew it into the mysql cmdline client via os.popen().
This was 60(!) times faster and loaded my table in seconds!

Is the mySQL module so slow ?

Any ideas to have the mySQL module working faster ?
The popen() way seems quite clumsy and not very pythonic for me,

Greetings
Hans

 

[Dikkie Dik]

... But it isn't very fast.

For executemany I have some hundred thousend lines in a list of tuples.
I joined() these lines to form an insert into table values (....) statement and
blew it into the mysql cmdline client via os.popen().
This was 60(!) times faster and loaded my table in seconds!

Is the mySQL module so slow ?

No. The fact that each statement is atomic makes it slow. Try the
multiple queries, but precede them with a "SET AUTOCOMMIT=0" statement
or use a transaction. You will probably see a tremendous speed increase.

When you combine all the queries into one statement, you are effectively
doing the same.

Best regards,
Dikkie.

 

[John Nagle]

Hello,

I have some programs doing a lot sql IO in some mySQL databases.
This works very fine and the DBAPI is quite simple to understand.

Now I came to the point where I had to insert millions of lines into a table.

If you're loading into an empty table, use the LOAD command. That's
far faster than doing vast numbers of INSERT operations. The
LOAD command loads all the data, unindexed, then builds the indices.
Expect a 10x speed improvement or better.

John Nagle

 

[Dennis Lee Bieber]

For executemany I have some hundred thousend lines in a list of tuples.

Since MySQLdb is coded for versions of MySQL <5.x, which did not
have "prepared SQL" statements and native parameter passing, even
executemany() is just a short cut for many individual execute() calls.
That is -- the adapter has to generate and pass a complete statement for
each "record"; and MySQL itself then has to parse that statement.

SQLite, OTOH, supports prepared SQL, and (though I've not actually
checked source code) the adapter likely has a faster executemany() as it
can send the parameterized SQL for parsing once, followed by sending
packets of just each record's arguments for execution.