Monday, February 14, 2011

Why not ORM: 5 ORM myths dispelled, Part 1

ORM-Robot Let's start with a DISCLAIMER: If you are already an active user of any ORM, this specific post is not for you. I'll talk to you later. For everyone else, let's continue.

In my role as a technical evangelist, I get the chance to travel the globe and talk to developer audiences on a regular basis. To keep a clear picture of how developers are working, I often informally poll audiences and try to listen for trends in development behavior. One trend that continues to stand out is how many developers still do all data access by hand!

What do I mean "by hand?" I mean manually creating and destroying ADO.NET objects (like SqlConnection and SqlCommand) to move data between apps and data stores. (This includes developers that use tools to simply auto-generate "by hand" code- that's just saving keystrokes.)

Does that describe you? Hopefully it does to a certain degree (otherwise you've overlooked and/or disregarded the DISCLAIMER). You need to stop. You need to reevaluate how and why you're building applications.


I'm going to talk primarily to web developers, A) because it's what I know best, and B) because I know many readers of this blog are just that. Think back to web development circa 1999 and good 'ol "classic" ASP. We've moved well beyond the days of those spaghetti code script pages, but let's illustrate how data access was commonly done:

Dim Conn
Dim SQLTemp

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.connectionstring =
 Source=" & Server.MapPath("\database_name.mdb")& ";"

SQLTemp = "SELECT * FROM table_name"
set rstemp=Conn.execute(SQLTemp)

Do While Not rstemp.EOF



Set Conn = Nothing
You created a connection object. You created a command. You executed it and looped through the results. And in an application working with lots of data, you did this over, and over, and over…
Fast forward to the modern .NET era. If you're still doing data access "by hand" (or having a tool generate code for you), you are probably doing some variant of this:
SqlConnection conn = new SqlConnection("connection string...");

SqlDataReader rdr = null;

   SqlCommand cmd = new SqlCommand("select * from Customers", conn);

   rdr = cmd.ExecuteReader();

   while (rdr.Read())
An improvement syntactically over VBScript, yes, but in concept it's the same old "naive" model for working with data: create connection, create command, execute and process results. Sure, you can wrap this in a pretty "framework"- you pass-in SQL, you get back a DataSet- but you're still doing pretty "raw" data access. There is no "software intelligence" helping you look for opportunities to lazy load data, optimize SQL, or cache data efficiently. That's all on you! There must be a better way.
Ah, ORM- that's Object Relational Mapper, for the uninitiated. Tomes have been written about ORM over the years, its benefits, its problems, its limits. Boil it all away and a good ORM (generally speaking) has a simple goal: make it easier to for developers to work with data. Data access is not rocket science. Information sitting in structured tables on disk needs to be retrieved and loaded in to memory for an application to do additional processing. Things get a bit harder when you introduce the real world complexities of a reliable, performant system. Data access needs to be fast, it needs to ensure data integrity, and it needs to be efficient so it doesn't "hog" server resources.
Still, these are well defined problem areas that don't change from application to application. These are the kinds of narrow, well-defined problems robots (i.e. software) are really good at solving. So that ultimately begs the question…
If there are tools, like ORMs, that make data access easier without compromises, then why would anyone (you!) continue to do data access by hand? I have some theories. I think there are some powerful myths surrounding ORMs that prevent hard working developers from realizing ORM benefits:
  1. MYTH #1: ORMs are difficult to configure, Learning curves are too steep
    A common myth surrounding ORM is that in the time it would take to configure and learn the ORM system, you can finish creating the data access you need by hand with time to spare. This myth earns its reputation honestly. In the early days of ORM, there were few options for .NET, and those that did exist, like NHibernate, did require some pretty gnarly XML configurating. But that was then.

    Today, .NET developers have a host of ORM options, some of which can have you up and running in minutes. Take LinqToSql or OpenAccess ORM. Both provide simple visual designers that let you easily configure an ORM data context in seconds. OpenAccess even provides wizards on top of the designer to further streamline the setup process. Entity Framework, while more complex, also offers a rich visual designer.

    CONCLUSION: Myth busted. The ORMs of today are easy to configure. If you're avoiding ORM because you remember the XML config days, it's time to look again.

  2. MYTH #2: I can't give-up Stored Procedures, and ORMs don't use Stored Procs
    I'll save the religious debate about the necessity of stored procedures for another day, but any ORM worth its salt today offers support for Stored Procedures. Some offer better stored proc support than others, but with a good ORM, you can "hydrate" (that's an ORM term that means initialize your model objects with data from your database) objects using stored procs and forego completely ORM generated SQL.

    CONCLUSION: Myth busted. Stored Procs are not an excuse for avoiding ORM.

  3. MYTH #3: It's difficult to keep the database and ORM in sync.
    A great myth, and true for simple ORMs like LinqToSql, but absolutely false for ORMs that provide powerful Visual Studio tools. Thanks to improved framework features, like partial classes, and improved tooling, like OpenAccess' wizards, the process of merging database schema changes in to your mapped ORM classes is no longer a frustrating "blow all your customizations away" event. In fact, tools like OpenAccess are powerful enough to go the other direction and merge changes in your classes back in to your database schema! How's that for developer power?

    CONCLUSION: Myth busted. It's not hard with modern tools and framework features to keep database and ORM code in-sync.

  4. MYTH #4: I can't optimize an ORM. I'm stuck with its data access decisions.
    This is true for many ORMs, but with powerful ORMs like Telerik's OpenAccess, even this is a myth for the past. Using simple configuration steps, you can give ORMs "hints" that optimize query plans in your application. Generating hundreds of SELECTs when loading a collection of objects? No problem. Specify a fetch plan and those hundreds of statements can be condensed in to one or two powerful SQL statements.

    CONCLUSION: Myth busted. True, some ORMs leave you hanging when it comes to optimization (like LinqToSql). But powerful, full-featured ORMs like OpenAccess even make optimization a simple process.

  5. MYTH #5: I don't trust the SQL that an ORM produces.
    The big one! I'd wager this is one of the TOP reasons developers like you avoid ORMs. You don't trust them. You don't trust the SQL they generate and you don't feel like you have a good idea how those simple design-time LINQ queries get expanded to "real SQL" at runtime. Sure, you can fire-up SQL Profiler (or the free AnjLab SQL Profiler Express) and try to see what's coming-out of your app at runtime, but that's inconvenient and can be difficult to analyze. I'd say this is the one myth that continues to trouble today's ORMs. Yes, there are commercial tools that can help with profiling, but out of the box, ORM is largely a "black box."

    Plausible. This myth presents a challenge that needs a solution. If you're going to stop doing data access by hand, you need to be able to peer-in to the black box and form a trust with your ORM.


If lack of SQL transparency- which I think leads to a lack of trust- is one of the biggest barriers to ORM adoption, then it's the problem that should be solved. You should not waste days of your development life writing unnecessary data access code simply because it's too hard to peer-in to the ORM black box and form an understanding of what's happening. What do we need? We need an easy way to see what our ORM is doing. A tool that will help us easily find ways to optimize ORM behavior and establish a close working relationship so that you can focus on application development, and the ORM can focus on moving data.

In Part 2, I'll introduce a simple tool that I've crafted that will help you do exactly that for OpenAccess ORM. In the mean time, let's all agree to stop wasting time, get past our 1999 data access patterns, dispel the myths, and give ORM a try. And since OpenAccess ORM is FREE, why not start today?!


Anonymous said...

Great article, but... your ORM designer sucks :S It takes 2 minutes to save diagram on powerful PC :S
Cant switch database to another vendor without recreating model :S Why do I need ORM then ? Got EF for SQL ?

Todd Anglin said...

@Anon- Sorry to hear you've had trouble. In most cases, the designer works well, but we know it can be better. I think you'll be very pleased with what's coming in our Q1 release. There are some very impressive designer updates on the way. Stay tuned...

Unknown said...

Good article Todd, but honestly ORMs, in my opinion, need to be used in the right project or you might shoot yourself in the foot. I have a *lot* of experience with OpenAcces, and it's pretty good. However, I found that the majority of the time saved to handle the basic CRUD operations were easily made up by fighting with the ORM, and trying to optimize the painfully slow performance on a lot of the queries (even after fetch strategies). Moreover, the ORM really does eat resources.. there is a lot going on there, and man do you notice the increased bloat.

In the end, after about a year or so of using OpenAccess, we've decided to move back to good old POCO and datareaders/stored-procedures. Yeah, yeah, yeah, go ahead and cringe all you want but you just can't beat the performance. Our CMS runs incredibly fast now, and it's a lot easier to maintain.

I am sorry if this is perceived as a negative post against Telerik/OpenAccess, because I am still a very happy Telerik customer. I am just trying to point out that ORMs are *not* for everyone, especially those who are very knowledgeable when it comes to SQL and demand top performance from their data access.

Ed Charbeneau said...

You couldn't be more wrong. You can swap databases with out rebuilding your models. In fact, you can do so at runtime with about 4 lines of code.

By the way, saving a diagram isn't quite instantaneous, but I haven't had a 2 minute save either.

csharptest said...

I think you hit the nail on the head. The issue at the heart of it is trust. Trust in the ORM to do the right thing, efficiently, and consistently.

Though I can admit that an ORM can solve a need. I don't doubt that it can serve me well today. I do have serious doubts about 3 or 4 releases from now.

The problem as I see it: If you pay ridiculously money for it you can expect that it is a complete and mature product that will not change substantially and therefor provide stability over time. Or you can go the cheep route (open source or not) and you get what you pay for... Breaking interface changes, subtle behavior changes, bugs and the like.

Frankly Telerik among others have bit me too many times to trust something so key to my product's success. IMO Telerik has a disregard for backward compatibility both at the interface contract level as well as semantically. By no means am I trying to pick on Telerik though. This is true pretty much across the board in the .NET space as far as I'm concerned. It's even fairly common in the CLR, maybe we can just blame Microsoft and move on.

At any rate, IF I where to consider an ORM it would only be because it's used 100% at code-generation time and plays no part in the runtime of my application outside of the generated code. This 'generator only' approach is perfect for this problem IMO and is likely the cause of so many avoiding the reflection/configuration heavy libraries that support ORM.

In closing, who really cares anyway. Go Mongo, Go CouchDB, Go away RDBMS! Ok I'm kidding... a little. :)

rtpHarry said...

Interesting article, I agree with it all. Title made me think it was an argument -against- using an ORM though :)

Anonymous said...

I agree with the premises of myths 2-5 being busted.

I don't accept your conclusion that what you list as Myth #1 is as a myth. ORMs are incredibly complex due to the level of abstraction they provide. Many also require very elaborate Unit of Work patterns be adopted, past that tuning performance inside of ORMs is very nontrivial.

Brian M. said...

@Sharbel, your points still doesn't take away from ORM.

1. Performance is relative. Your argument of 'slow' doesn't necessitate that it had to be the ORM. If it were, one way you could alleviate the performance issue is to use asynchronous code. Even with POCO + DataReaders/Stored Procs, you'll still run into the issue of performance when you begin to hit the limits (in ASP.NET, those threads are far and few).

2. ORM's are good because it provides a layer of abstraction. As a developer, ORM's make logical sense and you structure your object model according to how you want to interact with the objects.

3. Like all things, ORM does force you to rethink your "queries" in that you should be mindful of how you perform your CRUD operations. One of my biggest mistakes in using EF 4.0 is not paying attention to how I was doing things. I ended up refactoring code for performance but once I did, I saw notable improvements.