CODEWITHSUNDEEP
c#sqliteentity-framework-6unit-of-work
tronc
tronc

Reputation: 693

Speed up model instantiation for EF6 / sqlite db

I have a large collection of 12000 data entries for example and want to insert them via EF6 into a sqlite database. The most time consumes the instantiation of the data models:

at the moment I loop 12000 times 'new myItem()'

downloaded12000Items.foreach(result =>{
    var myItem= new myItem
    {
        Id = result.Id,
        Description = result.Description,
        Property1 = result.Property1
    }
    resultList.add(myItem);
});

unitOfWork.ItemRepository.InsertRange(resultList);

How can I speed up the instantiation of the models or is there maybe another way to insert the data faster into the sqlite database?

EDIT: I have to explain my problem better. The bottleneck is NOT the insert() into the database. To use EF6 .insert(someModel) you have to create an instance of a modelclass of your entity. I have to do this 12000 times, the instantiation of all the 12000 modelclasses takes too much time.

My question was, is there a possibility to fasten up the instatiation process of the model classes, maybe by cloning or something else?

Or, is there maybe a chance to insert the data into the sqlite db without using .insert(someModel), maybe by using a direct sql command or something else? Obviously skipping the model instantiation could be helpful...

Upvotes: 0

Views: 437

Answers (2)

user585968
user585968

Reputation:

How can I speed up the instantiation of the models or is there maybe another way to insert the data faster into the sqlite database?

Use the equivalent of await Context.SaveChangesAsync() in your repo after you have finished looping and inserting "12000 data entries" . Tell me more

Note it is no longer necessary to perform the following in order to improve performance:

context.Configuration.AutoDetectChangesEnabled = false;  // out of date
context.Configuration.ValidateOnSaveEnabled = false; // out of date

...such code has its own drawbacks but more importantly it is based on out-of-date philosophy and does not take advantage of await in EF.

Here's a snippet of production code that I use to save an requirement realisation matrix:

// create your objects
var matrix = // in my prod code I create in excess of 32,600+ matrix cells
foreach (var cell in cellsToAdd)
{
    matrix.Cells.Add(cell);
}

using (var context = new MyDbContext())
{
    context.Matrices.Add (newMatrix);

    await context.SaveChangesAsync();
}

I find this works perfectly well when I insert 32,646 matrix cells in my production environment. Simply using await and SaveChangesAsync() improved performance 12 times. Other strategies, like batching were not as effective and disabling options such as AutoDetectChangesEnabled though somewhat useful, arguably defeat the purpose of using an ORM.

Upvotes: 0

Maarten
Maarten

Reputation: 22945

The bottleneck is probably the adding of the entities to the context.

unitOfWork.ItemRepository.Insert(myItem);

At first it doesn't take much time, but after 100s or 1000 records, it does.

See also this answer for other optimizations you might be able to add (read the comments of the linked answer!).

Upvotes: 1

Related Questions