> On Sep 12, 2017, at 12:02 PM, Darren Duncan <dar...@darrenduncan.net> wrote: > > Practically speaking any optimization to reduce actual calls to the > deterministic function would have to be at compile time to rewrite the query > to explicitly keep the result of the function and use it several times,
Exactly. > which is someone users can also do by writing the query differently. Great — any advice on how to do it? I'm totally willing to do this :) but I'm not sure how. As I said, a WITH clause looks promising, but I don't know if that is purely syntactic sugar, like a macro. (And changing my query generator to factor common calls into WITH clauses would be a nontrivial amount of work, so I would like to get some assurance that it might help, before I try it.) The CSE optimization has long been standard in traditional compilers, even though the programmer could get the same result by changing their code. (The same is true of many other optimizations.) The benefit is that it lets the developer write simpler, clearer code with less effort. I realize SQLite doesn't have the kind of industrial-strength query optimizers that other SQL databases have, but (from an outside perspective) this seems like a fairly straightforward optimization. SQLite is already doing some similar tricks to recognize matching sub-expressions when it applies an expression-based index to a query, for example. —Jens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
