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