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.