Why not just stored procedures?

There always comes a phase in the software development when the team rethinks its strategy. The technology selection is critical and so is the development team's expertise. O-R mapping is one such area where "sounds good and feels good" is usually different from how it gets implemented. The reason I am mentioning O-R mapping because it tries to solve one of the most struggled areas - Object Persistence Impedance Mismatch. Why is it so important? It is important because of inherited difference between J2EE and database technologies. Other issues like differences in skill set, ownership differences and modeling and design principles also play important roles. O-R mappers bridge the same gap, but it also requires a new set of training to the staff. And this is the time when you hear... Why not just stored procedures? Most of the time the explanations are very administrative. Stored Procedures are harder to maintain, it won't be J2EE, debugging problems, abstraction and what not. The reasons are valid but so is the truth that a well written Stored Procedure gives a better runtime performance. And after all no design is a bad design if it makes sense (Eric Evans).

Then why choose O-R mappers? Before making a strategy change make sure the change is not because of the unfamiliarity with the technology. The design change can have more impact on the mission critical applications. The O-R mappers (e.g, Toplink) gives you the flexibility to change the database without changing the domain layer. Relational, non-relational or just a pure XML data source does not require any change in the application. It also comes up with all the goodies of Java and Object Oriented concepts along with the inherited security inbuilt into the Java systems. Still a few questions remain.. What if the database is not going to be changed. What if the abstracted system is not required. What if XML datasource is not a possibility, and what if the development team already has some good familiarity with SQL and Stored Procedures? Toplink for one, provides much more than object mapping. Object caching support, various descriptor level caching policies, Query flexibility, Transaction support, locking, visual toolkit and meta-language driven architecture support. In most cases, it boils down to a well written stored procedure against a poorly mapped domain design. Check if proper patterns are used. Revisit the domain design. Choose the proper cache policy. Use batch reads. If nothing else can be done then a custom SQL or a Stored procedure can also be integrated. Exploit the thread safety of Java. If done right, the overall performance impact will be negligible. And be realistic and avoid creating a new persistence model on top of Toplink or Hibernate. Its highly likely that the custom model will not have all the maturities of the commercially available systems. And don't create it because it makes you feel good. Most of these technologies are pretty matured and have community contributions. Compare Hibernate or Toplink and choose one. Choose one and stick with it. Don't create another abstraction with an assumed flexibility to be able to change Toplink with Hibernate in future. Believe me it rarely happens. The common denominator between these O-R mappers usually do not provide the best performance and its not a bad idea to stick with a proprietory solution if it gives the best solution. The life time of an application on average is 60-96 months. After that, its not just one component but the entire application which is revisited. Join the community and make rational decisions.