Are There Good Reasons Not to Use an Orm

Are there good reasons not to use an ORM?

There's been an explosion of growth with ORMs in recent years and your more experienced coworkers may still be thinking in the "every database call should be through a stored procedure" mentality.

Why would an ORM make things harder to debug? You'll get the same result whether it comes from a stored proc or from the ORM.

I guess the only real detriment that I can think of with an ORM is that the security model is a little less flexible.

EDIT: I just re-read your question and it looks they are copy and pasting the queries into inline sql. This makes the security model the same as an ORM, so there would be absolutely no advantage over this approach over an ORM. If they are using unparametrized queries then it would actually be a security risk.

The advantages and disadvantages of using ORM

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

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.
Read about the experiences other
people have had with the selected
ORM before you dig in too deep.
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

Using an ORM or plain SQL?

ORMs have some nice features. They can handle much of the dog-work of copying database columns to object fields. They usually handle converting the language's date and time types to the appropriate database type. They generally handle one-to-many relationships pretty elegantly as well by instantiating nested objects. I've found if you design your database with the strengths and weaknesses of the ORM in mind, it saves a lot of work in getting data in and out of the database. (You'll want to know how it handles polymorphism and many-to-many relationships if you need to map those. It's these two domains that provide most of the 'impedance mismatch' that makes some call ORM the 'vietnam of computer science'.)

For applications that are transactional, i.e. you make a request, get some objects, traverse them to get some data and render it on a Web page, the performance tax is small, and in many cases ORM can be faster because it will cache objects it's seen before, that otherwise would have queried the database multiple times.

For applications that are reporting-heavy, or deal with a large number of database rows per request, the ORM tax is much heavier, and the caching that they do turns into a big, useless memory-hogging burden. In that case, simple SQL mapping (LinQ or iBatis) or hand-coded SQL queries in a thin DAL is the way to go.

I've found for any large-scale application you'll find yourself using both approaches. (ORM for straightforward CRUD and SQL/thin DAL for reporting).

Why should you use an ORM?

Making data access more abstract and portable. ORM implementation classes know how to write vendor-specific SQL, so you don't have to.

What are the advantages of using an ORM?

I'd say that if you aren't dealing with objects there's little point in using an ORM.

If your relational tables/columns map 1:1 with objects/attributes, there's not much point in using an ORM.

If your objects don't have any 1:1, 1:m or m:n relationships with other objects, there's not much point in using an ORM.

If you have complex, hand-tuned SQL, there's not much point in using an ORM.

If you've decided that your database will have stored procedures as its interface, there's not much point in using an ORM.

If you have a complex legacy schema that can't be refactored, there's not much point in using an ORM.

So here's the converse:

If you have a solid object model, with relationships between objects that are 1:1, 1:m, and m:n, don't have stored procedures, and like the dynamic SQL that an ORM solution will give you, by all means use an ORM.

Decisions like these are always a choice. Choose, implement, measure, evaluate.

Why is ORM considered good but select * considered bad?

In my limited experience, things are as you describe--it's a messy situation and the usual cop-out "it depends" answer applies.

A good example would be the online store that I work for. It has a Brand object, and on the main page of the Web site, all of the brands that the store sells are listed on the left side. To display this menu of brands, all the site needs is the integer BrandId and the string BrandName. But the Brand object contains a whole boatload of other properties, most notably a Description property that can contain a substantially large amount of text about the Brand. No two ways about it, loading all of that extra information about the brand just to spit out its name in an unordered list is (1) measurably and significantly slow, usually because of the large text fields and (2) pretty inefficient when it comes to memory usage, building up large strings and not even looking at them before throwing them away.

One option provided by many ORMs is to lazy load a property. So we could have a Brand object returned to us, but that time-consuming and memory-wasting Description field is not until we try to invoke its get accessor. At that point, the proxy object will intercept our call and suck down the description from the database just in time. This is sometimes good enough but has burned me enough times that I personally don't recommend it:

It's easy to forget that the property is lazy-loaded, introducing a SELECT N+1 problem just by writing a foreach loop. Who knows what happens when LINQ gets involved.
What if the just-in-time database call fails because the transport got flummoxed or the network went out? I can almost guarantee that any code that is doing something as innocuous as string desc = brand.Description was not expecting that simple call to toss a DataAccessException. Now you've just crashed in a nasty and unexpected way. (Yes, I've watched my app go down hard because of just that. Learned the hard way!)

So what I've ended up doing is that in scenarios that require performance or are prone to database deadlocks, I create a separate interface that the Web site or any other program can call to get access to specific chunks of data that have had their query plans carefully examined. The architecture ends up looking kind of like this (forgive the ASCII art):


Web Site:         Controller Classes
                     |
                     |---------------------------------+
                     |                                 |
App Server:       IDocumentService               IOrderService, IInventoryService, etc
                  (Arrays, DataSets)             (Regular OO objects, like Brand)
                     |                                 |
                     |                                 |
                     |                                 |
Data Layer:       (Raw ADO.NET returning arrays, ("Full cream" ORM like NHibernate)
                   DataSets, simple classes)

I used to think that this was cheating, subverting the OO object model. But in a practical sense, as long as you do this shortcut for displaying data, I think it's all right. The updates/inserts and what have you still go through the fully-hydrated, ORM-filled domain model, and that's something that happens far less frequently (in most of my cases) than displaying particular subsets of the data. ORMs like NHibernate will let you do projections, but by that point I just don't see the point of the ORM. This will probably be a stored procedure anyway, writing the ADO.NET takes two seconds.

This is just my two cents. I look forward to reading some of the other responses.

Is there a good reason to use ORM in an MVC environment?

Well two things... ORM is object relational mapping, that is mapping objects and object types to a database, not necessarily providing a uniform DAL (Database Abstracion Layer) to the backend. So you seem to be working mostly with DALs and not ORMs. Where I work we use ORM to map jobs to a render farm and this allows us to access these same basic objects in any number of applications without storing the data in a hard form (other than in the database). Its is very helpful for when you need to access data in an application agnostic way and share it amongst many other applications in the same form. Long winded, I know-- hope this helps! :)

Why not both ORM and SQL?

Most ORM solutions support native SQL - e.g. Hibernate.

The reason NOT to combine the two is one of consistency - if you have to understand two ways of retrieving data from the database and converting them to objects in your application, you have twice as much to remember, twice as many opportunities for bugs, and twice as much testing to do (for the "connect to database and retrieve data" code).

What happens in practice is that developers who are not comfortable with ORM will tend to use native SQL, and developers who like the ORM tool will do nearly everything using the ORM layer - this will make your codebase hard to maintain and extend.

If you're going to do this, I'd at least enforce the "do SQL through the ORM tool, not directly through JDBC (or whatever)" rule - that gives you at least some consistency, and you can re-use a lot of the object mapping functionality.

Ideally, though, pick a solution and stick with it - hedging your bets is rarely free.

Are There Good Reasons Not to Use an Orm