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
