ORM vs Handcoded Data Access Layer
I'm a bit scared to ask this question as it may start a religous war so I want to be really clear on what I'm looking for. I'm looking for a reason(s) why you would or have jumped one way or the other and also for items to add to my lists. I'm looking for the big ticket, big bang items. Also, items specific to a product, maybe, if they are really relevant. At this point I'm trying to evaluate ORM vs Manual not product A vs product B.
ORM Advantages
- Quick to code and low maintenance (in some/most scenarios) - Additional features for "free" (no developer effort)
Hand Coded Advantages
- More Efficient (at runtime, maybe not at dev time?) - Less layers of complexity - Most ORMS seem to struggle with being retricted to sprocs only
In the interests of full disclosure, I really don't like the idea of "something" executing code against my database that I can't directly modify, if I see fit but I can see the potentially massive development time advatages of an ORM.
Its probably also worth noting I'm in a .Net world
[edit] (the question at Using an ORM or plain SQL? seems to answer many of the questions and reinforce the point about performance)
So, to alter my question slightly
Has any built an app using an ORM in the early stages and then gradually replaced with with a handcoded DAL? What were the pitfalls of this approach?
[Further Edit - getting to the heart of the problem now] Having a website be able to execute any SQL against my database is scary. If all access is through sprocs my database lives in nice, safe, comfortable isolation. Using exclusively sprocs removes a lot of, if not all, SQL injection attack vectors. Any comments on that?
Answer
We initially wrote an app using JPA ever since the day it went into production we have regretted it. The amount of database calls being made by the ORM were astronomical, so we have now started the process of piece-meal rewriting the application using good ol' fashioned JDBC utilizing Spring's JDBC helper classes. The pitfalls of starting with an ORM is that we spent a good deal of time learning JPA and at the end of the day we have to replace it with JDBC so our application can be more scalable without adding 3 other nodes to our Oracle RAC. So if you balance it out, the control and precision of JDBC was worth the extra lines of code you have to write. Also, most people do not understand that SQL is not something that can be generated and expected to perform. It is something that has to be written and tweaked to gain maximium performance.