Why are these Add/.Modify operations in Entity Framework so slow?

Question

I've just been noodling about with a profiler looking at performance bottlenecks in a WCF application after some users complained of slowness.

To my surprise, almost all the problems came down to Entity Framework operations. We use a repository pattern and most of the "Add/Modify" code looks very much like this:

public void Thing_Add(Thing thing)
{
    Log.Trace("Thing_Add called with ThingID " + thing.ThingID);

    if (db.Things.Any(m => m.ThingID == thing.ThingID))
    {
        db.Entry(thing).State = System.Data.EntityState.Modified;
    }
    else
    {
        db.Things.Add(thing);
    }
}

This is obviously a convenient way to wrap an add/update check into a single function.

Now, I'm aware that EF isn't the most efficient thing when it comes to doing inserts and updates. However, my understanding was (which a little research bears out) that it should be capable of processing a few hundred records faster than a user would likely notice.

But this is causing big bottlenecks on small upserts. For example, in one case it takes six seconds to process about fifty records. That's a particularly bad example but there seem to be instances all over this application where small EF upserts are taking upwards of a second or two. Certainly enough to annoy a user.

We're using Entity Framework 5 with a Database First model. The profiler says it's not the Log.Trace that's causing the issue. What could be causing this, and how can I investigate and fix the issue?

6 seconds in what environment? Debug? Release? Running from binary? I ask because I've noticed that EF runs extraordinarily slow in debug, but flies in the compiled binary. — Graham Bass, Jul 24 '15 at 16:25
@GrahamBass That's in release. It's just as slow running in debug mode. — Bob Tway, Jul 24 '15 at 16:28
I don't know if this is helpful, but if "thing" is already attached, you don't need to set the state to modified. So if you are calling this code on things which aren't actually changed you are wasting time. you can check if the state is Detached before setting the state to modifed. — Robert Noack, Jul 24 '15 at 16:52
Also, are you calling SaveChanges() after each call to this method? or just a single SaveChanges() if you call it once the results will be much better. Also (maybe a dumb question) but is there an index/primary key on the ThingId field in the db? — Robert Noack, Jul 24 '15 at 16:53
Do you have varchar columns? One possible problem I have encountered several times is if there is a varchar column in the where clause entity framework will paramaterize as nvarchar by default causing explicit type conversion of the column to nvarchar and the indexes to be ignored. http://stackoverflow.com/questions/15767803/entity-framework-query-slow-but-same-sql-in-sqlquery-is-fast I would sugest running sql profiler to see whats really happening at the database level. — Nathan Smith, Jul 24 '15 at 17:11
@RobertNoack Depends on the circumstances. Mostly, we don't call SaveChanges() until we're finished fiddling with the objects, although there are exceptions. But the profiler is saying the slow code is in the add/update code posted, not in the savechanges method. — Bob Tway, Jul 27 '15 at 09:48
@NathanSmith I find trying to unpick EF calls with SQL profiler a real headache. In this instance, I'm seeing a surprisingly large number of select statements being processed, which don't seem to be at all necessary. Especially if the slow performance is in the add/update process, as the .NET profiler suggests. — Bob Tway, Jul 27 '15 at 09:50

score 3 · Answer 1 · edited May 23 '17 at 11:51

I found the root of the problem on another SO post: DbContext is very slow when adding and deleting

Turns out that when you're working with a large number of objects, especially in a loop, the gradual accumulation of change tracking makes EF get slower and slower.

Refreshing the DbContext isn't enough in this instance as we're still working with too many linked entities. So I put this inside the repository:

public void AutoDetectChangesEnabled(bool detectChanges)
{
    db.Configuration.AutoDetectChangesEnabled = detectChanges;
}

And can now use it to turn AutoDetectChangesEnabled on and off before doing looped inserts:

try
{
    rep.AutoDetectChangesEnabled(false);
    foreach (var thing in thingsInFile)
    {
        rep.Thing_add(new Thing(thing));
    }
}
finally
{
    rep.AutoDetectChangesEnabled(true);
}

This makes a hell of a difference. Although it needs to used with care, since it'll stop EF from recognizing potential updates to changed objects.

Why are these Add/.Modify operations in Entity Framework so slow?

1 Answers1