I’m fairly sure the gains won’t be worth the effort RS as any arithmetic will mostly be in the WHERE clause. Thanks.
From: R Smith<mailto:rsm...@rsweb.co.za> Sent: 29 May 2017 15:18 To: sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Does prepare do arithmetic? 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
