That makes sense. Thanks for the explanation. Unfortunately when I run the more extensive unit test that is built into our system, I still see about a 10% regression (with temp_store=memory and any version >= 3.6.19). This is much better than the 25% regression I was getting in the extensive test when running with the default temp_store value. I am curious why versions prior to 3.6.19 don't enter the if (subjRequiresPage(pPg) ) in pager_write, but ever since 3.6.19 they do. (for my insert or replace unit tests).
Are there any options to avoid this? I have tried replacing my "insert or replace" with "delete" then "insert", which is almost as fast as the older code, so that is one option for me. thanks againm, katrina -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy Sent: Thursday, January 24, 2013 4:44 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] performance regression when using "insert or replace" On 01/24/2013 02:48 AM, Heiles, Katrina wrote: > Thank you so much!! This is great news. > > One question: according to the tempfiles.html doc... Section 3.0 > states that "The rollback journal, master journal, and statement > journal files are always written to disk. But the other kinds of > temporary files might be stored in memory only and never written to > disk." > > Section 2.3 states: "the statement journal is also omitted if an > alternative conflict resolution algorithm is used." I think both of those statements are out of date. Rollback and master journal files (as well as *-wal files) are always created on disk, as these are required by database recovery in the event of an application crash or system (power) failure. But these days statement journals may be stored in memory if SQLite is configured with "PRAGMA temp_store=memory" or equivalent. > ---------------------------------------------------------------------- > -------------------- > > /* If the statement journal is open and the page is not in it, > ** then write the current page to the statement journal. Note that > ** the statement journal format differs from the standard journal > format ** in that it omits the checksums and the header. */ if( > subjRequiresPage(pPg) ){ rc = subjournalPage(pPg); } > ---------------------------------------------------------------------- > ---------------- > > Doesn't going into this "if" statement mean that a statement journal > is getting created? If so, isn't a statement journal always written to > disk (based on above snipet from section 3)? Yes and no, respectively. See function openSubJournal() in pager.c. Dan. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
