Phil,
Yes its complicated. Yes its doable! But if you want performance its going to
be a bit complicated.
Sqlite does not allow concurrent read/write even from multiple threads!
Step back a bit, I've answered the question: yes you may disable journalling.
But the real problem your having is concurrency and performance. Disabling
journalling will get you into big trouble! So that really isn't an option,
forget about that!!!
So the only thing left is the performacnce... Try my suggestion, you might be
surprised in that it doesn't take that much more time to write 10 records vs
one!
Another solution to the issue might be a condition variable that you could kick
from the reader thread! Thus if a reader really needs to read, the blocking
write thread could catch the cv/mutex and wake up, commit allowing the reader
to sleep.
Now that you've opened the threading can of worms take a look an the "shared
cache" feature! This might be very helpful to you. I think there was some
mention of reading dirty pages?? This might be just the ticket for your app!!
Phil Sherrod <[EMAIL PROTECTED]> wrote: 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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
