Lowering totalUsed

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Lowering totalUsed

Cecil Westerhof-5
A few tables have a not completely apt named column totalUsed.

It is used to see which records are more used as other records and give the
less used records a bigger chance of being selected. When the numbers
become high I do something like:
UPDATE tips
SET totalUsed = totalUsed - (SELECT MIN(totalUsed) FROM tips) + 1

I am not quit happy with this. Would it be better to split it in two
queries and feed the result of the first to the second?

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Lowering totalUsed

Cecil Westerhof-5
2018-07-12 9:30 GMT+02:00 Cecil Westerhof <[hidden email]>:

> A few tables have a not completely apt named column totalUsed.
>
> It is used to see which records are more used as other records and give
> the less used records a bigger chance of being selected. When the numbers
> become high I do something like:
> UPDATE tips
> SET totalUsed = totalUsed - (SELECT MIN(totalUsed) FROM tips) + 1
>
> I am not quit happy with this. Would it be better to split it in two
> queries and feed the result of the first to the second?
>

​By the way, I wanted to use:
UPDATE quotes
SET totalUsed = totalUsed - MIN(totalUsed) + 1

but that gives:

Error: misuse of aggregate function MIN()

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Lowering totalUsed

Simon Slavin-3
In reply to this post by Cecil Westerhof-5
On 12 Jul 2018, at 8:30am, Cecil Westerhof <[hidden email]> wrote:

> I am not quit happy with this. Would it be better to split it in two
> queries and feed the result of the first to the second?

I would guess that it will run faster.  How much faster depends on how many rows there are in the table.  Naturally I would say this since I am a fan of multiple short SQL commands rather than one big complicated one.

You could enclose the two queries in a transaction if you're worried about something sneaking between them.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Lowering totalUsed

Keith Medcalf
In reply to this post by Cecil Westerhof-5

This query will work fine.  You could also do something like:

UPDATE tips
   SET totalUsed = totalUsed - (SELECT MIN(totalUsed) - 1 FROM tips);

which would include the extra 1 (the new base) in the scalar subquery.

The expression (SELECT MIN(totalUsed) FROM tips) is not correlated with the outer query (the update) and is a scalar value that is computed only once (when the first row of the outer update is processed), so the query effectively becomes equivalent to the following:

begin;
v = select min(totalUsed) from tips;
update tips set totalUsed = totalused - v + 1;
commit;

where v would be passed in and out by your application, or, if you include the extra +1 operation in the scalar subquery then

begin;
v = select min(totalUsed) - 1 from tips;
update tips set totalUsed = totalUsed - v;
commit;

The only difference being whether there is an additional "add" being performed for each row in the original ...

If you do an "explain" on the query you see that there is a ONCE instruction (at addr 9) which means to skip the calculation of the scalar after it has been done once (by jumping to location 26 if it has already been calculated once).  (The only difference being whether the offset of 1 is done inside the once set, or for each row).

sqlite> explain update x set x = x - (select min(x) from x) + 1;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     37    0                    00  Start at 37
1     Null           0     1     2                    00  r[1..2]=NULL
2     OpenWrite      0     2     0     1              00  root=2 iDb=0; x
3     Noop           0     0     0                    00  Begin WHERE-loop0: x
4     Rewind         0     35    0                    00
5       Noop           0     0     0                    00  Begin WHERE-core
6       Rowid          0     2     0                    00  r[2]=rowid
7       IsNull         2     36    0                    00  if r[2]==NULL goto 36
8       Column         0     0     5                    00  r[5]=x.x
9       Once           0     26    0                    00
10      Null           0     7     7                    00  r[7..7]=NULL; Init subquery result
11      Integer        1     8     0                    00  r[8]=1; LIMIT counter
12      Null           0     9     10                   00  r[9..10]=NULL
13      OpenRead       1     2     0     1              00  root=2 iDb=0; x
14      Noop           0     0     0                    00  Begin WHERE-loop0: x
15      Rewind         1     22    0                    00
16        Noop           0     0     0                    00  Begin WHERE-core
17        Column         1     0     11                   00  r[11]=x.x
18        CollSeq        0     0     0     (BINARY)       00
19        AggStep        0     11    9     min(1)         01  accum=r[9] step(r[11])
20        Noop           0     0     0                    00  End WHERE-core
21      Next           1     16    0                    01
22      Noop           0     0     0                    00  End WHERE-loop0: x
23      AggFinal       9     1     0     min(1)         00  accum=r[9] N=1
24      Copy           9     7     0                    00  r[7]=r[9]
25      DecrJumpZero   8     26    0                    00  if (--r[8])==0 goto 26
26      Subtract       7     5     4                    00  r[4]=r[5]-r[7]
27      Add            12    4     3                    00  r[3]=r[12]+r[4]
28      Noop           0     0     0                    00  BEGIN: GenCnstCks(0,1,2,2,0)
29      Noop           0     0     0                    00  END: GenCnstCks(0)
30      Delete         0     68    2     x              02
31      MakeRecord     3     1     4     D              00  r[4]=mkrec(r[3])
32      Insert         0     4     2     x              07  intkey=r[2] data=r[4]
33      Noop           0     0     0                    00  End WHERE-core
34    Next           0     5     0                    01
35    Noop           0     0     0                    00  End WHERE-loop0: x
36    Halt           0     0     0                    00
37    Transaction    0     1     1     0              01  usesStmtJournal=0
38    Integer        1     12    0                    00  r[12]=1
39    Goto           0     1     0                    00


sqlite> explain update x set x = x - (select min(x)-1 from x);
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     36    0                    00  Start at 36
1     Null           0     1     2                    00  r[1..2]=NULL
2     OpenWrite      0     2     0     1              00  root=2 iDb=0; x
3     Noop           0     0     0                    00  Begin WHERE-loop0: x
4     Rewind         0     34    0                    00
5       Noop           0     0     0                    00  Begin WHERE-core
6       Rowid          0     2     0                    00  r[2]=rowid
7       IsNull         2     35    0                    00  if r[2]==NULL goto 35
8       Column         0     0     4                    00  r[4]=x.x
9       Once           0     26    0                    00
10      Null           0     6     6                    00  r[6..6]=NULL; Init subquery result
11      Integer        1     7     0                    00  r[7]=1; LIMIT counter
12      Null           0     8     9                    00  r[8..9]=NULL
13      OpenRead       1     2     0     1              00  root=2 iDb=0; x
14      Noop           0     0     0                    00  Begin WHERE-loop0: x
15      Rewind         1     22    0                    00
16        Noop           0     0     0                    00  Begin WHERE-core
17        Column         1     0     10                   00  r[10]=x.x
18        CollSeq        0     0     0     (BINARY)       00
19        AggStep        0     10    8     min(1)         01  accum=r[8] step(r[10])
20        Noop           0     0     0                    00  End WHERE-core
21      Next           1     16    0                    01
22      Noop           0     0     0                    00  End WHERE-loop0: x
23      AggFinal       8     1     0     min(1)         00  accum=r[8] N=1
24      Subtract       11    8     6                    00  r[6]=r[8]-r[11]
25      DecrJumpZero   7     26    0                    00  if (--r[7])==0 goto 26
26      Subtract       6     4     3                    00  r[3]=r[4]-r[6]
27      Noop           0     0     0                    00  BEGIN: GenCnstCks(0,1,2,2,0)
28      Noop           0     0     0                    00  END: GenCnstCks(0)
29      Delete         0     68    2     x              02
30      MakeRecord     3     1     4     D              00  r[4]=mkrec(r[3])
31      Insert         0     4     2     x              07  intkey=r[2] data=r[4]
32      Noop           0     0     0                    00  End WHERE-core
33    Next           0     5     0                    01
34    Noop           0     0     0                    00  End WHERE-loop0: x
35    Halt           0     0     0                    00
36    Transaction    0     1     1     0              01  usesStmtJournal=0
37    Integer        1     11    0                    00  r[11]=1
38    Goto           0     1     0                    00


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Cecil Westerhof
>Sent: Thursday, 12 July, 2018 01:30
>To: SQLite mailing list
>Subject: [sqlite] Lowering totalUsed
>
>A few tables have a not completely apt named column totalUsed.
>
>It is used to see which records are more used as other records and
>give the
>less used records a bigger chance of being selected. When the numbers
>become high I do something like:
>UPDATE tips
>SET totalUsed = totalUsed - (SELECT MIN(totalUsed) FROM tips) + 1
>
>I am not quit happy with this. Would it be better to split it in two
>queries and feed the result of the first to the second?
>
>--
>Cecil Westerhof
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users