SQLite Forum

WAL mode speed vs normal comparison
It entirely depends.  And like everything else, the "total" time is mostly the same for the same amount of work, you are just calling in a plastic surgeon to do a "little nip and tuck here in the belly" and "let it hang out more over there in the ass".

It also depends whether you are talking about a 4 page database of a 4 trillion page database.  Or perhaps somewhere in between.  It also depends how your I/O is scattered across those 4 trillion pages.  And how good or bad (Windows falls in the canna-do-at-all category) is at doing I/O, particularly scatter-gather I/O.


In a "normal" database, writes are made randomly directly to the database file itself, and copies of the "pre-change" pages are written to a log file.  Committing a transaction means deleting the log.  Reads are always direct operations against the database file.  One connection cannot "read" the database at the same time another is "writing" to it.

In a WAL database, writes are made sequentially to a "difference file" (not randomly) -- and sequential writes are faster than random writes (this is where the plastic surgeon is doing the nip and tuck).  All reads are double indirect because they have to check for changes in the "difference file" and therefore operate somewhat more slowly than not having to do so (this is one of the places where the plastic surgeon lets the flab hang out).  Occasionally the database needs to be "checkpointed" by copying the sequential pages from the WAL file to the database (read sequential write random).  This is the other place where the plastic surgeon lets the flag hang out.  It is also where all the extra inflamation comes from.  However, you now have the advantage that a connection can "read" from the database at the same time as another connection is writing to the database (because it is not writing to the database, it is writing to the "difference file").

So, as you can see, it is both faster and slower all at the same time.