The advantages and disadvantages of using ORM [closed]

Question

I would like to discuss the advantages and disadvantages of using ORM (such as ADO.NET).

Advantages:

• Speeds-up Development - eliminates the need for repetitive SQL code.
• Reduces Development Time.
• Reduces Development Costs.
• Overcomes vendor specific SQL differences - the ORM knows how to write vendor specific SQL so you don't have to.

Disadvantages:

• Loss in developer productivity whilst they learn to program with ORM.
• Developers lose understanding of what the code is actually doing - the developer is more in control using SQL.
• ORM has a tendency to be slow.
• ORM fail to compete against SQL queries for complex queries.

In summary, I believe that the advantages of using an ORM (mainly the reduced time taken to perform repetitive tasks) are far outweighed by the disadvantages of ORM e.g. it's difficulty to get to grips with.

Can people point out where I am going wrong and suggest any further advantages/disadvantages.

Answer 1

"ORM fail to compete against SQL queries for complex queries."

• Well both LINQ-SQL and Entity Framework Allow complex queries and even translation of SQL query results into objects.

"Developers loose understanding of what the code is actually doing - the developer is more in control using SQL."

• Not really, if you know what you are doing. SQL profiler is enough to see what the translated SQL queries are.

"ORM has a tendency to be slow."

• Yes, but delay loading and some smart options can make it almost as fast.

"Loss in developer productivity whilst they learn to program with ORM."

• Hibernate and the Entity Framework might take time to learn but in the long run they will save time in development. LINQ-SQL on the other hand has little to no learning curve involved.

I say, use ORM but keep this in mind.

1. Design your queries and write code that will result in the least number of roundtrips with the server. It's the overhead taken for the roundtrip that takes up time.

2. Read about the experiences other people have had with the selected ORM before you dig in too deep.

3. Always compare your queries with the actual ones being executed in SQL server profiler.

Edit: You wouldn't use an ORM for a performance critical situation same way you wouldn't use .Net or Java to write an operating system. Consider your requirements before choosing. Even if you don't use an ORM, you will end up doing some mapping yourself either via repeating a lot of code or by using a data dictionary. Why not use an ORM and know how to use its options to make it ALMOST as fast? Weigh up the advantages and disadvantages and make your choice.

http://mikehadlow.blogspot.ca/2012/06/when-should-i-use-orm.html

Answer 2

Depending on the requirement, you might want to choose to use or not ORM. For example: Multiple persistence engine support (need to run on Oracle, DB2, MySQL, SQL Server, etc.), you might benefit from the abstraction that you get from ORM at the cost of potential app performance lost.

If you know that you are going to only support a particular persistence engine and you want to be able to take advantage of a particular feature in the persistence engine that might not be supported in the ORM, well... clear enough which choice you should choose.

Another factor might be developer knowledge like you mentioned and the time to learn the new stuffs and the actual project time (hard deadline, etc.). This goes for both staffs that know a particular ORM already versus staffs that does not know ORM but excel in ADO.NET / any other lower level data access technology.