Re: [sqlite] Common subexpression optimization of deterministic functions

lite] Common subexpression optimization of deterministic   functions On Sep 12, 2017, at 2:36 PM, Jens Alfke <j...@mooseyard.com> wrote: > > On Sep 12, 2017, at 12:58 PM, Warren Young <war...@etr-usa.com> wrote: >> >> Could it be *extended* to mean what you want?  O

Re: [sqlite] Common subexpression optimization of deterministic functions

On Sep 12, 2017, at 2:36 PM, Jens Alfke wrote: > > On Sep 12, 2017, at 12:58 PM, Warren Young wrote: >> >> Could it be *extended* to mean what you want? Of course, but that means >> you’re asking for a feature, not reporting a bug. > > I never claimed

Re: [sqlite] Common subexpression optimization of deterministic functions

Yeah that is a tricky bit, especially since the query optimizer might evaluate join expressions in an arbitrary order. A possible approach to this is to work out how to always get a particular expression evaluated first (that may well just be the left-most expression in the WHERE clause) then

Re: [sqlite] Common subexpression optimization of deterministic functions

ite-users@mailinglists.sqlite.org>, Jens Alfke <j...@mooseyard.com> Subject: Re: [sqlite] Common subexpression optimization of deterministic functions You can implement this by using user defined functions to implement row "local variables" or "registers". They'r

Re: [sqlite] Common subexpression optimization of deterministic functions

> On Sep 12, 2017, at 1:41 PM, Darko Volaric wrote: > > 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. Thanks!

Re: [sqlite] Common subexpression optimization of deterministic functions

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,

Re: [sqlite] Common subexpression optimization of deterministic functions

> On Sep 12, 2017, at 12:58 PM, Warren Young wrote: > > Could it be *extended* to mean what you want? Of course, but that means > you’re asking for a feature, not reporting a bug. I never claimed to be reporting a bug! The subject line refers to this as an

Re: [sqlite] Common subexpression optimization of deterministic functions

> On Sep 12, 2017, at 12:55 PM, Richard Hipp wrote: > > But we deliberately omit common subexpression elimination (CSE). This > is because our research shows that out of the millions of queries that > SQLite compiles every second, only a very tiny fraction would actually >

Re: [sqlite] Common subexpression optimization of deterministic functions

On Sep 12, 2017, at 12:41 PM, Jens Alfke wrote: > >> On Sep 12, 2017, at 11:09 AM, Warren Young wrote: >> >> From my reading of the docs, I don’t see that that is the purpose of >> SQLITE_DETERMINISTIC: >> >> https://www.sqlite.org/deterministic.html

Re: [sqlite] Common subexpression optimization of deterministic functions

On 9/12/17, Jens Alfke wrote: > > 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

Re: [sqlite] Common subexpression optimization of deterministic functions

> On Sep 12, 2017, at 12:02 PM, Darren Duncan 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

Re: [sqlite] Common subexpression optimization of deterministic functions

On 2017-09-12 11:41 AM, Jens Alfke wrote: On Sep 12, 2017, at 11:09 AM, Warren Young wrote: From my reading of the docs, I don’t see that that is the purpose of SQLITE_DETERMINISTIC: https://www.sqlite.org/deterministic.html

Re: [sqlite] Common subexpression optimization of deterministic functions

> On Sep 12, 2017, at 11:09 AM, Warren Young wrote: > > From my reading of the docs, I don’t see that that is the purpose of > SQLITE_DETERMINISTIC: > >https://www.sqlite.org/deterministic.html > Actually it is. "A

Re: [sqlite] Common subexpression optimization of deterministic functions

On Sep 12, 2017, at 11:25 AM, Jens Alfke wrote: > > 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 > registered as SQLITE_DETERMINISTIC: From my reading of the docs, I

Re: [sqlite] Common subexpression optimization of deterministic functions

> On Sep 12, 2017, at 10:52 AM, Richard Hipp wrote: > > ... WHERE fl_value(body,'contact.address.state') IN ('CA','WA'); This was just a simple example query. In general it's possible to have arbitrary queries that use arbitrary properties. For example it might be

Re: [sqlite] Common subexpression optimization of deterministic functions

On 9/12/17, Jens Alfke wrote: > 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 > registered as SQLITE_DETERMINISTIC: > > SELECT key FROM kv_default > WHERE fl_value(body,

[sqlite] Common subexpression optimization of deterministic functions

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 registered as SQLITE_DETERMINISTIC: SELECT key FROM kv_default WHERE fl_value(body, 'contact.address.state') = 'CA' OR fl_value(body,