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
