Prev: SCGIServer and unusal termination
Next: YIELD_VALUE Byte Code
From: Hans Müller on 16 Nov 2009 16:49
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
From: Dikkie Dik on 16 Nov 2009 17:28
> ... 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.
From: John Nagle on 17 Nov 2009 01:31
Hans M�ller wrote:
> 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
 | 
Pages: 1
Prev: SCGIServer and unusal termination
Next: YIELD_VALUE Byte Code