Re: [sqlite] WAL and updates

2012-11-28 Thread Richard Hipp
On Tue, Nov 27, 2012 at 11:16 PM, Keith Chew wrote: > I have found that after performing 5000 single transaction inserts, the WAL > size grows to approx 90MB. After a checkpoint, it becomes 0 and the main > DB's size goes up by less than 2MB. Is my observation correct? Ie

Re: [sqlite] WAL and updates

2012-11-27 Thread Keith Chew
> Thanks for this Simon, but unfortunately this is legacy code, which has to > be used for a while. I just did an update to check for column change (we > have a column to tell us the record has changed, so we do not need to check > every column), and the WAL file size growth has dropped

Re: [sqlite] WAL and updates

2012-11-25 Thread Keith Chew
> > > > Okay, if you're doing this set up updates to do synchronisation then > you're going about it a very poor way. We've written a lot on this list > about the problems with synchronisation over the years and you'll find that > your way isn't going to be efficient. > > Instead of keeping a

Re: [sqlite] WAL and updates

2012-11-25 Thread Simon Slavin
On 25 Nov 2012, at 9:12pm, Keith Chew wrote: > Ok, thanks for the all the suggestions, I will find a workaround. The > reason I am asking is that I am using sqlite to perform data > synchronisation between a server and client, and after a day, the WAL file > size can grow

Re: [sqlite] WAL and updates

2012-11-25 Thread Richard Hipp
On Sun, Nov 25, 2012 at 4:12 PM, Keith Chew wrote: > Hi > > Ok, thanks for the all the suggestions, I will find a workaround. The > reason I am asking is that I am using sqlite to perform data > synchronisation between a server and client, and after a day, the WAL file >

Re: [sqlite] WAL and updates

2012-11-25 Thread Keith Chew
Hi Ok, thanks for the all the suggestions, I will find a workaround. The reason I am asking is that I am using sqlite to perform data synchronisation between a server and client, and after a day, the WAL file size can grow to 3GB, quite a bit. I will look at improving the SQL update to reduce

Re: [sqlite] WAL and updates

2012-11-25 Thread Simon Slavin
On 25 Nov 2012, at 4:22pm, Imanuel wrote: > I'm not saying your statement is inefficient, I'm saying it's wrong > because it produces unwanted results. > > If the fields a,b,c ('12','34','56') should be updated to > ('1','2345','6') your statement would fail instead

Re: [sqlite] WAL and updates

2012-11-25 Thread Imanuel
b||'-'||c||'-' != ... > > Maybe there are other drawbacks? > > //Roger > -Ursprungligt meddelande- From: Imanuel Sent: Sunday, November > 25, 2012 5:22 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] WAL > and updates > I'm not saying your statement is inefficient, I'm saying

Re: [sqlite] WAL and updates

2012-11-25 Thread Roger Andersson
Then something like WHERE a||'-'||b||'-'||c||'-' != ... Maybe there are other drawbacks? //Roger -Ursprungligt meddelande- From: Imanuel Sent: Sunday, November 25, 2012 5:22 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] WAL and updates I'm not saying your statement

Re: [sqlite] WAL and updates

2012-11-25 Thread Imanuel
I'm not saying your statement is inefficient, I'm saying it's wrong because it produces unwanted results. If the fields a,b,c ('12','34','56') should be updated to ('1','2345','6') your statement would fail instead of doing the expected update. Which means with every false hit it has less data

Re: [sqlite] WAL and updates

2012-11-25 Thread Simon Slavin
On 25 Nov 2012, at 4:11pm, Imanuel wrote: > Hi Keith > >> UPDATE user SET a=1,b=2,c=3 WHERE a||b||c!=1||2||3; > > It seems to me that this is not reliable. > Think the the following text values: > a='12' > b='34' > c='56' > > If you want to update these values to:

Re: [sqlite] WAL and updates

2012-11-25 Thread Imanuel
Hi Keith > UPDATE user SET a=1,b=2,c=3 WHERE a||b||c!=1||2||3; It seems to me that this is not reliable. Think the the following text values: a='12' b='34' c='56' If you want to update these values to: a='1' b='2345' c='6' Then your statement would not update because '123456' = '123456'.

Re: [sqlite] WAL and updates

2012-11-25 Thread Simon Slavin
On 25 Nov 2012, at 10:46am, Keith Chew wrote: > I found that when an update SQL is issued against a table, the WAL file > gets updated with the new record, even though the record is exactly the > same as the current record. > > [snip] > > Is there a specific reason for

Re: [sqlite] WAL and updates

2012-11-25 Thread Kees Nuyt
On Mon, 26 Nov 2012 00:16:39 +1300, Keith Chew wrote: >Hi Richard > > >> >> UPDATE user SET user_name='Keith' WHERE user_name!='Keith'; >> >> >> >The example I provided was simplified only to explain the scenario. In a >production environment, there are over 40-50 columns,

Re: [sqlite] WAL and updates

2012-11-25 Thread Keith Chew
Hi Richard > > UPDATE user SET user_name='Keith' WHERE user_name!='Keith'; > > > The example I provided was simplified only to explain the scenario. In a production environment, there are over 40-50 columns, and the suggested workaround above is impractical. Regards Keith

Re: [sqlite] WAL and updates

2012-11-25 Thread Richard Hipp
On Sun, Nov 25, 2012 at 5:46 AM, Keith Chew wrote: > Hi > > I found that when an update SQL is issued against a table, the WAL file > gets updated with the new record, even though the record is exactly the > same as the current record. > > Eg user table has 1 record with

[sqlite] WAL and updates

2012-11-25 Thread Keith Chew
Hi I found that when an update SQL is issued against a table, the WAL file gets updated with the new record, even though the record is exactly the same as the current record. Eg user table has 1 record with user_name = 'Keith'. Issuing this SQL will increase the WAL file: update user set