title: Inserting a large number of rows into SQL CE 4.0 with Entity Framework 4 (performance issue)
body: <p>I have a small SQL CE 4.0 database with several tables, mapped using Entity Framework 4.</p>
  
  <p>Here is the code I have</p>
  
  <pre><code>foreach (String designation in newItemDesignations)
  {
      ParameterData defaultValue = PDM.GetDefaultParameterData(designation);
  
      // Fill the ItemParameterDBO object with the data
      ItemParameterDBO dbParam = new ItemParameterDBO();
      dbParam.ItemID = dbItem.ID;
      dbParam.Designation = designation;    
      dbParam.High = defaultValue.High;
      dbParam.Low = defaultValue.Low;
  
      database.ItemParameterDBOes.AddObject(dbParam);
  }
  
  database.SaveChanges();
  </code></pre>
  
  <p>This code happens <code>24 times</code> and each time the <code>newItemDesignations</code> list contains <strong><code>exactly 525 elements</code></strong>. That's a total of <strong>12600</strong> rows to add.</p>
  
  <p>The complete process lasts <strong><code>509 seconds</code></strong>. I guess that's too much for <code>12600</code> rows. </p>
  
  <p>I am aware that I am calling <code>SaveChanges 24 times</code>. At the moment, the application design does not allow me to put all inserts into a single transaction (with <code>SaveChanges</code>). However, take a look at what happens with the single transaction. <strong><code>509 / 24 = 21 seconds</code></strong>, or a <strong><code>40 ms per row</code></strong>. </p>
  
  <ul>
  <li>Is 40 ms the normal (avg) time for a row to be inserted via EF4?</li>
  </ul>
  
  <p><em>I've checked my other code (other than adding to the database and saving changes). It takes total of 100 ms for all 12600 rows. That's 0.01% of complete time, so that's obviously not the problem. The 99.99% of the processing time is spent in EF4 <code>AddObject</code> and <code>SaveChanges</code>.</em></p>
  
  <p>I am also aware that I am setting the ItemID property which is a foreign key. This is an integer so I guess it shouldn't matter much. But I wouldn't know.</p>
  
  <p>Also note: there are no indexes set on any of the tables (except for primary/foreign keys)</p>
  
  <ul>
  <li>What am I doing wrong here, why is this so slow?</li>
  <li>Is this the normal time needed for inserting that much rows or is this some kind of performance restrictions related to SQL CE 4?</li>
  </ul>