sqlsqliteindexinginsertprimary-key

Disabling primary key index while inserting


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).


Solution

    1. You can't remove embedded index since it's the only address of row.
    2. Merge your 2 integer keys in single long key = (key1<<32) + key2; and make this as a INTEGER PRIMARY KEY in youd schema (in that case you will have only 1 index)
    3. Set page size for new DB at least 4096
    4. Remove ANY additional index except primary
    5. Fill in data in the SORTED order so that primary key is growing.
    6. Reuse commands, don't create each time them from string
    7. Set page cache size to as much memory as you have left (remember that cache size is in number of pages, but not number of bytes)
    8. Commit every 50000 items.
    9. If you have additional indexes - create them only AFTER ALL data is in table

    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.