Does it make sense to use OR-Mapper?

Question

Does it make sense to use OR-mapper?

I ask this stack overflow question because it is the best I know to find smart developers who want to give them help and opinions.

My reasoning is this:

1.) Where does SQL belong?

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

b.) Problems with applications during production can often be resolved between tables and stored procedures without releasing new assemblies.

2.) How can I manage the created SQL? I trust parsing trees to generate efficient SQL. I have a lot of experience optimizing SQL in SQL Server and Oracle, but I wouldn’t feel cheated if I never had to do this again. :)

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

I used a repository template with a public data access level. If you need to cache a collection, I cache it. I also have experience using EF on a small CRUD application and experience helping set up an NHibernate application that is experiencing performance issues. Therefore, I am a little biased, but willing to learn.

Over the past few years, we have all heard many respected developers advocating the use of specific OR-Mappers (Entity-Framework, NHibernate, etc.).

Can someone tell me why someone should go to ORM for basic development in a large project?

edit: http://www.codinghorror.com/blog/2006/06/object-relational-mapping-is-the-vietnam-of-computer-science.html seems to be actively discussing this topic, but is deprecated.

One more edit: It seems that everyone agrees that Stored Procedures should be used for large enterprise applications, because of their performance and their ability to add programming logic closer to the data.

I see that the strongest argument in favor of OR-cards is the productivity of developers.

I suspect that the big motivator for the ORM movement is the developer’s preference for residual agnosticism (without worrying about whether the data is stored in memory [other than caching] or in the database).

ORMs seem like outstanding time savings for local and small web applications.

Perhaps the best advice I see is from client09: use ORM tuning, but use stored procedures for intensive database material (AKA when ORM is insufficient).

Answer 1

I have been a SP professional for many years and thought that this was ONLY the right way to develop a database, but the last 3-4 projects that I did, I completed in EF4.0 without SP and the improvements in my performance were really impressive - I can do something in a few lines of code that I would need the day before.

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

So, the short answer is for me: developer productivity is the reason for using ORM - as soon as you complete the training anyway.

Answer 2

Another approach ... With increased speed without SQL, you can now try to store your data instead of the object / document database. This way you basically avoid hell, which is OR. Store the data in the form in which their application uses it, and perform the conversion behind the scene in the workflow to move it to a more relational / OLAP format for further analysis and reporting.

Answer 3

Stored procedures are great for encapsulating database logic in one place. I was working on a project that used only Oracle stored procedures, and I'm currently using Hibernate. We found it very easy to develop redundant procedures because our Java developers did not understand the dependencies of PL / SQL packages.

As a DBA for a project, I find that Java developers prefer to store everything in Java code. You come across the case: "Why don't I just go through all the objects that have just returned?" This triggered a series of "Why doesn't the index care about this?" questions.

With Hibernate, your objects may not only contain related database properties, but may also contain any actions taken with them.

For example, we have a Task Object. You can also add or change a task. This can be modeled in Hibernate Entity in the requested names.

So, I would say go with the installation of ORM, but use the procedures to work with the database.

The disadvantage of storing your SQL in Java is that you risk developers using unparameterized queries, leaving your application open for SQL Injection.

Answer 4

The following is just my personal opinion, therefore it is rather subjective.

1.) I think that it is necessary to distinguish between local applications and corporate applications. For local and some web applications, direct database access is fine. For enterprise applications, I feel that better encapsulation and rights management makes stored procedures a better choice in the end.

2.) This is one of the big problems with ORM. They are usually optimized for specific query patterns, and while you use those generated by SQL, they are usually of good quality. However, for complex operations that need to be performed close to the data in order to remain efficient, I feel that using manual SQL code is a stilol way of transition, in which case the code goes into SP.

3.) Working with objects as data objects is also beneficial compared to direct access to “free” data sets (even if they are printed). Deamination of a result set in an object graph is very useful, regardless of whether the result set was returned by SP or a dynamic SQL query.

If you are using SQL Server, I invite you to take a look at my open-source project bsn ModuleStore , which is the basis for Database Version and using SP with some simple ORM concept (serialization and deserialization of objects when calling SP).