I was thinking of loading the location data into memory to effectively reduce database queries and writes, what do you guys think about that? Would it eventually create too much overhead with too many rooms?
are the old objects moved to front if they are accessed? if not, you might drop some old objects that are very often used..
Nope. It's just a dumb cache built on a hash table with link list chains. The width and depth are configurable. It allows specific objects to be marked as non-swappable (I mark my root object as non-swapable and objects attached to files and streams, like connected players). It does keep and track access statistics. So, there's plenty of opportunity for experimentation and measurement. Like perhaps continually moving accessed objects to the head of the chain. Or maybe adding an field that ages the old entries every time a chain is searched and removing ones that reach a certain threshold upon a synchronization. Or perhaps pre-loading likely to be accessed objects (i.e. when a player moves to a new location, preload the objects, exits, mobiles and adjacent locations).
There are some considerations depending on your back-end storage system. It might already be implementing it's own cache. You might be accessing the back-end through another application, which does not/cannot know about modified objects in your servers cache.