Unfortunately that is not reliable either because the aux_data is specific to the context and each invocation within the statement can have a different context.
What does work is if you use the undocumented cross-context aux_data by using a negative argument number (note, since this is undocumented it is liable to change in future versions of SQLite3). Passing the cross-context aux_data location as an argument seems to work, eg., expensive_function(-1, rowid, ?99, vdata) where -1 is the aux_data location to use, rowid is used in the aux_data struct so you know if you can use the cached result, and ?99 and vdata are the parameters if calculation is required. -- 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 <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of Keith Medcalf >Sent: Thursday, 14 November, 2019 11:16 >To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >Subject: Re: [sqlite] SQLITE_DETERMINISTIC and custom function >optimization > >>On Thursday, 14 November, 2019 03:52, Dominique Devienne ><ddevie...@gmail.com> wrote: > >>>On Sat, Nov 9, 2019 at 1:20 PM Mario M. Westphal <m...@mwlabs.de> 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 you re-invented >>> https://www.sqlite.org/c3ref/get_auxdata.html >>> but with global state, no? >>> I replied to your original thread with that link, before seeing this >>> message. Using the built-in SQLite >>> mechanism for function caching is much better, because it's clean, and >>> properly handles the lifetime >>> of the cache, tying it to the statement execution lifetime. > >>get/set auxdata is apparently intended to cache auxillary data >associated >>with a CONSTANT provided to a function parameter, not the dynamic result >>of a computation. I suppose you could attempt to store your cache >>results against argument 2 (the table column), however that will >probably >>not achieve the effect desired since this is not the purpose of get/set >>auxdata (read the web page describing it). And storing it against >>argument 1 will not work because, although that is a constant, how would >>you ever know when the *value* of argument 2 changed? > >>Maybe I will write a wee test to see if it does work as you think or if >>it only works as documented. > >The function only works as documented in that auxdata set against non- >constant parameters is not maintained. This could be made to work with >"some random function" where one of the parameters is a constant or a >bound parameter by passing the cache determinant as a parameter and >caching the determinant and the result against the constant agrument. > >eg: > >expensive_function(?99, vdata, rowid) > >could have a structure containing a arg[2] and the result, and set this >as auxdata against argument 0 anytime it is computed. Before computing >one would check that the retrieved auxdata rowid == arg[2] and if so >simply return the result from the auxdata. This assumes that the value >of vdata is dependant on the value of rowid, of course. > >You can of course move the parameters around, but only auxdata stored >against a constant has a chance of being preserved. In this case the >constant is a bound value. Where there is no constant, you would have to >"create" one ... eg expensive_function(1, rowid, data1) so that you have >somewhere to store the auxdata ... > >-- >The fact that there's a Highway to Hell but only a Stairway to Heaven >says a lot about anticipated traffic volume. > > > >_______________________________________________ >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
