title: Why is my EntityFramework insert so slow?
body: <p>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:</p>
  
  <pre><code>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&lt;AnalogState&gt; AnalogStates { get; set; }
      public virtual IList&lt;DigitalState&gt; 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; }
  }
  </code></pre>
  
  <p>The AnalogState and DigitalState classes use the TrackerStateId and their Index as a composite primary key.</p>
  
  <p>The tables are currently very small:</p>
  
  <ul>
  <li>TrackerStates: 2719</li>
  <li>AnalogStates: 0</li>
  <li>DigitalStates: 32604</li>
  </ul>
  
  <p>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.</p>
  
  <pre><code>public async Task&lt;int&gt; AddAsync(TrackerState trackerState)
  {
      using (var context = ContextFactory.CreateContext())
      {
          context.TrackerStates.Add(trackerState);
          await context.SaveChangesAsync();
          return trackerState.Id;
      }
  }
  </code></pre>
  
  <p>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:</p>
  
  <ul>
  <li>Disabling change tracking. I didn't expect this to do much, as I am using a separate context for each transaction anyway.</li>
  <li>Inserting the tracker state first, then the digital states in a separate step. Entity Framework is probably doing this internally anyway.</li>
  </ul>
  
  <p><strong>Update 1</strong></p>
  
  <p>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:</p>
  
  <pre><code>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));
  </code></pre>
  
  <p>This part alone is accounting for the majority of the time. It takes 3 seconds to insert 7 entries.</p>