OK, this is on my todo list but someone else who really knows their Drupal could do it, too. I am therefore posting it here.

One of the limitations of the path module, which is responsible for the "fancy" URL aliases like "stf4/alliance" rather than "node/###", is that it makes a separate page request for every link on the page. On many pages, that can be hundreds of queries. While each one is small, that's still an obscene number of queries. That makes sense on a site with hundreds or thousands of aliases, but we have at the moment only 65. It's much faster for our use case, then, to simply preload them all and be done with it.

I have devised an idea for how to get around that issue. It involves cloning the path module to create a "fastpath" module, which will move all alias records from the url_alias table to its own table and then use the custom_url_rewrite() function to do a separate lookup. Because it's doing the lookup itself, it can pre-cache everything however it wants. That should save us thousands of queries per hour, as well as be useful to Drupal in general. (Drupal 7 will hopefully have a smarter caching algorithm, but for now we're stuck with this.)

If you understood the above description, let me know that you want to work on this and I'll try to help you however I can. If not, I'll see about getting to this, um, sometime. But we really need fewer path queries. Seriously.