The question of wether you should put domain logic in the database or in Java popped up today. So I figured I should find out why I want to keep it in Java, besides my gut feeling. 😉
A couple of articles on the subject:
I view stored procedures as a means to optimize database access, not as a place to store business logic. I must admit I don’t have much experience with large database systems and stored procedures, but from what I’ve read I see some benefits of Java over DB:
- In memory caching
- Lazy loading for a wide variety of use cases instead of heaps of procedures for each use case
- Transactions across several datasources
- Easier testing
- Easier debugging
In memory caching is really a big issue. Of course it doesn’t help you if you need to retrieve 5000 records instead of one, but network access is one of the most expensive operations in a system. Once a system gets a reasonable cache up and running it has the potential to reduce network access considerably. Of course optimizing you DB access layer helps too. As fowler recommends; you do optimization when you find the hotspots by profiling.