I think people are missing the point, probably becuase it's not a great example. Consider the following statement:
SELECT funca(slow(10)), funkb(slow(10)) and lets say slow(10) takes an hour to compute, and funka and funkb take almost no time to execute. With common subexpression optimization the statement would take one hour, instead of two, to compute becuase the value of slow(10) would only be calculated once. > On Sep 14, 2017, at 8:31 AM, Wout Mertens <wout.mert...@gmail.com> wrote: > > Isn't that what cross join is for? Do a select on a virtual table to > calculate the value and then use that value in the real where clause? > > On Wed, Sep 13, 2017, 9:10 AM Hick Gunter <h...@scigames.at> wrote: > >> Try fl_value(...) IN (<list>) >> >> -----Ursprüngliche Nachricht----- >> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] >> Im Auftrag von Jens Alfke >> Gesendet: Dienstag, 12. September 2017 19:26 >> An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >> Betreff: [EXTERNAL] [sqlite] Common subexpression optimization of >> deterministic functions >> >> SQLite 3.19 doesn’t seem to coalesce identical calls to a deterministic >> function. For example, in this query, where `fl_value` is a function I’ve >> registered as SQLITE_DETERMINISTIC: >> >> SELECT key FROM kv_default >> WHERE fl_value(body, 'contact.address.state') = 'CA' >> OR fl_value(body, 'contact.address.state') = 'WA' >> >> fl_value gets called twice per row in the table, with the same inputs both >> times of course. As fl_value is not a cheap function — it’s similar to >> json_value — it would be a noticeable speedup if it were evaluated only >> once per row. >> >> Is there a way I can restructure these (automatically generated) queries >> to do the refactoring explicitly? Sort of like assigning to a temporary >> variable in an imperative language? It looks like a WITH clause lets me do >> this syntactically, but I'm not sure if it'll make a difference at runtime. >> >> —Jens >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> >> >> ___________________________________________ >> Gunter Hick >> Software Engineer >> Scientific Games International GmbH >> FN 157284 a, HG Wien >> Klitschgasse 2-4, A-1130 Vienna, Austria >> Tel: +43 1 80100 0 >> E-Mail: h...@scigames.at >> >> This communication (including any attachments) is intended for the use of >> the intended recipient(s) only and may contain information that is >> confidential, privileged or legally protected. Any unauthorized use or >> dissemination of this communication is strictly prohibited. If you have >> received this communication in error, please immediately notify the sender >> by return e-mail message and delete all copies of the original >> communication. Thank you for your cooperation. >> >> >> _______________________________________________ >> 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
