Forum OpenACS Q&A: Re: Performing queries on a cached query result?

Posted by David Walker on
You could probably do it with ns_sets if you only need to pull data based on a single column. (I haven't tried it)

Your best bet is probably to build a temp table and query that.

I had an idea that it might be pretty cool to embed a lightweight sql engine (PicoSQL is one I ran across) into an AOLServer module that connects to tables in memory for use with reasonably complex information caching such as query results.
Posted by Andrew Piskorski on
You definitely do not want to use ns_set for large amounts of data. If you need to lookup any values in that ns_set by key name, the performance will suck very, very badly. Remember, time to find a single key by name in an ns_set scales linearly with ns_set size... Use a Tcl array or nsv instead.

Extending AOLserver and/or the Tcl Thread Extension's nsv support to include in memory relational tables, with full ACId guarantees (no D for Durability of course as it's all in memory only), would be very nice. Such a feature would have been very convenient in some of the projects I've done. I don't really care whether it has a SQL query interface or not, just as long as it has the requisite power.

But, I've never yet had a probject where I really needed such functionality, I could always get by just with plain old AOLserver nsvs, which is what I did. It merely would have been very, very nice to be able to conveniently and efficiently model that in-memory information relationally.

I hadn't heard of picoSQL before, that's interesting. It claims to be multi-user, but is it thread safe? Can it be conveniently used in-memory only rather than writing to disk?

Last I looked, SQLite only supports a single write user and so can't be used effectively from multiple threads. Metakit says it does not "offer true concurrent access", but I think Jean-Claude Wippler plans to make it do so eventually. It's also unclear how easily either of those could be used as in-memory databases. The SLIB Portable Scheme Library has it's own Relational Database implementation that might be useful to look at for anyone building this sort of thing.