Into my SQLite database (with a table and a primary key consisting of two integers) I insert around 1GB of data. Creating a primary key implicitly creates an index which slows down inserts to a crawl after a few commits so I want to temporarily disable that index.
When dropping the primary key's automatic index SQLite throws an error. I could have the application make transparent copies of the database on the network drive, modify, then merge it back. As opposed to most SQLite/NFS questions I don't need access concurrency. What is the correct way to do this?
I'm using:
PRAGMA synchronous = OFF
PRAGMA journal_mode = OFF
PRAGMA locking_mode = EXCLUSIVE
PRAGMA temp_store = MEMORY
I'm inserting in batches. Every next batch is slower to commit than the previous one (assumingly due to size of the index). I tried batches of between 10k and 50k rows (tuples of two integers and a float).
If you'll be able to merge key (I think you're using 32bit, while sqlite using 64bit, so it's possible) and fill data in sorted order I bet you will fill in your first Gb with the same performance as second and both will be fast enough.