Synchronizing sqlite database from memory to file

Question

I'm writing an application which must log information pretty frequently, say, twice in a second. I wish to save the information to an sqlite database, however I don't mind to commit changes to the disk once every ten minutes.

Executing my queries when using a file-database takes to long, and makes the computer lag.

An optional solution is to use an in-memory database (it will fit, no worries), and synchronize it to the disk from time to time,

Is it possible? Is there a better way to achieve that (can you tell sqlite to commit to disk only after X queries?).

Can I solve this with Qt's SQL wrapper?

Answer 1

If you're executing each insert within it's own transaction - that could be a significant contributor to the slow-downs you're seeing. Perhaps you could:

• Count the number of records inserted so far
• Begin a transaction
• Insert your record
• Increment count
• Commit/end transaction when N records have been inserted
• Repeat

The downside is that if the system crashes during that period you risk loosing the un-committed records (but if you were willing to use an in-memory database, than it sounds like you're OK with that risk).

Answer 2

A brief search of the SQLite documentation turned up nothing useful (it wasn't likely and I didn't expect it).

Why not use a background thread that wakes up every 10 minutes, copies all of the log rows from the in-memory database to the external database (and deletes them from the in-memory database). When your program is ready to end, wake up the background thread one last time to save the last logs, then close all of the connections.