When you're inserting in WAL mode everything will basically have to be written twice. First the new page data will be written to the WAL, and then when a checkpoint happens the WAL file will be copied over to the main database file.
With rollback journal mode the contents of the original page are written to the rollback journal, then the data is written to the main database file. But if the page being written to is brand new (because the database size in increasing), then there is nothing to be written to the rollback journal, and only 1 write needs to happen. (Same if the page being written used to be a free page... I think) So if there's unused room in the file for the inserts, then the two modes should be similar, but if it's a brand new database file, or your insert is going to grow the file size, then rollback mode will result in fewer writes being needed. So technically the "commit" times are probably similar, but it's probably the "checkpoint" time that's killing you. Though I _am_ still curious as to why your insert times are twice as long. (Someone correct me if I'm off on this) -----Original Message----- From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of Andrew Cunningham Sent: Monday, July 01, 2019 2:04 PM To: SQLite Maillist <sqlite-users@mailinglists.sqlite.org> Subject: [sqlite] WAL mode much slower in certain use cases I am using SQLite 3.24.0, as a single user persistent data store for storing simulation data. The database can grow to many gigabytes as the software can ingest a lot of binary data which I store as multiple BLOBs. In the following example I am reading several 22GB of data into the DB. Times in seconds In WAL mode Read data & creating DB records 503.227 Commit of transaction,close database 334 In non-WAL mode Read data & creating DB records 244 Commit of transaction ,close database 4 WAL mode is 80-100x slower to commit to the database. I am probably abusing WAL mode, but I do not see anything in the documentation which says "don't use WAL mode in the following use case". Full disclosure, I am using ODB ( C++ ORM) that hides what is going on under the hood, but my experience is that in a simple use case like this ODB is not the problem. Andrew _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
