Best Spring/Hibernate configuration for never changing tables

Question

I'm currently using Spring+Hibernate+MySQL for a project I'm working on. I realized that a have quite a few tables which never change. They are static, no insert or updates can ever occur on these tables, and so can be considered immutable. All accesses to these tables are via lazy loading (can be made eager) of entity collections and hql queries.

I'm wondering for such a case what would be the best approach performance-wise for handling such a scenario. I have the basics in place, read only ehcache, query caching and the transactions set to read-only (does this do anything at all for Mysql?). What other things can I look at? Which ISOLATION modes, PROPAGATION modes are best? Should I be looking at other caching solutions? Or should I simply do away with all this and just load the data once into a series of hashmaps (this hopefully will be a last resort)?

Another possible (far-fetched?) solution would be to have some in-memory/no-transaction database and have hibernate connect to it. Do such db engines exist?

I'd appreciate any pointers, experience you guys have had!

Answer 1

I've dealt with exactly this sort of thing before, with enumeration tables of data that doesn't change, and frankly, the simplest thing to do was just to set the tables to eager loading and be done with it. The optimization that you may get from anything else is relatively small unless the tables are VERY large. Not to be dismissive, but your time is likely better spent optimizing another part of your system.

That said, if your tables are particularly large, you might want to consider another method of dereferencing the data that they contain; if the table data is large and truly never changes, you may want to consider another way of populating the object tree other than using Hibernate; it may be beneficial to simply create a class for the enumeration and manage the association of that reference on your own (i.e., without Hibernate).

Answer 2

In my experience these kinds of tables need to be deleted from the database and converted to enumerations or something. Not because of performance but for maintainability. Believe it or not, changing code is (again in my experience) a more straightforward and cut-and-dried operation than writing scripts to change the data in a production database. Especially if you don't necessarily control the database yourself; doubly especially if your company doesn't even control it.

Answer 3

Setup second level cahce's for all the entities ( check hibernate documentation for various configuration/mapping details for caching) ,configure query cache and mark them as immutable in the mapping / use read-only sessions, this would make hibernate not check for modifications for these entities when doing "transactional write-behind" and session flushing.

This is a very common scenario and this is all you should have to do . You shouldn't have to deal with rolling out your own in memory hashmap cache ( second level caches like echache offer you several storage alternative), that what second level cache would do for you. Transaction less DB access doesn't offer you anything , performance wise , so I wouldn't worry about it and let hibernate deal with it .

Answer 4

I believe read-only transactions are a Hibernate optimization. If Hibernate knows that it won't have to figure out if you changed anything in the objects, it can forgo a bunch of steps (CGLib modification of classes?)

Answer 5

I think most of your other questions were answered. However as far as isolation levels go, if your table is never inserted or updated you can use the READ_UNCOMMITTED isolation level, which allows dirty reads, non-repeatable reads and phantom reads. However none of that matters since the data is never changing.

You can look at the different isolation levels and effects of each in the spring javadocs (http://docs.huihoo.com/javadoc/spring/2.5/org/springframework/transaction/annotation/Isolation.html)

This will free up locks on rows the most and will give you the best performance, at least as far as locking goes.