Re: [sqlite] Recommended way to delete rows

2014-02-28 Thread Simon Slavin
On 28 Feb 2014, at 5:18pm, L. Wood wrote: > If I do this, would you expect _step() for the "BEGIN TRANSACTION" query and > _step() for each "DELETE" query to be very fast, but the _step() for the "END > TRANSACTION" query to take most (99%) of the time? > > Would you expect

Re: [sqlite] Recommended way to delete rows

2014-02-28 Thread Adam Devita
Yes. On Fri, Feb 28, 2014 at 12:18 PM, L. Wood wrote: > > I expect #2 to work best. Make sure to enclose the whole thing in an > > explicit transaction (or at least, run large batches within explicit > > transactions; one implicit transaction per deleted row will be slow as >

Re: [sqlite] Recommended way to delete rows

2014-02-28 Thread L. Wood
> I expect #2 to work best. Make sure to enclose the whole thing in an > explicit transaction (or at least, run large batches within explicit > transactions; one implicit transaction per deleted row will be slow as > molasses). If I do this, would you expect _step() for the "BEGIN TRANSACTION"

Re: [sqlite] Recommended way to delete rows

2014-02-28 Thread Igor Tandetnik
On 2/28/2014 9:59 AM, L. Wood wrote: * Prepare query containing single ID with _prepare(). Execute it with _step(). Repeat for each ID. * Call _prepare() with a query with single parameter for the ID. _bind(), _step(), and _reset() - repeat for each ID. * Call _prepare() with a query

Re: [sqlite] Recommended way to delete rows

2014-02-28 Thread Clemens Ladisch
Stephan Beal wrote: > On Fri, Feb 28, 2014 at 3:59 PM, L. Wood wrote: >> I can think of three ways: >> * Call _prepare() with a query with single parameter for the ID. >> _bind(), _step(), and _reset() - repeat for each ID. This is the obvious method to use. >> Are there

Re: [sqlite] Recommended way to delete rows

2014-02-28 Thread Stephan Beal
On Fri, Feb 28, 2014 at 3:59 PM, L. Wood wrote: > I can think of three ways: > Which way do you recommend? Are there other ways? > i don't know that this would be better, but it's a different way: collect the list into a temp table with one field (the to-delete id), then do a

[sqlite] Recommended way to delete rows

2014-02-28 Thread L. Wood
If I already have a collection of row IDs of rows I wish to delete from a table, what is a recommended/fast way to delete them from the table? The collection is just a set/array of integers, not necessarily contiguous. I can think of three ways: * Prepare query containing single ID with