Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > mySQL access speed

Reply
Thread Tools

mySQL access speed

 
 
Hans Müller
Guest
Posts: n/a
 
      11-16-2009
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
 
Reply With Quote
 
 
 
 
Dikkie Dik
Guest
Posts: n/a
 
      11-16-2009
> ... 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.
 
Reply With Quote
 
 
 
 
John Nagle
Guest
Posts: n/a
 
      11-17-2009
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
 
Reply With Quote
 
Dennis Lee Bieber
Guest
Posts: n/a
 
      11-19-2009
On Mon, 16 Nov 2009 22:49:30 +0100, Hans Müller <(E-Mail Removed)>
declaimed the following in gmane.comp.python.general:

> 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.
--
Wulfraed Dennis Lee Bieber KD6MOG
http://www.velocityreviews.com/forums/(E-Mail Removed) HTTP://wlfraed.home.netcom.com/

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL-python-1.2.2 install with no mysql washakie Python 4 01-15-2008 08:15 PM
"mysql.h: No such file or directory" when building MySQL-python francescomoi@europe.com Python 2 05-11-2005 03:12 PM
DBD:mysql doesn't read mysql option file /etc/my.cnf file JL Perl 0 01-28-2005 03:19 AM
speed speed speed a.metselaar Computer Support 14 12-30-2003 03:34 AM
"Pure Python" MySQL module like Net::MySQL Ravi Python 6 07-21-2003 06:53 PM



Advertisments