[sqlite] Recommended way to delete rows
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 _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 containing every single one of the IDs. Then _step() on it once. Done. Is this even possible? Since there can be a million IDs, I'm not sure if the query can be so long. Which way do you recommend? Are there other ways? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recommended way to delete rows
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 DELETE FROM x WHERE id IN temptablename That might solve your list-length problem (can't say how performantly). -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recommended way to delete rows
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 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 DELETE FROM x WHERE id IN temptablename In this case, SQLite will construct a temporary list of values to hold all the rowids before doing the actual deletions. The overhead of constructing this list might or might not be larger than the overhead of executing already-prepared statements. However, if there are too many IDs, that temporay list will affect the caches, or might even need to be stored on disk. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recommended way to delete rows
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 containing every single one of the IDs. Then _step() on it once. Done. Is this even possible? Since there can be a million IDs, I'm not sure if the query can be so long. 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). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recommended way to delete rows
> 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" 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 a similar speed boost for "INSERT"? Is one by one "INSERT" in a similar way slow as molasses, and wrapping many inserts in a transaction recommended? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recommended way to delete rows
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 > > molasses). > > 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 a similar speed boost for "INSERT"? Is one by one > "INSERT" in a similar way slow as molasses, and wrapping many inserts in a > transaction recommended? > ___ > 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
Re: [sqlite] Recommended way to delete rows
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 a similar speed boost for "INSERT"? Is one by one "INSERT" > in a similar way slow as molasses, and wrapping many inserts in a transaction > recommended? The way SQL works is that theoretically you cannot do any command outside a transaction. No changes are actually made to the database except when a transaction ends. Therefore if you issue "BEGIN TRANSACTION" all the work is actually done when you execute "END TRANSACTION". Until then, SQL is just making a list of the changes you may want to do sometime in the future. Therefore the "END TRANSACTION" is the command that takes all the time. However, SQLite tries to be useful to you. If you forget to do "BEGIN TRANSACTION" before you execute an INSERT or a DELETE, instead of returning an error message it helpfully wraps that single command inside a transaction for you. Therefore that single command makes changes to the database. So it takes a long time. This is why issuing many INSERT or DELETE commands with no transaction declared takes so long. For each separate command SQLite has to do make the change you asked for then do lots of work to make sure that the database file is synchronised with the journal file and the disk is updated to reflect both changes. If you wrap lots of commands inside one transaction SQLite only has to do this synchronization once. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
