Try fl_value(...) IN (<list>)

-----Ursprüngliche Nachricht-----
Von: sqlite-users [] Im 
Auftrag von Jens Alfke
Gesendet: Dienstag, 12. September 2017 19:26
An: SQLite mailing list <>
Betreff: [EXTERNAL] [sqlite] Common subexpression optimization of deterministic 

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 

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.

sqlite-users mailing list

 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

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

Reply via email to