Adding a large number of entities to the DbContext is slow

[maumar]

In my scenario I'm adding about 5000 entities, each has about 2-7 entities via 1-Many relationship (there is about 18000 of those "leaf" entities). If done all at once persisting it all takes 3 min 4 sec. If done in chunks of about 500-1000 it takes 47 sec. Large portion of time is spent on adding those parent entities to the context (and I guess walking the graph and fixing up the relationships).
For my scenario however this is not necessary - I only wish to persist those entities, I generate all the keys and fixup the relationships manually via foreign keys. Perhaps we should consider a way to disable all the change tracking, for scenarios similar to this?

[maumar]

Workaround could be to use two separate DbContexts, one where navigations would only be exposed as FKs, that would be used to persist the data, and then another, proper DbContext to do all the querying and other processing.

[cbmek]

Did you try to disable AutoDetectChangesEnabled?

_context.ChangeTracker.AutoDetectChangesEnabled = false;

It always helped me do a batch inserts in previous EF's (it was in _context.Configuration then).
I always do _context.SaveChanges() every 1k entities anyway, not to block DB with one big insert, but you may try to do it after 5k. Please share results :)

[bricelam]

Looks like this StackOverflow question is seeing similar results.

[maumar]

@cbmek I did some more investigation on this and long story short, AutoDetectChangesEnable = false doesn't make things much better. However breaking stuff into separate SaveChanges calls using separate contexts to do so improves the performance drastically.

I have the following entities:
Tab (about 300 of those in a batch), each Tab has 1-Many relationship with
Item (about 5000 of those in a batch), each Item has 1-Many relationship with
ItemMod (about 18-20k of those in a batch)

I saved 10 batches and took the average time it took.

Conducted the following scenarios:

• everything processed in one DbContext & SaveChanges, context has navigation properties and change tracking was switched on - AVG time = 01:06.6320406
• same as above but with change tracking switched off - AVG time = 01:03.9203228
• processing done in separate DbContexts, one for each entity type (saved tabs first, then items, then item mods), context has navigation properties - AVG time = 00:24.5785175
• processing done in separate DbContexts, saving 1000 entities per DbContext instance, context has navigation properties - AVG time = 00:20.7598355
• same as above but using a different DbContext that has no navigations defined (all was done using FKs only) - AVG time = 00:19.8548248

[rowanmiller]

I did some more investigation on this and long story short, AutoDetectChangesEnable = false doesn't make things much better.

This would be expected since we don't run this logic during each Add anymore