You could also set the TEMP_STORE to memory and copy the table to a temporary table. Then update the disk table from the temporary table using the 'changed' scheme below. Example:
Pragma temp_store=2; Create table datatable (tag text, value real); Create unique index dtags on datatable (tag); create temporary table tdatatable as select tag, value, 0 as changed from datatable; create unique index ttags on tdatatable (tag); create index ttchg on tdatatable (changed, tag); then do your normal updates against tdatatable update tdatatable set value=?, changed=1 where tag=? Then periodically, Begin immediate; insert or replace into datatable select tag, value from tdatatable where changed=1; update tdatatable set changed=0 where changed=1; Commit; The temp table is only visible to the single process/connection which created it though ... --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Simon Slavin > Sent: Thursday, 20 September, 2012 06:21 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] minimizing flash writes > > > On 20 Sep 2012, at 6:42am, Rojo James <rojoja...@gmail.com> wrote: > > > Is it possible to flush only the changed values from the in-memory database > > to the hard-disk and not backup the whole database (may be using something > > like a transaction log)? Is there some built-in function for this? > > Keep a column in your table(s) called 'changed'. After you startup and have > read the database into memory, set the 'changed' values in this memory copy > to 0/FALSE: > > UPDATE myTable SET changed=0 > > When a row is changed set its "changed" to 1. When you are ready to flush > memory look only at the rows with changed=1, write those away, then execute > the above UPDATE command again. > > > Is it possible to have a disk based database, with the WAL file residing in > > the RAM? In this case less IO cycles are required and the back-upping of > > data to flash drive is simpler. > > There are ways to mess with the drive used for temporary files, and point it > at a RAM drive. But that requires RAM drive support. I don't think you can > define it as ":memory:". > > > What other possibility do I have to minimize the write cycles to the flash > > memory? > > I think your idea of having a memory-based copy will work fine. And it has > the advantage of being completely standard SQLite without requiring any weird > coding or setup which another programmer might have trouble understanding. > > Simon. > _______________________________________________ > 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
