Thanks you Simon for your insightful reply, appreciate it. I will make the respective changes and get back you with the results shortly.
From: Simon Slavin <slav...@bigfraud.org> To: Runcy Oommen <net4ru...@yahoo.co.in>; General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Thursday, June 13, 2013 4:06 PM Subject: Re: [sqlite] Fine tuning SQLite performance with WAL mode On 13 Jun 2013, at 7:34am, Runcy Oommen <net4ru...@yahoo.co.in> wrote: > PRAGMA journal_mode = wal; WAL is the new modern way to do things and is generally better in lots of ways. Use it unless it causes problems for you. > PRAGMA wal_autocheckpoint = 10; > > Now I know that the default wal_autocheckpoint is 1000, does it affect either > positive or negatively with my current value of 10? Don’t do this unless you have a great reason for it. It makes SQLite update the files on disk extremely often, which means that anything that makes changes to your database will take a very long time to execute. I recommend you leave the value at its default. If you have a particular point in your application at which the database on file absolutely must be up-to-date (I’m not just talking normal transactions here, but more like a time when you expect power loss or USB drive disconnection) then at that point execute this command: PRAGMA wal_checkpoint Apart from that, it’s just things like ... > About 190+ SELECT, INSERT, DELETE statements executed every 10 seconds > (whether in idle or use) If any of these things go together, execute them all in a transaction (including the SELECT statements). This means (simplified) that SQLite only has to do one lot of locking for the whole transaction rather than locking for each statement. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
