This blog as moved to: http://nerditorium.danielauger.com/

How to fail at ORM

NoORM


Let's face it: if trends continue, some form of ORM will be a fact of life at most .net organizations that develop business / enterprise software. Microsoft isn't playing games this time with Entity Framework. They mean for it to succeed. Additionally, at the time of writing this, NHibernate has been downloaded 391,024 times from sourceforge alone (there is more than one place to download it from). This being the case, I’m going to give everyone a few pointers to ensure that their first attempt at ORM fails.

Here are my tips to insure ORM adoption failure (in no particular order):

Consider the ORM’s SQL engine as a replacement for SQL knowledge. The whole point behind ORMs is so that I don’t have to write or understand SQL right? WRONG!

Consider the ORM’s SQL engine to be a black box. I got back the correct dataset, so this must be the best SQL the ORM can produce right? WRONG! Most ORMs will create drastically different SQL depending on how the object query is structured.

Don’t get more than a skin deep understanding of the ORM. If you run into a brick wall with a bit of behavior from the ORM, you can follow two paths. You can A) learn about the finer points of the ORM to resolve the issue, or B) rip the ORM out of your application. The latter is the outcome I’ve seen more often than not. A classic example of this is the N+1 select issue where the app calls the database in a loop. ORMs have things such as eager loading, multi-queries, and future queries to avoid extra trips to the database. However, it’s best to ignore the existence of those features if you want to fail at ORM.

Use ORM generated schema without manually tweaking it. Many ORMs will happily create a schema for the developer just how they specified it, and index free. Ideally you shouldn’t use generated schema at all once you are up and running. A DBA should be creating a schema using relational theory. However, if you want to fail, it’s best to just used that generated schema.

Use the ORM for 100% of your data access.
Most ORMs allow for dropping into prepared SQL, stored procedures, and even db function calls. However, it’s best to ignore this functionality if you want to fail.

Maintain OO purity at all costs.
Does fetching your aggregate root cause an 11 table join? So be it.

Cut the DBA out of the development process.
The whole point of ORM is to cut out the DBA right? WRONG! The DBA should be just as active with helping to craft the data access strategy as they would with a hand rolled data access layer. Cutting the DBA of the picture is a recipe for failure.

Don’t profile your application.
If you want to fail, it’s best to find out if you have have created a SQL nightmare once you hit production. NHProf, EFProf, L2SProf, and SQL Profiler are your friends. Ignore them to fail.

I hope you find these tips helpful. I’d like to hear about any other tips for ORM failure you might have.

11 comments:

ToddO said...

Your first point is key. Programmers and designers in .net assume that OO and the DAL means they do not have to understand the relational model or SQL. I think this attitude originates from the idea that the database is just a place to persist data. If anything that is backwards, as the application code is there to "serve" the data in the database. Data is the irreplaceable resource here, not application code.

Another point you sort of get at is that every part of every solution should explicitly engineered. IOW, ORM is a tool, not a silver bullet. I look at ORM as a way for programmers to jump start their SQL; it should never be assumed that the ORM will generate "good enough" SQL for a well-engineered solution.

Anonymous said...

> Maintain OO purity at all costs.
> Does fetching your aggregate root
> cause an 11 table join? So be it.

Can you clairify this one a little?

Daniel Auger said...

Anonymous, what I'm getting at there is that sometimes you need to give up OO purity for performance. I've seen things such as a Company object that has to touch 10+ tables when pulled into the application. Although the object may be modeled with OO purity, it will introduce performance problems.

Anonymous said...

One of the underlying reasons for avoiding SQL and DBAs in ORM solutions is the high cost of DBA involvement. Costs in trying to make it work, costs in unclear limits of SQL, costs in including yet another layer between the development and the application's implementation, and costs in endless tweaking of SQL. Moreover, the tweaks never end. Ultimately the apps success or failure is entirely dependent on the DBA.

In face of such uncertainties, we have developers who'd rather struggle with an ORM and produce a marginal solution than work with DBAs to produce an optimal one.

This has been my experience having been on both sides and having dealt with many ORMs and db apps over the past 20 years.

Anonymous said...

I really do not understand the issue with databases. Databases are easy. SQL is easy. I am astonished that any application developer would want to put a complicated additional layer of abstraction into the mix. In fact, I'd probably fire them, and find a developer who understood database and transactional applications, and can actually architect. One value of an ORM is the ability to abstract the database from its unique vendor specific aspects. But how often is migration of a database a real business problem? And its not that hard to do even without an ORM.
High cost of a DBA? Even higher cost of an expensive developer poking a black box with a stick if they dont understand data management.
If the app success rests on the DBA, then my opinion is that the application architect needs to be brought to account on the design. We clearly have different experiences and expectations.

Daniel Auger said...

> I really do not understand the issue with
> databases. Databases are easy. SQL is easy.
> I am astonished that any application developer
> would want to put a complicated additional
> layer of abstraction into the mix"

Unfortunately object-relational mapping usually can't be avoided. If you haven't heard of it, there is something called the "object-relational impedance mismatch" which is a very real computer science problem. It's the reason ORMs exist. ORM is not an optional step. Every single OO application that uses a SQL database has to map the relational module to the object model. This means that either the developers hand-roll their own ORM layer (which is very complicated), or use a 3rd party framework. SQL generation is only one of many features in an ORM framework. Many ORMs can use procs instead of generated SQL, therefore making it an optional feature. The reason why many ORMs favor generated SQL is because you can create queries that only pull back exactly what you need for any given scenario, as opposed to creating procs and mappings for every fetch path variation that is needed during the lifetime of the application.

I think I may have to do a follow-up post on what all an ORM does besides execute SQL.

Anonymous said...

SQL is not the problem, databases are not the problem. ORMs are not the problem. It's the DBAs. They are the ones who make data management into something larger than it ought to be. OO apps are not going to disappear anytime soon. ORM impedance mismatches will continue to exist, good and bad developers will come and go. But DBAs need to figure out how to work and make things happen instead of merely throwing hurdles.

scarydba said...

SQL is not the problem, databases are not the problem. ORMs are not the problem. It's the developers. They are the ones who make data management into something larger than it ought to be. OO apps are not going to disappear anytime soon. ORM impedance mismatches will continue to exist, good and bad DBAs will come and go. But developers need to figure out how to work and make things happen instead of merely throwing hurdles.

Not really pretty when it's on the other foot, is it?

Knowledge, skill and communication are the problems and those problems exist regardless of the title or skill set of the person involved.

Anonymous said...

"Knowledge, skill and communication are the problems and those problems exist regardless of the title or skill set of the person involved."

Then what are you doing here debating ORMs instead of attending some self-help corporate consultant seminar?

Daniel Auger said...

>>Knowledge, skill and communication are the
>>problems and those problems exist regardless of
>>the title or skill set of the person involved

>Then what are you doing here debating ORMs
>instead of attending some self-help corporate
>consultant seminar?

He's here to distill much of my post and the comments down to one simple idea: The developer vs. DBA conflict is a noisy distraction which keeps us from moving forward.

Anonymous said...

Per the prior statement of DBA vs Developer. I am incurring this problem myself as the Dev Architect with a DBA that is comfortable in their SQL and that is it. She is not willing to learn ORM and the benefits of it & hear out what (as mentioned in this article) the downsides of it.
--
ORM in development is as simple as this, ORM is forcing me to work with the DBA to ensure the data model is not driving the architecure of the object model. That both models work well with each other.

In using SQL profiler you will quickly see what queries are faster by using sprocs. If you are using a good ORM tool this will strong type your parameters and sproc name directly into your code. This is awesome because this causes run time errors that you can quickly fix if the sproc changes not design time errors where the sproc may change and the code knows nothing about it. How do you have a sproc change without the code breaking? Having a DBA that has a lot of optional (NULL) parameters.
--
The other problem in this whole equation, a good developer must know SQL. A good DBA has no concern of code nor understands code and they generally don't have to. I use LLBL Gen Pro and it does a lot of the plumbing work for me, this means time/labor savings in creating code to do: object creation (and their classes), persistenc, sql injection protection, db objects: connection, command, paramaters, etc. Much of this is repetitive plumbing code that is time saved.
---
ORM should make the DBA and the Developer work together not have one hand lead the other.

Post a Comment