On 24 May 2015, at 4:58pm, Keith Medcalf <kmedcalf at dessus.com> wrote:
> This is not possible and would make an update operation incredibly slow.
> What you mean is that a row which is not updated is not updated. A row which
> is updated, even if you are re-writing the same value, *IS* an update
> operation.
CREATE TABLE myTable (x INTEGER, orig INTEGER);
(imagine 10000 operations like this here):
INSERT INTO "myTable" VALUES(9636,9636);
INSERT INTO "myTable" VALUES(8655,8655);
INSERT INTO "myTable" VALUES(7966,7966);
INSERT INTO "myTable" VALUES(1647,1647);
INSERT INTO "myTable" VALUES(7780,7780);
INSERT INTO "myTable" VALUES(9088,9088);
In the following, ignore the indented rows. Those are me putting the original
values back where they belong after each test.
sqlite> UPDATE myTable SET x=1111 WHERE x<100;
Run Time: real 0.007 user 0.002405 sys 0.001365
sqlite> UPDATE myTable SET x=orig;
Run Time: real 0.014 user 0.010427 sys 0.001255
sqlite> UPDATE myTable SET x=orig WHERE x<100;
Run Time: real 0.008 user 0.002374 sys 0.001319
sqlite> UPDATE myTable SET x=orig;
Run Time: real 0.016 user 0.010507 sys 0.001409
sqlite> UPDATE myTable SET x=1111 WHERE x<9000;
Run Time: real 0.015 user 0.010315 sys 0.001305
sqlite> UPDATE myTable SET x=orig;
Run Time: real 0.016 user 0.010493 sys 0.001333
sqlite> UPDATE myTable SET x=orig WHERE x<9000;
Run Time: real 0.016 user 0.010673 sys 0.001282
sqlite> UPDATE myTable SET x=orig;
Run Time: real 0.015 user 0.010674 sys 0.001383
I was wrong. You're right. The 'user' number is far larger when the WHERE
clause is selecting more rows. It is not larger when you're writing back the
same number as the number already in that column. So SQLite does not compare
the number you're writing with the number already in that column and rewrite
the row only if they're different.
Therefore, to answer the OP, using the WHILE clause is more likely to mean
faster execution with less writing going on. Which is good all round.
Simon.
