> 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

Reply via email to