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

I am working on a OpenACS PG site where I need to randomize the order of a huge query result (essentially pull an entire table into memory), cache it in memory so it remains constant throughout the day, and query the data for specific subsets.

At the moment we are not pulling the entire table or randomizing the order of the query data; instead, we are caching the results of small queries with util_memoize.

Is there a way to do this with ns_sets?

Thanks.

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.

There is a shared-memory version, and it is said to be thread-safe.

Speed: "With a 206-Mhz StrongARM processor, eXtremeDB performed 100,000 hash index lookup operations in 0.79 seconds."

FAQ: http://www.mcobject.com/faq.htm

Btw, SQLite can also be used either on-disk or in-memory now, and there's been discussion on the SQLite list lately about concurrency improvements. It sounds like they have a workable design for an MVCC model with table level locking for writers, but it's not yet clear whether it will ever be implemented or not.

See also the Ratcl thread, about Jean-Claude's new prototype package for relational algebra in Tcl (eventually other languages as well). It might ultimately be quite useful as a simple in-memory RDBMS, probably with read/write mutex locking per table/view.

With any sort of in-memory database, I figure table level locking is probably good enough, even if it's strictly pessimistic mutex locking, rather than the preferable MVCC model where locks block only other writers, never readers.

Note that table locking is probably always less scalable than simple Nsv key/value pairs, which you can always split up btween more and more mutex buckets. And of course nsv/tsv is simple and just the right thing in many cases - but when it's not, having a real in-memory RDBMS database would be very, very handy.

A long time ago, someone also pointed out Konstantin Knizhnik's FastDB and GigaBASE, which do have a sort of MVCC with one database-wide lock for writers, but IMNSHO are hardly "relational" at all, so I have trouble even imagining when or why I would ever want to use them. (No joins, unions, or foreign key references at all; instead each row is a C++ object and that object is allowed to have references - including dangling refereces - to other objects in other tables, so you are free do query that table, then try to do more queries chasing all the references. Yuck!)

I mentioned the Scheme SLIB relational database above, way back when. If I remember correctly, it is strictly one reader/write only, so is unlikely to be useful, even as an example, for the type of concurrent web-oriented applications we tend to talk about here.