Speed up model instantiation for EF6 / sqlite db

Question

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...

EDIT: it's not the insert that is slow. it's the simple looped instatiation of the Model objects. The insert itself is pretty fast. — tronc, Aug 29 '16 at 12:20
Your last comment contradicts your last statement in your question — MickyD, Aug 29 '16 at 14:38
I think this is the bottleneck: 'unitOfWork.ItemRepository.Insert(myItem);' - since the context has to track all those 12k items. First it doesn't take much time, but after 100s or 1000 records, it does. — Maarten, Aug 29 '16 at 16:03
can you put whole code snippet where it relates to the `insert` (including loop) ? and also with your data models. — Sampath, Aug 30 '16 at 01:37
@Maarten you were absolutely right... the tracking of the models took so long. please answer the question, i will mark it as answer to my problem. I thought the instatiation of the model took so long, but it was the looped context.insert(oneSingleModel). i've changed it to InsertRange(allModels) and it runs very much faster now. thanks! — tronc, Aug 30 '16 at 08:17

score 1 · Accepted Answer · edited May 23 '17 at 12:18

1

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!).

edited May 23 '17 at 12:18

Community

1
1

answered Aug 30 '16 at 08:20

Maarten

21,254
3
44
62

score 0 · Answer 2 · edited May 23 '17 at 10:29

0

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.

edited May 23 '17 at 10:29

Community

1
1

answered Aug 29 '16 at 10:03

MickyD

13,463
6
39
60

Why would that be quicker to insert the data into the sqlite database? – Maarten Aug 29 '16 at 10:05
As it will communicate one single "sql" statement. Otherwise 11000 single requests will be made. – AntonR Aug 29 '16 at 10:14
@Maarten http://stackoverflow.com/a/30043327/585968. In my experience it made it 12 times faster – MickyD Aug 29 '16 at 11:08
There is nothing to suggest in the question that the OP uses either a single `.SaveChanges()` or 11000 `.SaveChanges()`'s. The OP says nothing about how (s)he is doing the actual committing of data. – Maarten Aug 29 '16 at 11:25
@Maarten so? I've had the same problem before and it **was** caused by 10,000 plus inserts with a **single** SaveChanges(). Fix was a single SaveChangesAsync(). Besides, read the link – MickyD Aug 29 '16 at 14:36
So you would rather have 10k tasks plus 10k transactions to save 10k records. While I understand that on the surface this looks good since the application stays responsive, underneath it doesn't since it is a huge overhead. My point was this: since the OP doesnt describe exactly how the save process is (except the 10k records, but nothing else), it is kind of pointless to say any solution is better, since the bottleneck of the OP's current solution is unknown. – Maarten Aug 29 '16 at 16:01
@Maarten your assumption of how the mechanics operate is flawed. – MickyD Aug 29 '16 at 22:49

Speed up model instantiation for EF6 / sqlite db

2 Answers2