No, you do not have to keep track of all the possibly seen results to only compute them once in order to achieve significant benefits.
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 set you merely compute ONCE slow(a.x) and ONCE slow(b,y) and any subsequent call to the same phrase simply uses the result that was stored in the result register. You could, of course, build a "table" of results of the function, but I do not think there is any implementation that does that for you. If you want to do that, then you would do it yourself and join that table back to the original query. --- 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 [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 functions > > > >On 14 Sep 2017, at 3:08pm, Darko Volaric <li...@darko.org> 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 almost no time to execute. With common subexpression >optimization the statement would take one hour, instead of two, to >compute becuase the value of slow(10) would only be calculated once. > >Thing of how hard that is to implement, though. You have to keep a >cache of each deterministic function and the parameters it was given >and the result. You have to search through the cache every time you >evaluate a deterministic function. You have to allocate and free the >memory this cache takes up. You have to figure out a strategy for >how long you keep results in the cache. And the best strategy >depends on something the SQLite developers can’t tell about your >programming, so whatever they choose some people will complain. > >Simon. >_______________________________________________ >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
