On 6/18/07, John Stanton <[EMAIL PROTECTED]> wrote:
Andre du Plessis wrote: > How can one optimize the creation of the journal file. The problem is > this, for our system which is an event based one each message needs to > be insterted and committed to the database (guaranteed), this results in > a commit per insert, this was obviously unacceptably slow and according > to the lists by design you need to do more bulk transactions, and with > some efford I modified it with a combination of bulk and temporary > tables to do quite a few at a time, but the problem remains that > committing is terribly slow.
I, too, discovered this. By at least a factor of 50 to 100 times as slow with my current hardware (stripe & mirror hardware card software drivers) than by running in "PRAGMA SYNCHRONOUS=OFF;" mode. In my case I chose to accept the risk that the OPERATING SYSTEM or MACHINE will crash before the data is truly written to the hard drive platters. My alternative that I may still implement was to have an interior journaling table which recorded each operation, and then applied the operations in bulk within one transaction to the desired tables. I am not a SQlite expert by any stretch of the imagination, but these techniques worked for me. --andy