Home » Programming » ORM Performance – Have you gone ORM crazy?

ORM Performance – Have you gone ORM crazy?

April 10th, 2010 Leave a comment Go to comments

I gotta say, every framework I look at these days, there seems to be an ORM built-in.  For example in .NET alone (which is a single framework) there’s already LinqToSql and LinqToEntities and then on top of that there’s NHibernate, SubSonic and so on and so forth.  In PHP, every framework I look at seems to have an ORM… They don’t have half the other things that a decent PHP framework actually needs, but they have an ORM.

Is it really all that hard or labourous to write SQL?  Why do you need to learn effectively another language/api for ultimately creating SQL queries when you already know SQL?  Why do you want to destroy the performance of your application?

As crazy as it seams, that’s what seems to be happening.  It’s like people jump on the bandwagon without thinking.  If you read about LinqToSql and LinqToEntities you will find plenty of blog posts saying that performance is terrible and if you want to pre-compile the code needs to be WAY more complex.  Moreover at the moment LinqToEntities generates absolutely shocking SQL for anything but the basic queries.   The same goes for PHP frameworks… ORM takes heaps of code and processing and all for what?  To generate a basic SQL string that you could have written by hand?

Anyways, I guess anyone writing anything moderately serious doesn’t use ORM, but people really need to step back and think about things like performance before they jump on the bandwagon and start churning out SQL over which they have no control using ORM tools.

Categories: Programming Tags:
  1. Sheffield
    May 12th, 2010 at 05:01 | #1

    I tend to agree with you. I have a situation where I need to call a stored procedure that inserts records into a table based in grouping from another table. I guess this could be done by the OR/M framework but the problem is that the stored procedure doesn’t map exactly to the model object. Entity Framework can map Stored Procedures if the procedures are veneers over the CRUD. But as in my case that is not always the case and it makes sense to have this as a stored procedure.

    I’ve spend HOURS trying to figure out how to do this within the CONSTRAINTS of the framework. This is what is so frustrating about frameworks because if you need to do something outside of its purview your stuck. I could do this with Linq-to-sql because it does mapped stored procs nicely but not I have my models using EF and the stored proc with Linq-to-sql meaning that I’m supporting TWO disparate payloads. This makes no sense.

    I’ve been programming since the MFC days and learn to despise frameworks. What I want are thin layers that do not get in my way and I can handle the rest. Reusable Source code examples are IMO much more useful than frameworks that are convoluted and in many cases impenetrable.

    If you have any suggestion for useful SQL coding tools I’d greatly appreciate it.

  2. July 1st, 2010 at 08:49 | #2

    The whole reason you use an ORM is for the productivity and maintainability enhancements, not performance. Honestly, just buying a slightly faster CPU or more RAM is cheap compared to the costs of hiring expensive top tier developers.

    “Is it really all that hard or labourous to write SQL?”

    If you’re building a large enterprise application with potentially 1000’s of SQL strings strewn about the place then YES!

    An ORM lets you take advantage of the power of your Object Oriented Programming language to define rich domain models, that communicate your business domain more clearly than what you can do with just having a bunch of SQLCommand’s and SqlConnections and SqlDataReaders all over the place. You might want to read up on something called Domain Driven Design (http://en.wikipedia.org/wiki/Domain-driven_design), which is essential what I’m describing.

    Honestly, I’d rather write:

    Category category = CategoryRepository.FromId(categoryId);
    IList products = category.Products;

    …than trying to write the equivalent in RAW ADO.NET/SQL which would probably be dozens of lines of code long, and I bet the performance difference could barely be measured in milliseconds, a performance degradation that could easily be recouped by spending an extra £100 on a slightly faster CPU vs. spending potential thousands on extra contractors to maintain your 1000’s line of ADO.NET/SQL code all over your codebase.

    As for ‘Sheffield’s’ comments, yes some have constraints but some are very flexible, for instance Linq 2 SQL and NHibernate let you map actual Stored Procedures if you need to perform a complex query that can’t be done by the ORM, but 90% of the time I’m writing relatively simple queries that can.

  3. July 1st, 2010 at 09:10 | #3

    The problem with ORM is that it can really hurt performance and you’re not always exactly sure what SQL it’s generating. You should have a read of the horror SQL that was getting generated in Linq-to-Entities in .NET Framework 3.5 SP1. I’m hoping this has been significantly improved and fixed in .NET Framework 4.0 but havn’t played around with it as yet. Also, any good enterprise developer should really know SQL so writing SQL isn’t a chore. You should also have a read of the horror stories about performance especially on the first call and the complexity involved in writing Linq-to-Entities or Linq-to-SQL queries that pre-compile. If you want performance than I’d say first you’re best off to put all your queries (be they using ORM or SQL from code or Stored Procedures) into a dedicated data access layer and then if you actually want good performance then go for SQL or Stored Procedures.

  4. drupality
    July 29th, 2011 at 13:46 | #4

    ORM is evil for big projects. All in this subject

  5. April 4th, 2012 at 20:56 | #5

    I agree, ORM is ridiculous and I can’t wait until the masses figure out that stored procs are way-better.

  1. No trackbacks yet.

4 − one =