O/R Mapping and domain query optimizations


One of the cons of O/R mapping is that the abstraction is a bit too high.
You write object-oriented code and often forget about eventual performance problems.

Take this (somewhat naive) example:

class Customer
{
   ...
  public double GetOrderTotal()
   {
       var total = ( from order in this.Orders
                        from detail in order.Details
                        select detail.Quantity * detail.ItemPrice)
                       .Sum();

       return total;
   }
}

For a given customer, we iterate over all the orders and all the details in those orders and calculate the sum of quantity multiplied with itemprice.
So far so good.

This will work fine as long as you have all the data in memory and the dataset is not too large, so chances are that you will not notice any problems with this code in your unit tests.

But what happens if the data resides in the database and we have 1000 orders with 1000 details each?
Now we are in deep s##t, for this code to work, we need to materialize at least 1 (cust) + 1000 (orders) * 1000 (details) entities.
The DB needs to find those 1 000 001 rows , the network needs to push them from the DB server to the App server and the App server needs to materialize all of it.
Even worse, what if you have lazy load enabled and aren’t loading this data using eager load?
Then you will hit the DB 1 000 001 times… GL with that! :-)

So clearly, we can not do this in memory, neither with lazy load nor eager load.

But what are the alternatives?
Make an ad hoc sql query?
In that case, what happens to your unit tests?

Maybe we want to keep this code, but we want to execute it in the database instead.

This is possible if we stop beeing anal about “pure POCO” or “no infrastructure in your entities”

Using an unit of work container such as https://github.com/rogeralsing/Precio.Infrastructure

We can then rewrite the above code slightly:

class Customer
{
   ...
  public double GetOrderTotal()
   {
  var total = ( from customer in UoW.Query<Customer>() //query the current UoW
                        where customer.Id == this.Id //find the persistent record of "this"
                        from order in customer.Orders
                        from detail in order.Details
                        select detail.Quantity * detail.ItemPrice)
                       .Sum();

       return total;
   }
}

This code will run the query inside the DB if the current UoW is a persistent UoW.
If we use the same code in our unit tests and use an in mem UoW instance, this code will still work, if our customer is present in the in mem UoW that is..

So the above modification will reduce the number materialized entities from 1 000 001 to 1 (we materialize a double in this case)

I don’t know about you , but I’d rather clutter my domain logic slightly and get a million times better performance than stay true to POCO and suffer from a broken app.

7 thoughts on “O/R Mapping and domain query optimizations

  1. I disagree in those cases I would rather choose to not have the connection Customer.Orders and I would have my O/R Mapper to do the calculation in the database when the entity loads, or let a DomainService solve that issue for me which would do the calculation in the database for me.

  2. If you do the calculation on load, then the value can be out of sync with the actual objects if you do modifications.
    The above code would solve that if the mapper flush changes to the DB when you query (as NHibernate does)
    The same can be acheived in EF if the UoW does the flushing for you.

    (I do however agree that the case above is naive, customer would not be an AR over Orders and Details if you do DDD)

    If you do it in a domain service, you strive for anemic domain model.
    Logic that invloves one aggregate root, should live in that aggregate root.

  3. You are right if some changes has been made it is not the accurate.
    I think I as an developer want to control the comminucation with the db and not get an save and an search when calling a method on the domainservice, I would rather let an domainservice do that for me, or just reload the entity in these cases.

    In this case I mostly would see the anemic domain model over the domainmodel to interfere with the persistence. (If the list now is to big to load into memory)

    But I guess it is about taste..

  4. Correction “I think I as an developer want to control the comminucation with the db and not get an save and an search when calling a method on the domainservice”

    Should be
    “I think I as an developer want to control the comminucation with the db and not get an save and an search when calling a method on the domainmodel”

  5. Which actually should say:

    “I think that as a developer I want to control the comminucation with the db, and not get a save and a search when calling a method on the domain model”.

    (I just became a grammar police…. and a bad one at that too)

    Going back to the ORM problem. I have wrote several different ORMs within the last few years. Different clients want their own code so they feel that they own the secret sauce. I used to mark my classes with custom attributes. So I have an aggregate attribute that specify the child class to aggregate, the kind of aggregation, and it was attached to the property on the parent that will contain the result, the engine will insert the proper SQL when loading an object with this property. There is also the ability to refresh an entity.

    The problem expressed in the article is because it is under the philosophy that we need to manually process object by object. In other words, this is a store procedure written in C# using cursors… no better than a for loop in dBase. The power of modern database is in the fact that they can process sets for us.

  6. Pingback: DotNetKicks.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s