Re: [sqlite] How to interrupt a long running update without roll back?

On 25 Jun 2013, at 1:41am, jhnlmn wrote: > Using trigger during insert slows down insert by about 20%. > This is not much, but unnecessary as long as AUTOINCREMENT > does not reach the maximum value. > As other people mentioned, this is unlikely to happen > in our lifetime.

Re: [sqlite] How to interrupt a long running update without roll back?

Keith Medcalf writes: > ... you can create a trigger that adds the rowid of > rows inserted (ie, that you need to update) into another table. Using trigger during insert slows down insert by about 20%. This is not much, but unnecessary as long as AUTOINCREMENT does not reach the

Re: [sqlite] How to interrupt a long running update without roll back?

Peter Aronson writes: > create trigger inter_update before update on my_table > begin >    select raise(ignore) where my_function() = 1; > end; No, this is the worst approach so far. This "raise(ignore)" does abort that single update of that particular row, but the loop

Re: [sqlite] How to interrupt a long running update without roll back?

Bit long ago to recall exactly, but to be honest I think it was quite more sinister even, if memory serves, I was modifying the rowids too. Either way I avoid messing with rowids or relying on them altogether now as a short-cut to updating - but it s quite safe to do so. As an aside, while

Re: [sqlite] How to interrupt a long running update without roll back?

Regarding: ...then went horribly wrong at some point after rowids consumed all of 32 bits... If your application treated ROWIDs as 32-bit integers, that may possibly have been the cause of your problem, since ROWID's are 64-bit integers. The maximum ROWID is 9223372036854775807

Re: [sqlite] How to interrupt a long running update without roll back?

On 2013/06/23 00:15, Simon Slavin wrote: //... UPDATE myTable SET C1 = [calculation] WHERE rowid > [lastupdatedrowid] then update the updateprogress table. Since there is no searching needed, there’s no need for scanning the table, and no need for any indexes. Simon. One caution on this

Re: [sqlite] How to interrupt a long running update without roll back?

Keith Medcalf wrote: > > I considered that as well, but it's not clear how much benefit you get > > over the autoincrement scheme: the PK-index is there either way, so > > that's not a slowdown. The split table approach also makes > > query-writing and indexing more complex, so at a minimum you'd

Re: [sqlite] How to interrupt a long running update without roll back?

> I considered that as well, but it's not clear how much benefit you get > over the autoincrement scheme: the PK-index is there either way, so > that's not a slowdown. The split table approach also makes > query-writing and indexing more complex, so at a minimum you'd probably > want to make a

Re: [sqlite] How to interrupt a long running update without roll back?

On 22 Jun 2013, at 10:50pm, Ryan Johnson wrote: > I considered that as well, but it's not clear how much benefit you get over > the autoincrement scheme: the PK-index is there either way, so that's not a > slowdown. The split table approach also makes

Re: [sqlite] How to interrupt a long running update without roll back?

On 21/06/2013 8:41 PM, Keith Medcalf wrote: No, in my case user does not touch the DB - he can only add or delete items, but all DB modifications are done by my app and I have a full control over all the values. All I need is to find the most efficient way to do that. Therefore, I do not

Re: [sqlite] How to interrupt a long running update without roll back?

> No, in my case user does not touch the DB - he can only add or delete > items, > but all DB modifications are done by my app and I have a full control over > all the values. All I need is to find the most efficient way to do that. > > Therefore, I do not believe that I need any triggers. I

Re: [sqlite] How to interrupt a long running update without roll back?

From: jhnlmn <jhn...@yahoo.com> To: sqlite-users@sqlite.org Sent: Friday, June 21, 2013 3:20 PM Subject: Re: [sqlite] How to interrupt a long running update without roll back? Alas, none of the above answered my original question "How to interrupt a long running update withou

Re: [sqlite] How to interrupt a long running update without roll back?

Ryan Johnson writes: > So if I understand correctly, you start out with only C2, use C2 to > drive an expensive/slow computation that derives all other fields in the > row, and set C1 != NULL when the derivation completes? And you don't > want a trigger because that would pin

Re: [sqlite] How to interrupt a long running update without roll back?

On 21/06/2013 2:29 PM, jhnlmn wrote: Ryan Johnson writes: Q1: Is C1 *always* NULL in a newly-inserted row, or does the application sometimes insert some arbitrary value? Q2: Does the transition from NULL to calculation(C2) mean something special to the application? ...

Re: [sqlite] How to interrupt a long running update without roll back?

> Ryan Johnson writes: > Q1: Is C1 *always* NULL in a newly-inserted row, or does the application > sometimes insert some arbitrary value? > Q2: Does the transition from NULL to calculation(C2) mean something > special to the application? ... > Scenario 3: C1=NULL has a

Re: [sqlite] How to interrupt a long running update without roll back?

On 21/06/2013 2:55 AM, jhnlmn wrote: Ryan Johnson writes: histo = list(conn.execute('select min(rowid) lo, max(rowid) hi, count(*) n from lineitem group by rowid/1' order by lo)) ... a,b,n = buckets[-1] Thank you for your reply. You code is not very correct ("a"

Re: [sqlite] How to interrupt a long running update without roll back?

Simon Slavin writes: > Nevertheless, this is the way the problem should be solved according to the design of SQLite. What you are > doing is searching for NULL entries in a table. > The way you speed up a search is to create an index I do not mind adding indexes to columns, which

Re: [sqlite] How to interrupt a long running update without roll back?

> Ryan Johnson writes: > histo = list(conn.execute('select min(rowid) lo, max(rowid) hi, count(*) > n from lineitem group by rowid/1' order by lo)) ... > a,b,n = buckets[-1] Thank you for your reply. You code is not very correct ("a" always remain 0), but I got your

Re: [sqlite] How to interrupt a long running update without roll back?

On 19/06/2013 1:41 AM, jhnlmn wrote: Thank you for your response Simon Slavin writes: UPDATE T SET C1 = calculation(C2) WHERE C1 IS NULL AND rowid < 1 This is the best solution when the table is freshly created and max(rowid) == number of rows. But after many deletes and

Re: [sqlite] How to interrupt a long running update without roll back?

On 19 Jun 2013, at 6:41am, jhnlmn wrote: > Simon Slavin writes: > >> Do you have an index on T(C1) ? >> That should dramatically reduce the search time. > > I tried adding index. > It caused slow down of the original insert of records to the table by about >

Re: [sqlite] How to interrupt a long running update without roll back?

Thank you for your response Simon Slavin writes: > Another way would be to use the LIMIT clause on UPDATE Yes, this was the very first thing I tried. But without index on C1 the time of each query with limit is not limited. For example on 1 mln rows the first UPDATE T set C1 =

Re: [sqlite] How to interrupt a long running update without roll back?

On 14 Jun 2013, at 11:29pm, DL wrote: > UPDATE T set C1 = calculation(C2) where C1 is NULL > If table is large, this update may take many seconds or even minutes. > During this time all other queries on this table fail with database is > locked > after connection timeout

[sqlite] How to interrupt a long running update without roll back?

Hi, I have a long running multirow update such as: UPDATE T set C1 = calculation(C2) where C1 is NULL If table is large, this update may take many seconds or even minutes. During this time all other queries on this table fail with database is locked after connection timeout expires (currently my