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
pointer to this struct to the function.

(I have full control over when EXPENSIVE_FUNCTION is created, dropped and
used. This trick may not work in other use cases.)


Then I've changed the EXPENSIVE_FUNCTION signature to also take the rowed as
the first parameter: 




EXPENSIVE_FUNCTION uses sqlite3_user_data() to get the state struct pointer
and then compares the rowid parameter with the rowid in the struct.

If they are identical, the cached result is used. Very fast.

Else the result for the requested row is calculated and cached. This is the
slow part.


Thanks to this change, EXPENSIVE_FUNCTION needs to perform the slow
calculations in only 46,031 of 91,806 calls. 

In all other cases the cached value from the previous call can be used.

The runtime drops to 2,580ms (from 3,350ms) for the 45K rows sample set.
Which yields a roughly 20% better runtime. Very good.


I'm always amazed about what can be achieved with SQLite. Very impressive
product and API design.


Thank again for all who provided suggestions and commented.


-- Mario

sqlite-users mailing list

Reply via email to