You can implement this by using user defined functions to implement row "local variables" or "registers". They're single assignment storage that keeps intermediate results, namely the common subexpressions.
You'd define two functions, something like Get(rowid, name) and Set(rowid, name, value). You call Set with the subexpressions as the last parameter. It doesn't return any value and just stores the value. The Get function returns a previously set value with the given name and is used in the expressions where that subexpression would otherwise appear. Note that you would need to order these so they are evaluated in dependency order, i.e. ensure each name is set before it is got. The rowid parameter is used to detect when the row changes and the local variables are all cleared in readiness for the next row. > On Sep 12, 2017, at 9:22 PM, Jens Alfke <j...@mooseyard.com> wrote: > > > >> 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
