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."
When I debug my test, I enter the if statement below (at the bottom of the
pager_write() function).
With pragma temp_store=file (the default), it creates temporary files, which is
much slower, and with
pramga temp_store=memory, it is fast.
------------------------------------------------------------------------------------------
/* 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);
}
--------------------------------------------------------------------------------------
My question is:
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).
Also, why is a statement journal created at all here (since based on section
2.3, it should be omitted
because I'm using an alternative conflict resolution algorithm (insert or
replace).
thanks SO MUCH for your help!
katrina
-----Original Message-----
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Dan Kennedy
Sent: Wednesday, January 23, 2013 11:51 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] performance regression when using "insert or replace"
On 01/23/2013 11:04 PM, Heiles, Katrina wrote:
> Hi Dan,
> Yes, this resolves the problem. performance comes back up to 31K/sec.
>
> What are the risks of using this as a workaround? Data integrity is
> very important to us so I'm curious what effect this pragma would have.
No effect on data integrity or durability.
SQLite uses temporary files for various reasons - statement journals, temporary
tables (those created with CREATE TEMP TABLE), to materialize views and
sub-queries when required, that sort of thing. If you set "PRAGMA
temp_store=memory", then it uses malloc'd memory instead of temporary files for
these things.
See also:
http://www.sqlite.org/tempfiles.html
Dan.
>
> thanks, katrina
>> Out of interest, is performance improved any with 3.7.15.2 if you
>> execute "PRAGMA temp_store = memory" before calling _do_batch_insert()?
>>
>> Thanks,
>> 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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
