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

Reply via email to