Yes, manually buffering the resultset or buffering the updates is of course a possible workaround. But I would like to avoid that as much as possible.
Another approach is to use limit/offset and then page through the resultset to control the amount of buffering needed. But this just feels like a lot of complexity for such simple task. Anyway - since WAL mode seems to work I will stick with that for now. cheers, Torsten On Mon, May 28, 2018 at 9:10 PM Simon Slavin <slav...@bigfraud.org> wrote: > On 28 May 2018, at 7:56pm, Torsten Curdt <tcu...@vafer.org> wrote: > > > Just to clarify: I have a single thread - so intermixing the stepping > > through a resultset and doing an update requires WAL mode but should be > > fine. Correct? > > Yes, this should work fine. Obviously, one thread is not going to be > trying to do two database accesses at the same time, especially since your > software design uses the result from one SELECT row in order to figure out > what UPDATE to issue. > > You could, course, build up an array of pairs in memory while doing the > SELECT, then consult the array to create all the UPDATE commands once you > have finalized the SELECT. If you do do this don't forget to surround the > UPDATE commands with BEGIN;...COMMIT; . > > One technique I have used in a similar situation was to write all my > UPDATE commands to a long text buffer. This was in an unusual situation > where I needed to get the SELECT done as quickly as possible to avoid > locking up lab equipment. So the software figured out all the UPDATE > commands and concatenated them in a text variable: > > UPDATE for_row_1;UPDATE for_row_2;UPDATE for_row_3;... > > The buffer could get as big as half a megabyte or so. Then I > sqlite3_reset() the SELECT command. Then I submit the entire piece of text > as one long parameter to sqlite3_exec(). Worked perfectly, very quickly, > and didn't take up much more memory than storing the parameters in an array. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
