performance of insert with python and sqlite3

Question

I'm doing big batch inserts into an SQLite3 database and I'm trying to get a sense for what sort of performance I should be expecting versus what I'm actually seeing.

My table looks like this:

cursor.execute(
            "CREATE TABLE tweets(
             tweet_hash TEXT PRIMARY KEY ON CONFLICT REPLACE,
             tweet_id INTEGER,
             tweet_text TEXT)"
        )

and my inserts look like this:

cursor.executemany("INSERT INTO tweets VALUES (?, ?, ?)", to_write)

where to_write is a list of tuples.

Currently, with about 12 million rows in the database, inserting 50 000 rows is taking me around 16 minutes, running on a 2008 macbook.

Does this sound reasonable, or is there something gross happening?

Have you tried inserting multiple values in each query? Maybe not all of them at once, since you would have to create a very large string, but maybe 100 each? In my experience this improves insertion speed a lot. — Gustav Larsson
– Gustav Larsson, Commented Jul 25, 2013 at 20:02
@GustavLarsson: executemany is supposed to take advantage of preparing the query and streaming in the per-row columns already. — Martijn Pieters
– Martijn Pieters, Commented Jul 25, 2013 at 20:03
@GustavLarsson: How were you proposing creating multiple inserts? execute() and executemany() only allow for one statement. — Martijn Pieters
– Martijn Pieters, Commented Jul 25, 2013 at 20:04
i think something like for i in range(0,len(my_list),100):execute_many(qry,my_list[i:i+100]) at a guess — Joran Beasley
– Joran Beasley, Commented Jul 25, 2013 at 20:31
Does executemany use transactions? If not, sqlite will internally wrap each and every insert statement with an implicit transaction, which can cause a huge performance hit on bulk inserts. See these for a little more info: sqlite.org/faq.html#q19 or stackoverflow.com/questions/3852068/sqlite-insert-very-slow — rutter
– rutter, Commented Jul 25, 2013 at 21:24

drastega · Accepted Answer · 2013-07-25 23:54:58Z

8

As I understand the main reason of bad performance is time you waste to commit many SQLite transactions. What to do?

Drop the indexes, then

PRAGMA synchronous = OFF (or NORMAL)

Insert blocks of N rows (define N, try N=5000 to start). Before inserting block do

BEGIN TRANSACTION

after inserting do

COMMIT

See also http://www.sqlite.org/faq.html#q19

edited Jul 25, 2013 at 23:54

answered Jul 25, 2013 at 23:26

drastega

1,7735 gold badges33 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

performance of insert with python and sqlite3

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related