Ad 1) You may be able to speed up deletion if you can partition your logging tables by time, e.g. each table holds the changes within a certain time frame or a fixed number of changes. When you jump back in time, dropping the tables created after the target timestamp is faster than deleting the individual records. This will, however, require that your application handle reading data from a sequence of files.
Ad 2) Network filesystem locking is notoriously broken. SQLite depends on file locks being reliable. -----Urspr?ngliche Nachricht----- Von: Zaumseil Ren? [mailto:RZaumseil at kkg.ch] Gesendet: Donnerstag, 02. April 2015 07:44 An: 'sqlite-users at mailinglists.sqlite.org' Betreff: [sqlite] windows network and wal mode Hi there, I have used sqlite to store parameter changes with a time stamp in a simulation environment. Because of the number of parameters (2000+) and the max. change rate up to 50ms I have a separate table for each parameter with time stamp and value. The time stamp is used as "integer primary key asc". So is no need of an internal rowid. The database is opened from one process in wal mode with the following settings: PRAGMA synchronous = 1 PRAGMA journal_mode = WAL PRAGMA wal_autocheckpoint = 10000 PRAGMA page_size = 4096 PRAGMA cache_size = 5000 Only one process will write to the database. It is also possible to go back in time and then start from there. Currently I remove all values from the tables after the specified time. New values can be added after this. With larger backtrack intervals I have performance problems. Question 1: Can I speed up the deletion? Question 2: I will only read the data with a second program on another computer. I have read it is not possible to use sqlite in wal mode over a network. Is it so in my scenario? Would it be possible if I can stop the writing for the duration of the read? Is there another solution? 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. _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
