StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>To your remark in the comments to your question:</p> <blockquote> <p>"...SavingChanges (<strong>for each record</strong>)..."</p> </blockquote> <p>That's the worst thing you can do! Calling <code>SaveChanges()</code> for each record slows bulk inserts extremely down. I would do a few simple tests which will very likely improve the performance:</p> <ul> <li>Call <code>SaveChanges()</code> once after ALL records.</li> <li>Call <code>SaveChanges()</code> after for example 100 records.</li> <li>Call <code>SaveChanges()</code> after for example 100 records and dispose the context and create a new one.</li> <li>Disable change detection</li> </ul> <p>For bulk inserts I am working and experimenting with a pattern like this:</p> <pre><code>using (TransactionScope scope = new TransactionScope()) { MyDbContext context = null; try { context = new MyDbContext(); context.Configuration.AutoDetectChangesEnabled = false; int count = 0; foreach (var entityToInsert in someCollectionOfEntitiesToInsert) { ++count; context = AddToContext(context, entityToInsert, count, 100, true); } context.SaveChanges(); } finally { if (context != null) context.Dispose(); } scope.Complete(); } private MyDbContext AddToContext(MyDbContext context, Entity entity, int count, int commitCount, bool recreateContext) { context.Set<Entity>().Add(entity); if (count % commitCount == 0) { context.SaveChanges(); if (recreateContext) { context.Dispose(); context = new MyDbContext(); context.Configuration.AutoDetectChangesEnabled = false; } } return context; } </code></pre> <p>I have a test program which inserts 560.000 entities (9 scalar properties, no navigation properties) into the DB. With this code it works in less than 3 minutes.</p> <p>For the performance it is important to call <code>SaveChanges()</code> after "many" records ("many" around 100 or 1000). It also improves the performance to dispose the context after SaveChanges and create a new one. This clears the context from all entites, <code>SaveChanges</code> doesn't do that, the entities are still attached to the context in state <code>Unchanged</code>. It is the growing size of attached entities in the context what slows down the insertion step by step. So, it is helpful to clear it after some time.</p> <p>Here are a few measurements for my 560.000 entities:</p> <ul> <li>commitCount = 1, recreateContext = false: <strong>many hours</strong> (That's your current procedure)</li> <li>commitCount = 100, recreateContext = false: <strong>more than 20 minutes</strong></li> <li>commitCount = 1000, recreateContext = false: <strong>242 sec</strong></li> <li>commitCount = 10000, recreateContext = false: <strong>202 sec</strong></li> <li>commitCount = 100000, recreateContext = false: <strong>199 sec</strong></li> <li>commitCount = 1000000, recreateContext = false: <strong>out of memory exception</strong></li> <li>commitCount = 1, recreateContext = true: <strong>more than 10 minutes</strong></li> <li>commitCount = 10, recreateContext = true: <strong>241 sec</strong></li> <li>commitCount = 100, recreateContext = true: <strong>164 sec</strong></li> <li>commitCount = 1000, recreateContext = true: <strong>191 sec</strong></li> </ul> <p>The behaviour in the first test above is that the performance is very non-linear and decreases extremely over time. ("Many hours" is an estimation, I never finished this test, I stopped at 50.000 entities after 20 minutes.) This non-linear behaviour is not so significant in all other tests.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload