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 is used twice or more in a single query,
they don't need to know about each other, but perhaps a further win can
be achieved with adding an ID to the call, so that:
SELECT x * calc_once(slow(10), 1), y * calc_once(slow(10), 1), z *
calc_once(slow(20), 2)
FROM xxx....
will only see two slow calculations performed, and it means no expensive
cache-management code by SQLite itself, it's completely up to the query
programmer managing those IDs.
I thought of doing this a while ago via a UDF, but then realized there
is no way for me to prevent SQLite from re-calculating a parameter to my
own UDF.
So essentially I tried to do the above and it would be a rather easy
exercise for me in code, but I can't prevent SQLite from recalculating
the "slow(10)" in calc_once(slow(10), 1) before evaluating the
encapsulating function whose job it is to report back the cached
version. But perhaps there is a way for you to do it.
I also thought of a way to send my function a string, so that it would be:
SELECT x * calc_once( ' slow(10) ' ), y * calc_once( ' slow(10) ' )
where my function would then initiate it's own little SQL to do "SELECT
slow(10);" and use the return value from that as a cache whenever it
gets asked for the same string, but that prevents the evaluation from
containing something from the same query and I wasn't sure how well it
would play with ATOMICness of transactions and the like.
I feel there might be a middle-ground in there somewhere where
"something" can be done that doesn't require all the heft.
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users