Great! Looking forward to finding out about your results. It is unfortunate that the biggest impact it would have would be on the admin account (if indeed my findings are repeatable) and that I had to dig into moodle core to do it. As well, eventually you get to the age old question of dev efficiency vs. code efficiency, and use of a cache definitely adds complexity to the dev side. Not sure where this would fall on the spectrum as I'm fairly new to the moodle world... but I can definitely understand the arguments on both sides.
I'm attaching an update to the solution I originally posted, without the first step (trying to get everything in one query and then hash it) which really didn't make an impact in the end, and a more general "cached_value" function that takes a cache key and a function closure. The syntax is a little weird, but it would mean that only a couple of lines would need to be added to make something use the cache, and if apc isn't installed, it would just default to the usual. Not sure if that would make it easier to integrate or not.
I think memcache (centralized) would still likely be a better choice to cache anything that's not configuration data, but then you're definitely in the game of having to manage cache invalidation, etc. carefully. That said, it's a nice thing to have easy local caching in the PHP toolset... it was dumb luck that I stumbled on an article talking about using APC's cache api. I'd always just thought of it as an accelerator that you set and forget.