On 5/12/15, Zaumseil Ren? <RZaumseil at kkg.ch> wrote: >>I think that operation will go MUCH faster if you (1) turn off >>auto_vacuum, (2) use journal_mode=DELETE, and (3) put all of the >>DELETE operations inside a single transaction. >> >>D. Richard Hipp > > I have used auto_commit as a replacement for vacuum. Because I only add new > data and only delete from the end I thought it would be faster (no extra > scan to rebuild ).
Humor me. Try your experiment without auto_vacuum. Leave everything else the same. See what performance difference you get. > > I use WAL mode to not block the reader processes. Do you suggest I should > switch to delete mode before I really delete data and switch to wal back > after I'm done? Maybe WAL mode will be OK. Try the experiment. > > I have a prepared statement "delete from tablename" for each table. I start > a transaction and for each table I do sqlite3_reset() and sqlite3_step(). > After this I end the transaction. > Should I place all the delete statements in a single statement? > You should be running sqlite3_step() and sqlite3_reset(), not the other way around. But otherwise, what you are doing is fine. -- D. Richard Hipp drh at sqlite.org
