Options for high performance SQLite

I'm developing an embedded system, which needs to store and retrieve data very frequently. I am expecting perhaps 100s of writes per second and 10s of reads. The data will arrive in bursts rather than continuous.

I would like to use SQLite to store all this data, but since it is a flash file-system, the writes (INSERTS & UPDATES) are much too slow. I tried setting synchronous=off and this does certainly improve the performance, but I am nervous about corrupting the database.

In my application, power failures would be a rare, but real possibility. I can live with losing some recent data, but absolutely cannot risk losing all the data due to a database corruption.

I开发者_高级运维 also thought about using an in-memory SQLite database as the primary database and periodically sync it to the file-system as discussed in Synchronizing sqlite database from memory to file

Are there any other options I should consider?

---

When a burst of data arrives, be sure to do the series of inserts and updates within a single transaction.

sqlite3_exec(handle, "BEGIN TRANSACTION", NULL, NULL, NULL);

for ( ... ) {
    // Do your inserts/updates
}

sqlite3_exec(handle, "END TRANSACTION", NULL, NULL, NULL);

By default, SQLite puts every insert/update in its own transaction. This can make SQLite quite slow if the underlying filesystem is slow. By declaring your own transactions, you can significantly reduce the amount of actual writing that is done to the filesystem, thereby substantially increasing SQLite's performance.

---

I don't know how large are your data sets, but if they are not too big, then the WAL mode might help. You may experiment with "PRAGMA synchronous=1": This setup does not sync after each transaction, but once in a while. (Default: When 2 MB of new data is written.) SQLite docs say that you might loose a few recent transactions, but the DB won't be corrupted.