Holding commits with a timeout is a feasible solution. However, in my application it is somewhat complex to implement. Multiple threads are accessing the database, and read requests usually run in a different thread than writes. I don't want reads to be blocked while a commit timeout waits, so a read would have to force a commit. I don't think one thread can commit transactions for another thread, so I would have to set up an inter-thread queueing system to allow readers to notify writers in different threads that commits need to be done. Since commits are done in multiple places, this approach will get messy fast.
I think removing the FlushFileBuffers call (which is normally done on every commit) is the best solution. It is not as fast as totally turning off journaling, but it allows my program to run 20 times faster without the complexity of trying to hold commitments. Also, journaling _is_ being done (just not forced to the disk). So if my application crashes but Windows continues to run, the journaling will eventually get flushed from Windows cache to disk, and it should be available for a rollback. If I can figure out how to totally turn off journaling, I will do some timing tests to see how much that speeds things up. It certainly won't be a factor of 20, but a factor of 2 is possible. A new pragma "journaling=[off|on]" would be nice. Key wrote: Removing the journalling will certainly cause you lots of grief in the event of a "crash"... You could do the following, The write code (inserts) will queue incoming data into an "array/storage in memory etc..." When the first row is captured set a timer. When either the timer expires or you reach a row limit threshold, write the data to sqlite in a batched transaction. This way you get good performance and reliablity! But if you code crashes and you don't keep persistent what was in memory you'll loose that data. You'll probably need to do some tuning of the timer/row limits to get a balance between performance and potential data loss. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users