ORM vs Handcoded Data Access Layer

Question

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 1

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.

Answer 2

I've used Subsonic for a couple of largish projects. I'm not advocating the use of one ORM over another, but I wouldnt do another database-related project without one. The ability to regenerate the entire db access layer whenever I change the database structure, the ability to add features in one place that affect the entire DB layer.

The thing is that you have to understand how it interacts with the database, otherwise you run the risk of writing (severely) under-performing code. Your ORM tool might give you a nice object-oriented view of your data, but you might find you're streaming whole tables to the client in order to do simple processing. The database is good at relating and filtering data, so make sure it still has that job. Ironically, Subsonic's lack of joins (in the version I used), helped with that, because it forced me to create DB Views to combine data where needed.

A friend of mine worked with a guy who developed an in-house ORM tool. It had the nice feature of locally caching everything that you could possibly want from the database, done by walking through the foreign keys. The downside was that reading one column from one row in the DB resulted in an excess of 11,000 select statements.