Slow loading of large in-memory tables

Discussion in 'Python' started by Philipp K. Janert, Ph.D., Sep 7, 2004.

  1. Dear All!

    I am trying to load a relatively large table (about 1 Million
    rows) into an sqlite table, which is kept in memory. The
    load process is very slow - on the order of 15 minutes or
    so.

    I am accessing sqlite from Python, using the pysqlite driver.
    I am loading all records first using cx.execute( "insert ..." ).
    Only once I have run cx.execute() for all records, I commit all
    the preceding inserts with conn.commit()

    I have tried using cx.executemany(), but if anything, this
    makes the process slower.

    I have not tried mucking manually with transactions.
    I have sufficiently convinced myself that the slow part
    is in fact the cx.execute() - not reading the data from file
    or anything else.

    My system specs and versions:
    SuSE 9.1
    Python 2.3.3
    SQLite 2.8.12
    pysqlite 0.5.1
    1 GB memory (I am not swapping, this is not the problem).

    Are there ways to make this process faster?

    Also, I want to keep the DB in memory, since I use it later
    to run a very DB-intensive simulation against it. However,
    this implies that I need to load the DB from the same python
    program which will later run the simulation - I think.

    Any hints appreciated!

    (Please cc me when replying to the list in regards to this
    message!)

    Best regards,

    Ph.
    Philipp K. Janert, Ph.D., Sep 7, 2004
    #1
    1. Advertising

  2. * Philipp K. Janert, Ph.D. (2004-09-07 07:14 +0200)
    > I am trying to load a relatively large table (about 1 Million
    > rows) into an sqlite table, which is kept in memory. The
    > load process is very slow - on the order of 15 minutes or
    > so.
    >
    > I am accessing sqlite from Python, using the pysqlite driver.
    > I am loading all records first using cx.execute( "insert ..." ).
    > Only once I have run cx.execute() for all records, I commit all
    > the preceding inserts with conn.commit()
    >
    > I have tried using cx.executemany(), but if anything, this
    > makes the process slower.
    >
    > I have not tried mucking manually with transactions.
    > I have sufficiently convinced myself that the slow part
    > is in fact the cx.execute() - not reading the data from file
    > or anything else.
    >
    > Are there ways to make this process faster?


    According to [1]:

    pragma temp_store = memory;
    # or any bigger value ('2000' is the default)
    pragma cache_size = 4000;
    pragma count_changes = off;
    pragma synchronous = off;

    Also SQLite makes a commit after every SQL statement (not only those
    that alter the database)[2]. Therefor you have to manually start a
    transaction before the first SQL statement and manually commit after
    the last statement. You have to turn the integrated pysqlite
    committing off to be able to do this:

    connection = sqlite.connect(database,
    autocommit = 1)

    > Also, I want to keep the DB in memory, since I use it later
    > to run a very DB-intensive simulation against it. However,
    > this implies that I need to load the DB from the same python
    > program which will later run the simulation - I think.


    Yes.

    Thorsten

    [1] http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html
    [2] http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html#transactions
    Thorsten Kampe, Sep 15, 2004
    #2
    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. Replies:
    3
    Views:
    3,014
  2. HK
    Replies:
    3
    Views:
    435
  3. mike
    Replies:
    3
    Views:
    376
    Virgil Green
    Jul 11, 2005
  4. JosephByrns

    Slow, then quick then slow

    JosephByrns, Jul 10, 2006, in forum: ASP .Net
    Replies:
    4
    Views:
    2,478
    codezilla94
    Nov 13, 2007
  5. Replies:
    24
    Views:
    656
    Jim Janney
    Feb 23, 2011
Loading...

Share This Page