> 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,
> 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.
sqlite-users mailing list