Re: [sqlite] Performance with journal_mode = off

In reference to your needs one more suggestion : >> If you put the blobs outside of a sqlite database and store your householding, indexing data inside your sqlite data i would suggest to use journal mode = on because your journal file and database file without the blobs has a small amount

Re: [sqlite] Performance with journal_mode = off

From : http://www.sqlite.org/pragma.html#pragma_journal_mode The OFF journaling mode disables the rollback journal completely. No rollback journal is ever created and hence there is never a rollback journal to delete. The OFF journaling mode disables the atomic commit and rollback

Re: [sqlite] Performance with journal_mode = off

On 29.03.2013 19:42, Jeff Archer wrote: From: "James K. Lowden" To: sqlite-users@sqlite.org Your experiment is telling you different: transaction control costs more than I/O. But shouldn't transactions be disabled when journal_mode = off? Maybe that is a faulty

Re: [sqlite] Performance with journal_mode = off

>On Fri, Mar 29, 2013 at 4:05 PM, Simon Slavin wrote: > >No. The two things have nothing to do with one-another. Transactions are >about grouping changes together >so that either they all happen or none happen. Journalling is about surviving >through crashes and

Re: [sqlite] Performance with journal_mode = off

On 29 Mar 2013, at 6:42pm, Jeff Archer wrote: > But shouldn't transactions be disabled when journal_mode = off? No. The two things have nothing to do with one-another. Transactions are about grouping changes together so that either they all happen or none

Re: [sqlite] Performance with journal_mode = off

>Date: Thu, 28 Mar 2013 14:13:51 +0100 >From: ibrahim > >... You should compare > >a) Journal mode=on w/wo single transaction >b) Journal mode=off w/wo single transaction So, this means I can use transaction when I have journal_mode = off? I did not understand that

Re: [sqlite] Performance with journal_mode = off

>From: "James K. Lowden" >To: sqlite-users@sqlite.org > > Your experiment is telling you different: transaction control costs more than I/O. But shouldn't transactions be disabled when journal_mode = off? Maybe that is a faulty assumption. If so, what is the point of

Re: [sqlite] Performance with journal_mode = off

On 28.03.2013 14:03, Jeff Archer wrote: On Thu, Mar 28, 2013 at 8:24 AM, ibrahim wrote: On 28.03.2013 13:09, Jeff Archer wrote: On Wed, Mar 27, 2013 at 6:35 PM, Simon Slavin wrote: Reasonable figures. With 5764 writes to the disk in

Re: [sqlite] Performance with journal_mode = off

On Thu, 28 Mar 2013 08:43:50 -0400 Jeff Archer wrote: > > When you turn off journalling, you save something; when you > > consolidate the activity into a single transaction, you save > > something else. What you're seeing is that the

Re: [sqlite] Performance with journal_mode = off

On 28 Mar 2013, at 12:43pm, Jeff Archer wrote: > But the question is: Why? Without journaling only half as > much writing to disk should occur so why would it take longer? But you are still doing 5764 sets of writing. One per transaction (which, since you

Re: [sqlite] Performance with journal_mode = off

On Thu, Mar 28, 2013 at 9:03 AM, Jeff Archer < jsarc...@nanotronicsimaging.com> wrote: > > Yes, I have read this. (And now re-read it) > > So, since much more work must be done when using journal file, why > does it take longer to do the inserts when there is NO journal file? Much work must

Re: [sqlite] Performance with journal_mode = off

On Thu, Mar 28, 2013 at 8:24 AM, ibrahim wrote: > On 28.03.2013 13:09, Jeff Archer wrote: >> >> On Wed, Mar 27, 2013 at 6:35 PM, Simon Slavin >> wrote: >>> >>> Reasonable figures. With 5764 writes to the disk in separates >>> transactions you

Re: [sqlite] Performance with journal_mode = off

On 28.03.2013 13:09, Jeff Archer wrote: On Wed, Mar 27, 2013 at 6:35 PM, Simon Slavin wrote: Reasonable figures. With 5764 writes to the disk in separates transactions you have quite a lot of reading of data plus 5764 attempts to update the database file. The updates

Re: [sqlite] Performance with journal_mode = off

On 27.03.2013 22:55, Jeff Archer wrote: On Wed, Mar 27, 2013 at 5:46 PM, David King wrote: I am populating a database with 5764 records using the exact same data set each time into a newly created file. When I use no explicit transactions (default atomic commit) it takes

Re: [sqlite] Performance with journal_mode = off

On Thu, Mar 28, 2013 at 8:35 AM, Niall O'Reilly wrote: > > On 28 Mar 2013, at 12:09, Jeff Archer wrote: > >> But my most basic question remains. Why is single transaction faster >> than PRAGMA journal_mode = off? >> >> Seems to me that with no journal there should only be

Re: [sqlite] Performance with journal_mode = off

On 28 Mar 2013, at 12:09, Jeff Archer wrote: > But my most basic question remains. Why is single transaction faster > than PRAGMA journal_mode = off? > > Seems to me that with no journal there should only be single set of > writes to the actual db and that journaling would double the number of

Re: [sqlite] Performance with journal_mode = off

On Wed, Mar 27, 2013 at 6:35 PM, Simon Slavin wrote: > > Reasonable figures. With 5764 writes to the disk in separates transactions > you have quite a lot of reading of data plus 5764 attempts to update the > database file. The updates have to be done in the right order,

Re: [sqlite] Performance with journal_mode = off

On Wed, 27 Mar 2013 17:55:00 -0400, Jeff Archer wrote: >On Wed, Mar 27, 2013 at 5:46 PM, David King wrote: >> >> > I am populating a database with 5764 records using the exact same data set >> > each time into a newly created file. >> > When

Re: [sqlite] Performance with journal_mode = off

On 27 Mar 2013, at 9:55pm, Jeff Archer wrote: > Which is why I expected journal_mode = off to make it faster. But it > is 3 seconds faster when I leave journaling enabled and do all writes > within a single transaction. >> When I set journal_mode = off, same

Re: [sqlite] Performance with journal_mode = off

On Wed, Mar 27, 2013 at 5:46 PM, David King wrote: > > > I am populating a database with 5764 records using the exact same data set > > each time into a newly created file. > > When I use no explicit transactions (default atomic commit) it takes 17.7 > > seconds. > > When I

Re: [sqlite] Performance with journal_mode = off

> I am populating a database with 5764 records using the exact same data set > each time into a newly created file. > When I use no explicit transactions (default atomic commit) it takes 17.7 > seconds. > When I set journal_mode = off, same operation takes 5.5 seconds. > If I do all 5764 inserts

[sqlite] Performance with journal_mode = off

Could someone please confirm if this makes sense. It is not what I expected. I have repeated several times so I believe these are the correct numbers. I am populating a database with 5764 records using the exact same data set each time into a newly created file. When I use no explicit