Do the select and updates run inside a explicit transaction or they
run in individual implicit transactions?
pseudocode:
---------------------
exec(BEGIN); // <- Do you have this?
st_sel = prepare(SELECT ...);
st_upd = prepare(UPDATE tab SET c1=?1, c2=?2, WHERE id=?3);
while(step(st_sel) == SQLITE_ROW)
{
do_something(...);
bind(st_upd, ...);
step(st_upd);
reset(st_upd);
}
finalize(st_upd);
finalize(st_sel);
exec(COMMIT); // <- and this?
---------------------
>> 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;...
Each update is prepared and executed separately.
If you really want a single query you could write something like:
WITH data(id, c1, c2 /*, ... */) AS (VALUES
(123, 'abc', 'xyz' /*, ... */),
(456, 'xxx', 'yyy' /*, ... */),
(789, 'xyz', 'xyz' /*, ... */)
/* ... */
) UPDATE tab
SET (c1, c2 /*, ... */) = (SELECT c1, c2 /*, ... */ WHERE data.id = tab.id)
WHERE id IN (SELECT id FROM data);
2018-05-28 21:32 GMT+02:00, Torsten Curdt <tcu...@vafer.org>:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
