On 2017/05/29 4:05 PM, x wrote:
Thanks Richard. I can’t claim to fully understand the part about the app
calling sqlit3_column_text but I’m reassured that leaving the arithmetic to
sqlite is the way to go.
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.
He is also saying that the effort is probably not worth all that many
CPU cycles, unless the computation is really heavy, and even then... So
before you embark on that road, make sure the gains are well worth the
effort.
From: Richard Hipp<mailto:d...@sqlite.org>
Sent: 29 May 2017 14:52
To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Does prepare do arithmetic?
On 5/29/17, x <tam118...@hotmail.com> wrote:
Thanks Clemens, that clears that up.
I’m still left wondering though why it calculates 2+2 every step in
‘select 2+2 from Tbl;’
All result values must be recomputed on every step because the
application can change the value by (for example) calling
sqlite3_column_text and thus causing the integer result to be
converted into a string result. The result needs to be reset back to
an integer for the next row.
It is true that SQLite could compute 2+2 and store the result then
copy the result into the result register for each result row, but
doing the copy is not measurably faster than simply redoing the 2+2
computation.
You will notice that in a more complex computation like:
SELECT 2*15-5+11/5<<3*1 FROM tbl
That the "2*15-5+11/5" and "3*1" subexpressions are computed just
once, and only the final "<<" operator is repeated for each row.
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
