Speeding Up Entity Framework 4.2 POCO

Question

I am using the Entity Framework 4.2 and I have a fairly serious performance issue. I am using the POCO approach, inheriting from DbContext and here is a small sample that explains the problem:

I have a database that has 2 tables - A and B:

A

• AId (int - not null - identity - primary key)
• Name (nvarchar(50) - not null)

B

• BId (int - not null - identity - primary key)
• SomeValue (int - not null)
• AId (int - not null - foreign key connecting to AId in the table A)

A has a single row in it (1, 'Test') and B has 6000 rows (SomeValue is just a number from 0 to 5999) - all of which reference the A row via the foreign key column.

I create an edmx from the database and turn off code generation. I then create the following classes:

public class DatabaseContext : DbContext
{
    public DatabaseContext(string name) : base(name)
    {
        Configuration.AutoDetectChangesEnabled = false;
        As = Set<A>();
        Bs = Set<B>();
    }

    public DbSet<A> As { get; private set; }
    public DbSet<B> Bs { get; private set; }
}

public class A
{
    public virtual int AId { get; set; }
    public virtual string Name { get; set; }
    public virtual ICollection<B> Bs { get; private set; }

    public void AddB(B b)
    {
        if (b == null)
        {
            throw new ArgumentNullException("b");
        }

        if (Bs == null)
        {
            Bs = new List<B>();
        }

        if (!Bs.Contains(b))
        {
            Bs.Add(b);
        }

        b.A = this;
    }
}

public class B
{
    public virtual int BId { get; set; }
    public virtual A A { get; set; }
    public virtual int SomeValue { get; set; }
}

Now I simply do the following:

var ctx = new DatabaseContext("ScalabilityTestEntities");
var a = ctx.As.FirstOrDefault();
a.Bs.Add(new B { SomeValue = 987 });

The last line (where I add a new B) takes something in the region of 6 seconds on my quad core, 4gb RAM 64 bit Windows 7 machine that has the database running locally.

The really bad thing is that it seems to degrade something like exponentially since if you double the number of rows in B, it takes nearer to 20 seconds!

I would really appreciate any tips to make this happen faster. Thanks very much!

Answer 1

The world of navigation properties can be a painful one. We essentially had to phase out their use because they cause so many performance problems behind your back (especially when you get into attaching and detaching entities, but that's a different story).

What's happening is that when you access a.Bs it loads all of the B's for that A.

In this specific case, if you don't actually need the full list of B's and you just want to add a new one, it's better to simply create a B and set its AId to a's ID.