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

Collapse
2: Re: Performing queries on a cached query result? (response to 1)
Posted by David Walker on 03/13/03 10:13 PM
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.
Collapse
3: Re: Performing queries on a cached query result? (response to 2)
Posted by Andrew Piskorski on 03/14/03 06:09 PM
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.