On Mon, 29 May 2017 16:18:17 +0200 R Smith <rsm...@rsweb.co.za> wrote:
> Actually what Richard is saying is that you SHOULD do the arithmetic > yourself when you can, because you can boldly make assumptions about > the code that you write, but he, or the SQLite engine to be precise, > cannot be so bold, it may not be accurate in accepting that nothing > about the database will change from one evaluation to the next, so it > has to recompute - but you may optimise that computation out because > you KNOW your system and you KNOW you won't be changing column > collations or type affinities etc. in between steps. I wouldn't characterize his answer that way. He said that complex partial constants are computed once, and that recomputing simple constants wasn't measurably cheaper than storing them. From what they've measured, you're safe letting SQLite compute your constants. Even if it's doing a little extra work, it won't be noticeable. What was a little more suprising was why the little extra work is necessary. > calling sqlite3_column_text and thus causing the integer result to be > converted into a string I didn't know sqlite3_column_text converted the result. I thought the result was constant, and the function returned a string representation of it. Richard is saying that the column-retrieval functions change the type of the retrieved column for the current row. Because of that, and because that change-of-type action is under programmer control, SQLite must recompute each row anew. Even for "constant" columns. > 2*15-5+11/5<<3*1 In this case, two partials are stored, for efficiency, and the final operation, shift, is recomputed each time. Because the programmer can't affect the types of the partials, they can safely be memoized. Because the result of the final computation is a column -- whose type *can* be changed by the programmer -- it's recomputed for each row. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
