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).

Icono

CSLA is not an OR-Mapper. This is a common misconception. – Annihilator

Maybe this is more current for you. #404583 – Chalet

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.

Vindicate

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.

Cop

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.

Promotive

In response to: "With Hibernate your entities can contain not only their linked database properties, but can also contain any actions taken upon them." But I can get this without an ORM. Also, single responsibility must play a part here as well. What is the role of the Dto: Transfer data or calculate based off values? – Icono

Answer 4

The following is just my private opinion, so it's rather subjective.

1.) I think that one needs to differentiate between local applications and enterprise applications. For local and some web applications, direct access to the DB is okay. For enterprise applications, I feel that the better encapsulation and rights management makes stored procedures the better choice in the end.

2.) This is one of the big issues with ORMs. They are usually optimized for specific query patterns, and as long as you use those the generated SQL is typically of good quality. However, for complex operations which need to be performed close to the data to remain efficient, my feeling is that using manual SQL code is stilol the way to go, and in this case the code goes into SPs.

3.) Dealing with objects as data entities is also beneficial compared to direct access to "loose" datasets (even if those are typed). Deserializing a result set into an object graph is very useful, no matter whether the result set was returned by a SP or from a dynamic SQL query.

If you're using SQL Server, I invite you to have a look at my open-source bsn ModuleStore project, it's a framework for DB schema versioning and using SPs via some lightweight ORM concept (serialization and deserialization of objects when calling SPs).

Telephone