Quickest way to insert into SQLite using C API?

Question

I want to do a lot of inserts (let's say a couple of million) into a database as quick as possible. Since I'm calling from C I thought that there might be a shortcut for doing this in the API. I can't help but feel that creating strings from data and having SQLite parse the strings back, all in one call, is less efficient than it could be.

Is there a way to insert data without having to create SQL strings? Are prepared statements the only way to go?

I'm looking for C-specific ways to insert data in the most efficient manner, not general database tips

Answer 1

Start a transaction in the code, and all of the inserts will happen in memory before being written to the disk on a commit. (You may need to chunk this depending on how much memory you have available, and how much data you are inserting) This will be the biggest performance increase that you'll see.

Otherwise, if it's inserts into the same table, rebind your data one row at a time to the same statement after each insert to prevent the text processing overhead. However, compared to transactions, this will be relatively minor. The majority of your insert time will be in disk writes...

Answer 2

DROP INDEX, INSERT, ADD/REBUILD INDEX.

Another General technique SQL for large inserts is to drop the index, insert your x000's of records and then rebuild the index. If you have enough records being inserted you may find this substantially quicker. You can need to benchmark this with your database to find if this is quick for your setup.

Answer 3

It's possible to achieve more than 90 000 inserts-per-second with SQLite and C (that number drops to approximately 60 000 inserts-per-second if you have an index). You should:

• Use a single transaction
• Use prepared statements and the bind methods
• Put rollback journal into memory (if you can)
• Drop the index and re-index after the data is inserted

• Read this SO question for more details: "How do I improve the performance of SQLite?" *

  *Shameless plug: I had many of the same performance questions when I started using SQLite - so I tried to create a thorough Wiki entry (with real benchmarks, and real C code) based on my experience.