CODEWITHSUNDEEP
c#entity-framework
Andrew Williamson
Andrew Williamson

Reputation: 8691

Why is my EntityFramework insert so slow?

I've found a bottleneck in my application to be the insert operation for one particular entity (into three tables, through navigation properties). The classes are defined as follows:

public class TrackerState
{
    public int Id { get; set; }
    [Index]
    public int TrackerId { get; set; }

    [Index]
    public DateTime DateRecorded { get; set; }
    public DateTime DatePublished { get; set; }
    public DateTime DateReceived { get; set; }
    public LocationStatus LocationStatus { get; set; }
    public double Latitude { get; set; }
    public double Longitude { get; set; }
    public double Altitude { get; set; }
    public double Accuracy { get; set; }
    public string Source { get; set; }
    public double Speed { get; set; }
    public double Heading { get; set; }
    public int PrimaryOdometer { get; set; }
    public int SecondaryOdometer { get; set; }
    public int OperationalSeconds { get; set; }
    public virtual IList<AnalogState> AnalogStates { get; set; }
    public virtual IList<DigitalState> DigitalStates { get; set; }
}

public class AnalogState
{
    public int TrackerStateId { get; set; }
    public virtual TrackerState TrackerState { get; set; }
    public int Index { get; set; }
    public int Value { get; set; }
}

public class DigitalState
{
    public int TrackerStateId { get; set; }
    public virtual TrackerState TrackerState { get; set; }
    public int Index { get; set; }
    public bool Value { get; set; }
}

The AnalogState and DigitalState classes use the TrackerStateId and their Index as a composite primary key.

The tables are currently very small:

When I insert into the tables manually, through SQL management studio, the operation runs in a fraction of a second. When I insert through Entity Framework, using the following code, it can take up to 15 seconds, and the amount of time taken is very dependent on the number of digital values included in the tracker state - e.g. a tracker state with 0 digital values takes between 0.1 to 0.5 seconds, and a tracker state with 64 digital values takes between 10 and 15 seconds.

public async Task<int> AddAsync(TrackerState trackerState)
{
    using (var context = ContextFactory.CreateContext())
    {
        context.TrackerStates.Add(trackerState);
        await context.SaveChangesAsync();
        return trackerState.Id;
    }
}

Based on this, it seems like Entity Framework is doing something very slow in the background, but I can't figure out why. 0.5 seconds is pretty slow for a transaction, considering how often this is going to be done. 15 seconds is just too damn slow. Things I have tried so far, to no success:

Update 1

I'm using EntityFramework 6.1.3. I couldn't figure out how to view the SQL being executed, but I updated the repository's store method to use SQL instead of EF:

context.Database.ExecuteSqlCommand("INSERT INTO DigitalStates ([TrackerStateId], [Index], [Value]) VALUES (@Id, @Index, @Value)",
                    new SqlParameter("Id", entity.Id),
                    new SqlParameter("Index", digital.Index),
                    new SqlParameter("Value", digital.Value));

This part alone is accounting for the majority of the time. It takes 3 seconds to insert 7 entries.

Upvotes: 1

Views: 1012

Answers (1)

Andrew Williamson
Andrew Williamson

Reputation: 8691

Saving all the digital states in one transaction made a huge difference:

if (trackerState.DigitalStates.Count > 0)
{
    var query = "INSERT INTO DigitalStates ([TrackerStateId], [Index], [Value]) VALUES "
        + string.Join(",", trackerState.DigitalStates.Select(state => String.Format("({0}, {1}, {2})", entity.Id, state.Index, state.Value ? 1 : 0)));
    context.Database.ExecuteSqlCommand(query);
}

For some reason, letting Entity Framework add the collection automatically seemed to be making a request to the database for each digital state that was added, although I was under the impression that it should have been one transaction, triggered by the context's SaveChanges() method. This fix has changed it from linear time to approximately constant time, relative to the size of the collection. Now my next question is, why?

Upvotes: 1

Related Questions