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

table. >It assumes and requires that "some_table" is a rowid table. > >-- >The fact that there's a Highway to Hell but only a Stairway to Heaven >says a lot about anticipated traffic volume. > >>-Original Message- >>From: sqlite-users On >

Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization

e's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users On >Behalf Of Mario M. Westphal >Sent: Friday, 8 November, 2019 12:08 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] SQLI

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

[sqlite] SQLITE_DETERMINISTIC and custom function optimization

Hi all, I have a table with matrices stored as blobs. Each matrix has about 800 bytes. This table has between 20,000 and 500,000 rows. I use a custom function "EXPENSIVE_FUNCTION" which performs a calculation using a matrix supplied via sqlite3_bind_pointer() as ?99 and the matrix in the