Tag Archives: entity framework

ORMs and Performance

In one of my projects, actually Proactimas project, I’m using Entity Framework (EF) on top of SQL Azure and it works pretty well; data access is relatively fast, dead easy most of the time and the cost (in $) is OK. However I have discovered how sensitive EF is to how you write your queries. It appears to be especially true if you have several one-to-many relationships.

Just a quick introduction to Proactima Compliance; Any user has access to one or more companies, each company consists of one or more units. Units can be things like ‘Head Office’, IT-department, a ship etc. For every unit there can be one or more evaluations and each evaluation is based on a set of rules that the company measure compliance against. An example of a ruleset is the Petroleum Activities Act in Norway.

In Proactima Compliance the most frequently accessed page had an action that took about 1000ms to complete. This page allows the user to evaluate a rule and then choose ‘Save and Next’; so there are two actions involved:

  1. Save current rule and find next based on previous rules’ sequence
  2. Load next rule and display it to the user

The second action completes in 200-400ms, but the first takes from 800-1200 ms to complete. The main reason for the long processing time is what MiniProfiler calls Duplicate Readers. Basically, because of the way I had written my queries it was executing a reader querying for each rule in a ruleset! It looked like this:

Pre_Optimize

It’s running 103 (!) queries just for that one action, that’s terrible! The actual query looked like this:

var rules = from r in evaluationRule.Evaluation.Rules
                 where r.Rule.Sequence > currentSequence 
                 orderby r.Rule.Sequence 
                 select r;

The variable ‘evaluationRule’ was already fetched from the database and so I was just navigating to its Evaluation (remember how an evaluation is based on a ruleset which in turn consists of individual rules) and then the ruleset for that evaluation. Then I set up the where clause to make sure we can only get a rule with a sequence higher than the current rule. Finally I do an order by and select, from this I will only choose the first rule thus getting the next rule based on sequence. Possible bug here; sequences could potentially be same, but I’m 100% in control of the sequences so that’s really not an issue.

I won’t show the rendered query from this, it’s a nightmare, but the end result is correct and dead slow… So I had to fix this! I tried to manipulate the query in a try-fail-retry pattern; no success at all! I figured after awhile that I was addressing the issue from the wrong end; I was just thinking of the code. Thinking back on previous issues I’ve had with generated Sql (from other ORMs) I remembered (finally!) an old lesson; start by writing the query manually in the best possible way you can and then try to express that same query using the ORM! So that’s what I did! I fired up Sql Server Management Studio Express 2012 Deluxe Gold Plated Edition, or whatever it’s called and thought really hard about what I really needed. What I ended up with was this:

DECLARE @currentSequence int = 1;
DECLARE @currentEvaluation int = 1;

select	top 1 er.ID, 
	er.RuleID,
	r.Sequence,
	er.EvaluationID
from	EvaluationRules er,
	Rules r
where	er.RuleID = r.ID
and	er.EvaluationID = @currentEvaluation
and	r.Sequence > @currentSequence
order by r.Sequence;

Not that hard to write at all. Now all I needed was to convert it to EF code:

var rules =
    (from er in Database.EvaluationRules
        join r in Database.Rules on er.RuleID equals r.ID
        where er.EvaluationID == evaluationId
        && r.Sequence > currentSequence
        orderby r.Sequence
        select er)
        .Take(1);

It’s definitely a bit more than my original code based query, but as you can see it looks a whole lot like the manual sql query. And how does MiniProfiler now report on my page:

Post_Optimize
Please ignore the minor differences (controller name); I have done some other refactoring’s as well as the query.

What an amazing change! 900ms to 222ms! And 100+ queries to just 7! That’s performance optimizations the user notices!

But why did it all go so terribly wrong in the first place? I’m not going to over analyze the two queries or discuss how EF generates the Sql; somebody much smarter than me can probably do that (or has!). But what I learned from all this is that my old skills as Sql developer isn’t lost even if I am using an ORM AND the way you express a query can matter a whole lot when it comes to performance!

AutoMapper, aka AutoMagic

 

Update 27.10.2011: It turns out that the implementation below is not really all that good. The map created with the custom resolver will be cached; so all future results from a mapping process will use the initial value (enityTwoId). I reverted to ignoring the property during the mapping and then setting it manually afterwards.

We are currently working on a re-write of an application here at Proactima and in that process I’ve decided to use EF 4.1 Magic Unicorn Edtion as my backend. It supports POCOs, but sometimes I don’t really wanna send the whole object to the view; so I create some view specific classes to use for this purpose. Mapping between the entity class and the view class would be tedious work was it not for the magic of AutoMapper! It handles the RHS-LHS mapping for me! How cool! But sometimes even magic needs a helping hand and AutoMapper accepts it!

These are my entity classes (not the actual classes…):

public class EntityOne
{
    public int EntityOneID { get; set; }
    public string Title { get; set; }
    public virtual ICollection<EntityTwo> EntityTwos { get; set; }
}

public class EntityTwo
{
    public int EntityTwoID { get; set; }
    public string SomeInformation { get; set; }
    public int EntityOneID { get; set; }
    public virtual EntityOne EntityOne { get; set; }
}

And these should be mapped to these classes:

public class EntityOneView
{
    public int EntityOneID { get; set; }
    public string Title { get; set; }
    public virtual EntityTwoView EntityTwo { get; set; }
}

public class EntityTwoView
{
    public int EntityTwoID { get; set; }
    public string SomeInformation { get; set; }  
}

Basically I only bring with me one of the “EntityTwoView” objects, as opposed to a list in the entity class. Naturally in an actual application the classes would be FAR more complex than these, but I needed this to illustrate the point. I find it easier to understand code where there is little or no noise.

Given an “EntityOne” object I now want to map/convert it to an “EntityOneView” object before sending it of the the view (from the controller in ASP.Net MVC). If I didn’t have to list->one issue I could simply write these two lines:

EntityOne one = db.Load<EntityOne>(1);
Mapper.CreateMap<EntityOne, EntityOneView>();
EntityOneView oneView = Mapper.Map<EntityOneView>(one);

But this will fail since AutoMapper have no idea on how to go from a list of EntityTwo’s to a single EntityTwo. So I have to help her (?) out a bit. I can do that by writing a Custom Value Resolver. It would for this code look like this:

public class SingleLineResolver : ValueResolver<EntityOne, EntityTwoView>
{
    private int EntityTwoId { get; set; }
    public SingleLineResolver(int entityTwoId)
    {
        this.EntityTwoId = entityTwoId;
    }

    protected override EntityTwoView ResolveCore(EntityOne source)
    {
        Mapper.CreateMap<EntityTwo, EntityTwoView>();
        var line = source.EntityTwos.First(e=> e.EntityTwoID == this.EntityTwoId);
        return Mapper.Map<EntityTwoView>(line);
    }
}

This code took me awhile to understand and be able to write… Now I might be stupid and you smart, so perhaps I’m the only one struggeling with this one… But to make use of it I had to update the previous code to:

EntityOne one = new EntityOne();
SingleLineResolver resolver = new SingleLineResolver(2);
Mapper.CreateMap<EntityOne, EntityOneView>()
    .ForMember(dest => dest.EntityTwo, opt => opt.ResolveUsing(resolver));
EntityOneView oneView = Mapper.Map<EntityOneView>(one);

Here I am basically saying that when you (AutoMapper) create the mapping between EntityOne and EntityOneView and come to the “EntityTwo” property (on the EntityOneView object) please use my custom value resolver (resolver) to figure out the value of that property. It will then send the “one” object into my custom value resolver class (SingleLineResolver) and the output will be set as the value of the property called “EntityTwo” on the new object of type “EntityOneView”; Puh!

Basically AutoMapper let’s me use two objects (or more!) that are fairly similar and map between them. She (?) will handle complex scenarios like this and much, much more. I hereby dub the project “AutoMagic”.

Please note that the code I present here has been severly altered from the actual production code, so please don’t shoot me if it fails to work as expected!