I'd use the memoize stuff - in OpenACS 4 it uses ns_cache so I don't think there'd be much advantage in using it directly.
As to what to cache and what not to cache, there's obviously a tradeoff in the use of memory (caching is PER THREAD I believe, not PER AOLSERVER INSTANCE) for caching vs. more RAM for the database and filesystem cache. Complex queries are certainly worth caching, if they're hit frequently, but are simple queries?
One weakness of the current design is that there's no way to parameterize what should and shouldn't be cached. Then again, with hardware so cheap it's no big deal to stick a separate webserver box in front of the DB with a GB of RAM and have it cache like hell ...
