ORM vs Handcoded Data Access Layer

Question

I'm a bit scared to ask this question as it may start a religous war so I want to be really clear on what I'm looking for. I'm looking for a reason(s) why you would or have jumped one way or the other and also for items to add to my lists. I'm looking for the big ticket, big bang items. Also, items specific to a product, maybe, if they are really relevant. At this point I'm trying to evaluate ORM vs Manual not product A vs product B.

ORM Advantages

 - Quick to code and low maintenance (in some/most scenarios) 
 - Additional features for "free" (no developer effort)

Hand Coded Advantages

 - More Efficient (at runtime, maybe not at dev time?)
 - Less layers of complexity
 - Most ORMS seem to struggle with being retricted to sprocs only

In the interests of full disclosure, I really don't like the idea of "something" executing code against my database that I can't directly modify, if I see fit but I can see the potentially massive development time advatages of an ORM.

Its probably also worth noting I'm in a .Net world

[edit] (the question at Using an ORM or plain SQL? seems to answer many of the questions and reinforce the point about performance)

So, to alter my question slightly

Has any built an app using an ORM in the early stages and then gradually replaced with with a handcoded DAL? What were the pitfalls of this approach?

[Further Edit - getting to the heart of the problem now] Having a website be able to execute any SQL against my database is scary. If all access is through sprocs my database lives in nice, safe, comfortable isolation. Using exclusively sprocs removes a lot of, if not all, SQL injection attack vectors. Any comments on that?

Solution 1

We initially wrote an app using JPA ever since the day it went into production we have regretted it. The amount of database calls being made by the ORM were astronomical, so we have now started the process of piece-meal rewriting the application using good ol' fashioned JDBC utilizing Spring's JDBC helper classes. The pitfalls of starting with an ORM is that we spent a good deal of time learning JPA and at the end of the day we have to replace it with JDBC so our application can be more scalable without adding 3 other nodes to our Oracle RAC. So if you balance it out, the control and precision of JDBC was worth the extra lines of code you have to write. Also, most people do not understand that SQL is not something that can be generated and expected to perform. It is something that has to be written and tweaked to gain maximium performance.

Solution 2

I've used Subsonic for a couple of largish projects. I'm not advocating the use of one ORM over another, but I wouldnt do another database-related project without one. The ability to regenerate the entire db access layer whenever I change the database structure, the ability to add features in one place that affect the entire DB layer.

The thing is that you have to understand how it interacts with the database, otherwise you run the risk of writing (severely) under-performing code. Your ORM tool might give you a nice object-oriented view of your data, but you might find you're streaming whole tables to the client in order to do simple processing. The database is good at relating and filtering data, so make sure it still has that job. Ironically, Subsonic's lack of joins (in the version I used), helped with that, because it forced me to create DB Views to combine data where needed.

A friend of mine worked with a guy who developed an in-house ORM tool. It had the nice feature of locally caching everything that you could possibly want from the database, done by walking through the foreign keys. The downside was that reading one column from one row in the DB resulted in an excess of 11,000 select statements.

Solution 3

I've been writing and maintaining my own ORM for 8 years now. It started in Java, then translated to C#. I can't imagine writing any database backed system without it. It is way simpler than NHibernate, and doesn't have all its features but it gets the job done and it is quite fast even though it uses reflection extensively, since it replaces XML configuration with reflection over the DAO class definitions.

I'm quite happy with it, and would not use any other approach.

EDIT: regarding SQL injection attacks: a recent system I developed using this ORM was extensively audited and absolutely no injection was permitted. The reason is simple: it generates SQL on the fly and always uses parameters, no string concatenation.

Solution 4

A few weeks ago I started development on a new project. I had complete control over the tools I could use. I started with db40 because I wanted to eliminate this question altogether; I just wanted to persist my objects directly, forget about ADO.NET or OR/M. But db40 had problems so I ha to abandon it.

My next choice was ADO.NET because I thought it would be fast and easy. But I had to write way too much code, use too much "string sql" and it was just a chore altogether. I mean, it was a royal PITA and after coding two repositories with it I wanted to cut my wrists.

My third choice was NHibernate. I have had previous problems with NHibernate in a situation where I needed to use disconnected objects (such was the case this time as well, hence why it took me three tries to get to it). But that was NHibernate 1.2. This time I got the 2.0 binaries and had zero problems with updating disconnected objects. I also had to write way fewer lines of code and it was super-simple when I needed to refactor things, which was probably most important since my design changed rapidly.

I'm sold on NHibernate now. It appears highly optimized as well. Honestly, I can't find a negative to it. And there's no stored procedures.

I know it's my OR/M of choice going forward and I'll never write straight ADO.NET again.

Solution 5

Have you tried redbean? In development phase it's fluid and easy to use while in production mode (frozen) its fast. http://redbeanphp.com

View more solutions

Comments

[mxmissile]

not sure who said this, but I quote "if you are writing data access code manually, you are stealing from your employer"

[Frustrating Developments]

I wouldn't consider no sprocs as a positive. If you need to manipulate large amounts of data, the database is the place to do it

[Frustrating Developments]

you've highlighted my main concern with ORMs. I don't really know what they are doing to get my data

[geofftnz]

This was my concern also. The first thing I did was fire up SQL Profiler (MSSQL). My concerns evaporated.

[Webjedi]

Yeah there is definately this incorrect assumption that once you use an ORM you're A) stuck with the SQL it makes and B) that SQL is bad. On both counts it's a bit of misinformation. If the query is bad, make your own and move on.

[Webjedi]

Again I have to say you aren't stuck with the SQL it generates. It can be replaced easily. If you could get 100% of your data access handled for you, but have to tweak 10% of those queries to get the best perf out of it...you still win with an ORM.

[Nick]

It's not simply about performance, Webjedi. If you need multiple objects to come back in a header with a couple of details for instance, I bet 100% of ORM's will make 3 database calls. However, with JDBC, I can get all that data in one call. Which do you think will scale better?

[Webjedi]

I'm pretty sure with some helper tools like Rhino Commons the same can be achieved with nHibernate. And even if you can't you're not violating and law that says you can use an ORM for most of you data access and leave the wonky stuff to direct data access. Just saying it's not all or nothing.

[dkretz]

It's possible that, with enough tweaking and poking, it can be made to work. But by definition it's an additional abstraction layer with generated-not-written code, plus the hacks. Not everyone thinks DALs are so bad that they need avoiding.

[Alex Beardsley]

i've had the same trouble with hibernate & JPA, and have too had great experience with Spring's JDBC helper classes. they are simply amazing.

[Mark]

In my case, it is all about performance... I started off with an ORM and ended up with an app that is too slow for my customers. Sometimes ORM technology leaves you in a position where optimizations are very tricky.

[Janus Troelsen]

What's it called? Where's the source?

[Otávio Décio]

@user309483 - it is becoming abandonware because I haven't had time to update - databroker.codeplex.com

[rsenna]

I downvoted this question back in 2010/12/30, without a comment. Now I'm going through some sort of "repent" phase (mid-life crisis perhaps?) and trying to rollback most of those -1 that I've made in the past. But I simply cannot do that here. A good ORM saves development time. To simply avoid using an ORM because it could give you a bad performance is nonsense. IMHO It is just another incarnation of premature optimization.