Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization

Thursday, 14 November, 2019 11:16 >To: SQLite mailing list >Subject: Re: [sqlite] SQLITE_DETERMINISTIC and custom function >optimization > >>On Thursday, 14 November, 2019 03:52, Dominique Devienne > wrote: > >>>On Sat, Nov 9, 2019 at 1:20 PM Mario M. Westphal wr

Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization

>On Thursday, 14 November, 2019 03:52, Dominique Devienne >wrote: >>On Sat, Nov 9, 2019 at 1:20 PM Mario M. Westphal wrote: >>> Thanks to all the friendly people who commented on my question. Much >>> appreciated :-) >>> I was able to solve this with a small trick: >>> I created a small

Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization

-- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. On Thursday, 14 November, 2019 03:52, Dominique Devienne wrote: >On Sat, Nov 9, 2019 at 1:20 PM Mario M. Westphal wrote: >> Thanks to all the friendly people who

Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization

On Sat, Nov 9, 2019 at 1:20 PM Mario M. Westphal wrote: > Thanks to all the friendly people who commented on my question. Much > appreciated :-) > > I was able to solve this with a small trick: > I created a small 'state' struct with a rowid and the result (float) for > that row. > Sounds like

Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization

On Fri, Nov 8, 2019 at 9:20 PM Keith Medcalf wrote: > [...] The optimizer is prone to calculating things more often than it > needs to, and is difficult to force to "materialize" things. Since your expensive function needs to be calculated for every row of the > table anyway, it would be better

Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization

Thanks to all the friendly people who commented on my question. Much appreciated :-) I was able to solve this with a small trick: I created a small 'state' struct with a rowid and the result (float) for that row. Using the "user data" parameter when creating EXPENSIVE_FUNCTION, I supply a

Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization

>But this makes me think of the upcoming virtual column feature. If you >define a virtual table column whose value is equal to >EXPENSIVE_FUNCTION(), do multiple references to that column in a query >cause multiple calls to the function, or is it computed only once per >row? In the present case

Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization

To: 'SQLite mailing list' >Subject: RE: [sqlite] SQLITE_DETERMINISTIC and custom function >optimization > > >SQLITE_DETERMINISTIC does not mean that the function is only called once >for each unique set of arguments, only that when called with a unique set >of arguments that it

Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization

SQLITE_DETERMINISTIC does not mean that the function is only called once for each unique set of arguments, only that when called with a unique set of arguments that it returns the same result. This means that if it is a constant it can be factored out of being called more than once. In your

Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization

> On Nov 8, 2019, at 11:08 AM, Mario M. Westphal wrote: > > The EXPENSIVE_FUNCTION function is referred multiple times in the update > statement. But it always returns the same result (for any given row). There was a similar thread (that I started, I think) from two years ago with subject

Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization

On 8 Nov 2019, at 7:08pm, Mario M. Westphal wrote: > The query looks like this: Try using EXPLAIN QUERY PLAN on your query. You might be able to see the two places where your function is called. ___ sqlite-users