Thank you for your tips!! On Thu, Sep 20, 2012 at 2:51 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
> > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
