To what extent is it not advisable to use an ORM?

Question

I am working on a very large system using . NET (ASP.NET MVC) the application requires a critical level of performance. How worthwhile or not to use a ORM? Is there a tool that I can compare in terms of ORM’S and pure SQL performance?

I know there is a difference in performance between pure SQL and ORM, I don’t know if it’s Active Record’s or Orms in general are slower compared to pure SQL if you consider using a ORM, which one probably has the best performance: EF or Nhibernate?

I know that everything also depends on the structure of the bank and the best practices, but in general for a very large system I consider an ORM? Since the cost benefit in relation to system maintenance is much higher than not using.

Answer 1

I am in favour of a mixed approach

• Use the ORM as much as possible to improve readability and productivity. In C# or VB.Net it is possible to use LINQ, which is an extraordinary tool in terms of productivity. The simpler the better.

• When performance needs to be increased, or when the ORM does not provide the right tools for the job, further study the chosen ORM and search for information on the Internet, and only then, go to SQL.

  Orms usually allow you to use an SQL-like language, or allow you to use SQL with specific formatting, which is an intermediate point.

Which to choose?

Working with both, Nhibernate has more features, is more flexible, and has many tools and extension libraries, however Entityframework is simpler and easier to use, in addition to tools to integrate with Visual Studio.

• For a project born with LOW demand

  Choose between Entityframework and Nhibernate, in this case can be reduced to a matter of custom, ie, use the one that’s more familiar.

  If I had to learn one of them, I would choose the Entityframework, it uses the latest technologies, and is evolving rapidly.

  And if you already know both, would Entityframework also, because it is simpler to work, besides having integration with Visual Studio, which helps a lot in productivity.

• For a project born with HIGH demand

  For a project that is already going to be born with a great load of data/users/expectation (imagine a system for a company to manage thousands of employees), then I would still recommend Nhibernate... is a technology that surely will not leave you wanting anything.

References

• Nhibernate X Entity Framework

• Entity Framework 5 and 6 vs Nhibernate 3 - The State of Affairs ^english

• Entity Framework 6 vs Nhibernate 4 ^english

Answer 2

About the Entity Framework

The implementation of DbSet<> makes the data context load the database record only once during the life cycle of the Controller. Therefore, the performance difference between the system written with pure SQL for the system in Entity Framework is negligible if the database is normalized correctly (i.e., with unique primary keys and avoiding unnecessary relationships, such as tables that are updated from time to time and can be replaced by Enums). Because it is very simple, it is ideal for the beginning of a system development. The disadvantage is the lack of support for other banks, such as Oracle, for example.

About Nhibernate

It is a solution older than the Entity Framework, coming from Java when the ORM started. It has more configuration options and works better with other databases than SQL Server. However, the approach to Code First is still somewhat prolific, which makes it slower than the Entity Framework to start a project in terms of productivity, being more recommended for large projects that are already implemented and work with large masses of data, which requires fine tuning of settings.

It is not advisable to use a ORM?

This part is somewhat based on opinions, but initially I would say that there are no restrictions, since the frameworks ORM enables the execution of pure SQL in steps where there are performance bottlenecks. Therefore, neither productivity nor performance are compromised.

---

UPDATE

The Oracle developed its data provider with the Entity Framework, finally. This overcomes the connectivity problem between Oracle and Entity Framework.

A tutorial was also prepared by the Oracle team to remedy any doubts that may arise in the integration.

Answer 3

Proponents of the ORM will say that this does not apply to all projects: not everyone needs to run complex joins; and that the ORM is an 80/20 solution, where 80% of users need only 20% of SQL functionality. But I can tell you that hasn’t been true. Only at the beginning of a project can work without the use of joins or using them in a simplistic way. Afterwards, it will be necessary to optimize and consolidate consultations. Even considering that 80% of users use only 20% of SQL functionality, 100% will have to violate its abstraction to do what needs to be done.

• The advantages of using ORM disappear with increasing complexity as it is necessary to promote an abstraction break by forcing the developer to handle SQL;

• Objects are not an appropriate form of representing relational queries;

• Instead of using relational databases and Orms as a solution to all problems, if your data are objects by nature use Nosql;

• OO design cannot represent relational data efficiently, this is a fundamental limitation of OO design that ORM cannot handle.

Bottom line: Orm and a foot shot from my point of view.

Answer 4

The question is very opinionated, at the time was well accepted, has opinions here and decided by mine that is different.

Why use

ORM is an excellent tool to give some productivity to create Cruds and data access when mastering one of these tools and you need this productivity. That is, when you make repetitive codes that changes only the data model but in the background what is being done is the usual and does not change much, is a hand in the wheel.

It can also be useful in the hands of those who do not know how to handle data persistence mechanisms well. It doesn’t mean that the ORM will make everything great, but there is a tendency for people to make fewer mistakes, especially in terms of security when using something ready.

This implies that they are always simple codes. When you enter complex systems everyone knows you have to start bypassing the ORM and go rogue to meet the demands. And since the future always holds uncertainty, at some point you might have a monster in your hand and it starts to be worse than if you hadn’t used it.

Actually it doesn’t happen that much, since many systems don’t need maintenance so simple that they are.

Why not use

Some people adopt so they don’t have to deal with SQL. This is one of the worst reasons to adopt ORM, including because anything more sophisticated will force you to use SQL in the most difficult way, and even when you don’t need to use SQL doesn’t mean you don’t need to know how to model and query properly. The hard part is creating the templates and queries, not SQL.

Another terrible reason to adopt the ORM is to facilitate database exchange in the future.

That doesn’t make sense because almost no one changes the DB, and if they change, the mistake was so big that the ORM won’t save you. Even if you change, the ORM doesn’t make everything look beautiful and wonderful. The ORM levels down, it doesn’t work well in any database, except the most basic uses.

The most common person wanting to exchange the database is because the ORM is creating a bottleneck because it does not take advantage of the resources of the adopted DB. That is, if you use the resources directly and properly for that DB without going through the ORM would not have the problem.

Of course, that’s always assuming the person knows what they’re doing. When the person does not know, orm or not, changes very little, he will err and luck will define whether he will have a small or a big problem. Actually most cases where people use Orms don’t make much difference.

The problem with the idea that you are doing something simple is that in the future it can no longer be simple, things evolve, and then it will be a problem to deal with the ORM. So make sure the ORM will never be a problem. It’s not enough not to be a problem today.

I will agree with the answer that says that the advantages of the ORM disappear when it becomes complex; and that object models are not good for queries in relational databases.

Another reason not to use it is because there are religious quarrels about which Orms to use, to the point that there are those who say that you should implement a layer in order to exchange the ORM if necessary. I don’t even want to comment on this atrocity.

In fact it is not easy to find the appropriate ORM for your case. And that’s important because you marry him, what you can do with ease depends on what he offers, it’s not all the same.

In practice people accept the defects of the chosen ORM in cases where it does not fit well. That is, the solution is bad.

People only choose the right ORM after choosing often the wrong one. And not everyone can make this evolution, most only accept what they have, as said before.

Microwaves

That’s another thing, even people say it’s not even ORM because it doesn’t implement the standard Repository or at least the Active Record.

For me ORM is just the mapping of data between the database and the language since it doesn’t always have compatible types. Some people consider that you have to map in the form of an object. Whatever it is, the so-called Microorm is something necessary and useful, unless the language is integrated with the database, which is very rare.

THE ORM full is a "facilitator" to make the relational database model with the application object model.

There’s something called impedance Ismatch. Which is also classified as the Vietnam of Computing.

Other solutions

Some people solved the problem by ending the relational and adopting Nosql, so I could model as an object in the database. And then I don’t even know what to call it, since the proper name was already taken over by the ORM abuse. There’s nothing worse than you modeling inefficiently and making programming difficult and almost unfeasible for consistency, just so you don’t use an ORM or change your application to the way you should.

Of course there are cases where Nosql is useful, but not to stop using ORM. People who adopt Nosql admit that the ORM is a bad thing, but decide to take a poison instead of a vaccine.

Why not adopt the relational model in the application? If everything leads to this, it should be obvious that it is the most appropriate solution. It’s effective, it’s not hard to use, it’s just not as pretty and it’s not in fashion. For a long time people did this and nobody said it was a huge problem. One day someone showed up with this ORM guy saying it was nicer to do so and people started to adopt. The problems started to appear later, but by then it had become fashionable.

Entity Framework x nhibernate

The EF is official Microsoft, evolves very well. They say it is easier and is getting fast, as far as it goes.

Nhibernate is more complex and powerful, and has more problems because it is older. Some say that after a while you regret using something simpler, the complex ends up helping you in the hardest part. Today it is very unpopular and is adopted either by legacy or fashion, or because who has a more advanced knowledge and adopts ORM with a little more awareness.

Completion

For simple things, for something fast, for situations where it doesn’t cause harm, the ORM can be used, and many cases are like this.

But if you want a better, more efficient system, that you have more control, that is quieter to maintain, where you want something of more quality, the ORM should not be used.

Note that I am not saying that whenever using ORM will not have minimum quality, a 5.00 dish can be nutritious, yummy and kills hunger. A 50.00 will do the same better. There’s a time for everything.

There is no single case that should or should not use, depends on the person, depends on the project and depends on the demand for quality.

Even with some recent improvements, if you want efficiency and performance, do not use ORM. Even to leave near the ideal is a lot of work. But reinforcing, without proper knowledge, will have problems to get performance with or without ORM.