mySQL access speed

Discussion in 'Python' started by Hans Müller, Nov 16, 2009.

  1. Hans Müller

    Hans Müller Guest

    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
     
    Hans Müller, Nov 16, 2009
    #1
    1. Advertising

  2. Hans Müller

    Dikkie Dik Guest

    > ... 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.
     
    Dikkie Dik, Nov 16, 2009
    #2
    1. Advertising

  3. Hans Müller

    John Nagle Guest

    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
     
    John Nagle, Nov 17, 2009
    #3
  4. On Mon, 16 Nov 2009 22:49:30 +0100, Hans Müller <>
    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://wlfraed.home.netcom.com/
     
    Dennis Lee Bieber, Nov 19, 2009
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ham

    I need speed Mr .Net....speed

    Ham, Oct 28, 2004, in forum: ASP .Net
    Replies:
    6
    Views:
    2,343
    Antony Baula
    Oct 29, 2004
  2. efiedler
    Replies:
    1
    Views:
    2,053
    Tim Ward
    Oct 9, 2003
  3. Replies:
    2
    Views:
    2,296
    Howard
    Apr 28, 2004
  4. Replies:
    2
    Views:
    335
    Christopher Benson-Manica
    Apr 28, 2004
  5. Weng Lei-QCH1840
    Replies:
    1
    Views:
    184
    Thomas
    Aug 15, 2003
Loading...

Share This Page