Does it make sense to use an OR-Mapper?

Question

Does it make sense to use an OR-mapper?

I am putting this question of there on stack overflow because this is the best place I know of to find smart developers willing to give their assistance and opinions.

My reasoning is as follows:

1.) Where does the SQL belong?

a.) In every professional project I have worked on, security of the data has been a key requirement. Stored Procedures provide a natural gateway for controlling access and auditing.

b.) Issues with Applications in production can often be resolved between the tables and stored procedures without putting out new builds.

2.) How do I control the SQL that is generated? I am trusting parse trees to generate efficient SQL. I have quite a bit of experience optimizing SQL in SQL-Server and Oracle, but would not feel cheated if I never had to do it again. :)

3.) What is the point of using an OR-Mapper if I am getting my data from stored procedures?

I have used the repository pattern with a homegrown generic data access layer. If a collection needed to be cached, I cache it. I also have experience using EF on a small CRUD application and experience helping tuning an NHibernate application that was experiencing performance issues. So I am a little biased, but willing to learn.

For the past several years we have all been hearing a lot of respectable developers advocating the use of specific OR-Mappers (Entity-Framework, NHibernate, etc...).

Can anyone tell me why someone should move to an ORM for mainstream development on a major project?

edit: http://www.codinghorror.com/blog/2006/06/object-relational-mapping-is-the-vietnam-of-computer-science.html seems to have a strong discussion on this topic but it is out of date.

Yet another edit: Everyone seems to agree that Stored Procedures are to be used for heavy-duty enterprise applications, due to their performance advantage and their ability to add programming logic nearer to the data.

I am seeing that the strongest argument in favor of OR mappers is developer productivity.

I suspect a large motivator for the ORM movement is developer preference towards remaining persistence-agnostic (don’t care if the data is in memory [unless caching] or on the database).

ORMs seem to be outstanding time-savers for local and small web applications.

Maybe the best advice I am seeing is from client09: to use an ORM setup, but use Stored Procedures for the database intensive stuff (AKA when the ORM appears to be insufficient).

Answer 1

I was a pro SP for many, many years and thought it was the ONLY right way to do DB development, but the last 3-4 projects I have done I completed in EF4.0 w/out SP's and the improvements in my productivity have been truly awe-inspiring - I can do things in a few lines of code now that would have taken me a day before.

I still think SP's are important for some things, (there are times when you can significantly improve performance with a well chosen SP), but for the general CRUD operations, I can't imagine ever going back.

So the short answer for me is, developer productivity is the reason to use the ORM - once you get over the learning curve anyway.

Answer 2

A different approach... With the raise of No SQL movement now, you might want to try object / document database instead to store your data. In this way, you basically will avoid the hell that is OR Mapping. Store the data as your application use them and do transformation behind the scene in a worker process to move it into a more relational / OLAP format for further analysis and reporting.

Answer 3

Stored procedures are great for encapsulating database logic in one place. I've worked on a project that used only Oracle stored procedures, and am currently on one that uses Hibernate. We found that it is very easy to develop redundant procedures, as our Java developers weren't versed in PL/SQL package dependencies.

As the DBA for the project I find that the Java developers prefer to keep everything in the Java code. You run into the occassional, "Why don't I just loop through all the Objects that just returned?" This caused a number of "Why isn't the index taking care of this?" issues.

With Hibernate your entities can contain not only their linked database properties, but can also contain any actions taken upon them.

For example, we have a Task Entity. One could Add or Modify a Task among other things. This can be modeled in the Hibernate Entity in Named Queries.

So I would say go with an ORM setup, but use procedures for the database intensive stuff.

A downside of keeping your SQL in Java is that you run the risk of developers using non-parameterized queries leaving your app open to a SQL Injection.