Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On Thursday, 14 September, 2017 19:05, Jens Alfke , wrote: >> On Sep 14, 2017, at 1:23 PM, Keith Medcalf >wrote: >> You merely need to ONCE it either for each input row or for each >result row. So for example: >> select slow(a.x), slow(a.x)*slow(b.y),

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

Nico Williams wrote: > I would much prefer to be able to specify which CTEs must be materialized, > and which may be left as internal views. That would give the user a great > deal of control. WITH x AS () MATERIALIZED ... . "Materialized" is the wrong word; you want to prevent only subquery

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On Fri Sep 15, 2017 at 09:55:40AM +0200, Dominique Devienne wrote: > On Thu, Sep 14, 2017 at 11:43 PM, Nico Williams > wrote: > > > [...] I would much prefer to be able to specify which CTEs must be > > materialized, > > and which may be left as internal views. That would

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On Thu, Sep 14, 2017 at 11:43 PM, Nico Williams wrote: > On Thu, Sep 14, 2017 at 1:10 PM Simon Slavin wrote: > > Can you not do it with WITH ? I don’t really understand how WITH works > > but it would seem to evaluate its terms just once for each

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

> On Sep 14, 2017, at 1:23 PM, Keith Medcalf wrote: > > You merely need to ONCE it either for each input row or for each result row. > So for example: > > select slow(a.x), slow(a.x)*slow(b.y), slow(b.y) from a, b where a.this == > b.that > > when computing the result

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On 14 Sep 2017, at 10:43pm, Nico Williams wrote: > On Thu, Sep 14, 2017 at 1:10 PM Simon Slavin > wrote: > >> Can you not do it with WITH ? I don’t really understand how WITH works >> but it would seem to evaluate its

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On Thu, Sep 14, 2017 at 1:10 PM Simon Slavin wrote: > > > On 14 Sep 2017, at 5:55pm, R Smith wrote: > > > Richard, wouldn't it be possible to supply a wrapping function (perhaps > a hint function, like the likelihood() function), that takes another >

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin >Sent: Thursday, 14 September, 2017 08:57 >To: SQLite mailing list >Subject: Re: [sqlite] [EXTERNAL] Common subexpression optimization of >deterministic functi

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On 14 Sep 2017, at 8:02pm, Clemens Ladisch wrote: > Jens Alfke wrote: >> can someone please tell me how to hoist/factor out the subexpression >> manually then? > > Move the subexpression into a subquery, then prevent subquery flattening >

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

Jens Alfke wrote: > can someone please tell me how to hoist/factor out the subexpression manually > then? Move the subexpression into a subquery, then prevent subquery flattening (http://www.sqlite.org/optoverview.html#flattening) by violating one of the listed constraints. (These rules might

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

> On Sep 14, 2017, at 10:31 AM, R Smith wrote: > > I think that's actually one of the things one of the posters tried and found > not to work. The problem is the WITH syntax in theory is just-like > constructing a temporary table and using the values from there, but in >

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On 2017/09/14 7:10 PM, Simon Slavin wrote: On 14 Sep 2017, at 5:55pm, R Smith wrote: Richard, wouldn't it be possible to supply a wrapping function (perhaps a hint function, like the likelihood() function), that takes another function as a parameter and then ensuring

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

If you're looking for a simple/easy/clean way of doing it, there isn't one. You have to modify the library to do it properly. But I still find it an interesting design challenge. Maybe instead of going the eager route you can go lazy and just cache subexpressions which might be called again.

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On 14 Sep 2017, at 5:55pm, R Smith wrote: > Richard, wouldn't it be possible to supply a wrapping function (perhaps a > hint function, like the likelihood() function), that takes another function > as a parameter and then ensuring that THAT gets calculated only once? Can

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

Richard, wouldn't it be possible to supply a wrapping function (perhaps a hint function, like the likelihood() function), that takes another function as a parameter and then ensuring that THAT gets calculated only once? SELECT calc_once(slow(10))   FROM xxx Note that if the same function

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

> On Sep 14, 2017, at 8:38 AM, Warren Young wrote: > > All the examples I’ve seen attempting to support the value of this feature > are simple enough that even a naive text compression algorithm could find the > similarities and “hoist” the copies so the value is computed

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On Thu, Sep 14, 2017 at 5:38 PM, Warren Young wrote: > On Sep 14, 2017, at 8:49 AM, Dominique Devienne > wrote: > > > > On Thu, Sep 14, 2017 at 4:13 PM, Richard Hipp wrote: > > > >> the amount of extra time spent inside of

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On 9/14/17, Darko Volaric wrote: > is > there a way of ensuring that a particular expression (or just function call) > will be guaranteed to be evaluated before any other in a particular > statement? No, not in general. There are special cases were the order of evaluation is

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On Sep 14, 2017, at 9:36 AM, Darko Volaric wrote: > > I don't support automatic CSE, I think it should be done manually. So…you want a SQL profiler, then? Like EXPLAIN? I suppose there might be a market for a tool that takes a query string and spends a ridiculous amount of

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On Sep 14, 2017, at 8:49 AM, Dominique Devienne wrote: > > On Thu, Sep 14, 2017 at 4:13 PM, Richard Hipp wrote: > >> the amount of extra time spent inside of sqlite3_prepare() in order to >> deal with them is not worth the effort. > > But why not let the

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

OK, in trying to clear up one misunderstanding I've created another, so let me be clear: I don't support automatic CSE, I think it should be done manually. But to support that Richard, can you answer this question: is there a way of ensuring that a particular expression (or just function call)

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On 14 Sep 2017, at 3:08pm, Darko Volaric wrote: > I think people are missing the point, probably becuase it's not a great > example. Consider the following statement: > > SELECT funca(slow(10)), funkb(slow(10)) > > and lets say slow(10) takes an hour to compute, and funka

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On Thu, Sep 14, 2017 at 4:13 PM, Richard Hipp wrote: > On 9/14/17, Darko Volaric wrote: > > I think people are missing the point, probably becuase it's not a great > > example. Consider the following statement: > > > > SELECT funca(slow(10)), funkb(slow(10)) >

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On 9/14/17, Darko Volaric wrote: > I think people are missing the point, probably becuase it's not a great > example. Consider the following statement: > > SELECT funca(slow(10)), funkb(slow(10)) > > and lets say slow(10) takes an hour to compute, and funka and funkb take >

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

I think people are missing the point, probably becuase it's not a great example. Consider the following statement: SELECT funca(slow(10)), funkb(slow(10)) and lets say slow(10) takes an hour to compute, and funka and funkb take almost no time to execute. With common subexpression optimization

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

Isn't that what cross join is for? Do a select on a virtual table to calculate the value and then use that value in the real where clause? On Wed, Sep 13, 2017, 9:10 AM Hick Gunter wrote: > Try fl_value(...) IN () > > -Ursprüngliche Nachricht- > Von: sqlite-users

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

Try fl_value(...) IN () -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Jens Alfke Gesendet: Dienstag, 12. September 2017 19:26 An: SQLite mailing list Betreff: [EXTERNAL] [sqlite]