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'm thinking the journal file, the fact that it is created written and deleted each time which is slowing it down, Is there not a way to create and set up a permanent journal file pre-allocated to a certain size, so sqlite does not have to go through the OS each time to create and delete this file? Along the same lines of this question, is there a way to fix the initial size of the DB, ie set it to pre-allocate 700mb for instance so that no growing of the db file is needed until the space is exceeded, may also speed things up. Thank you very much in advance.