Thank you both for the discussion and conclusion.

2015-05-24 18:32 GMT+02:00 Simon Slavin <slavins at bigfraud.org>:

>
> 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.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

Reply via email to