>> 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. >
It was no great difference. And without auto_vacuum I can not shrink the file. >> >> 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. It was easier this way and not explicitely recommended otherwise. I have done some experiments offline using a large db (ca.15 GB). First select on large tables (ca. 1 million values take ca 1min.) Following selects are fast (ca. 300ms) The same goes for delete's. Vacuum on such a beast takes forever (I canceled after 20 min.) Could it be the data are too fragmented? If I delete only the last part of the data the code works ok (1 till 2 minutes). Thank you Rene Kernkraftwerk Goesgen-Daeniken AG CH-4658 Daeniken, Switzerland Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
