Insert performance using python and sqlite3

Question

I am doing big inserts into the SQLite3 database, and I'm trying to figure out what kind of performance I should expect from what I actually see.

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, the database contains about 12 million rows, inserting 50 000 rows takes about 16 minutes, working in the 2008 Mac.

Does this sound reasonable or is there something rude?

Answer 1

As I understand it, the main reason for poor performance is that you spend time committing many SQLite transactions. What to do?

Drop indices, then

 PRAGMA synchronous = OFF (or NORMAL) 

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

 BEGIN TRANSACTION 

after pasting do

 COMMIT 

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