Entity Framework Bulk Load Too Slow Add Seed

Question

    protected override void Seed(Fitlife.Domain.Concrete.EFDBContext context)
    {
        List<List<string>> foodweights = GetLines(basePath + "FoodWeights.txt");
        int counter = 0;
        foodweights.ForEach(line =>
        {
            FoodWeights newVal = new FoodWeights()
            {
                 FoodCode = int.Parse(line[0]),
                  PortionCode = int.Parse(line[1]),
                 PortionWeight = decimal.Parse(line[2])
            };
            context.FoodWeights.Add(newVal);

            if (++counter == 1000)
            {
                counter = 0;
                context.SaveChanges();                  
            }
        });
}

Above method is used to populate my database. But it takes 50 seconds for 1000 entries i have a file with 470k entries, how can i improve performance i am using entity framework and this method is called when i do PM> update-database with Package manager. i need similar functionality, i am very new to asp.net and entity framework any guidance will be appreciated thanks.

PS: Is it ok to take 50 seconds for 1000 entries or am i doing something wrong.

Unfortunately EF doesn't play well with batch/bulk jobs. I'd just use [`SqlBulkCopy`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx), which was designed for mass inserts. — Patryk Ćwiek, Oct 10 '13 at 21:39
can you provide me with any example i can use sqlbulkcopy inside my seed function, i want to still be able to use package manager command like PM> update-database — , Oct 10 '13 at 21:43
How many records does FoodWeights.txt contain? I see that your saving every 1000 lines. It looks as if it will still loop through your entire file, which could take quite some time if FoodWeights.txt is a large file. — jsmith, Oct 10 '13 at 21:44

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

1

The Seed method runs every time the application starts, so the way you have coded it will attempt to add the FoodWeights over and over again. EF have provided the AddOrUpdate as a convenient method to prevent that but it is really not appropriate for bulk inserts.

You could use sql directly on the database - and if you are using sql server that sql could be 'BULK INSERT'.

I would put the sql in an Up migration because you probably only want to run the insert once from a known state, and it avoids having to worry about the efficiency of the context and tracking changes etc.

There is example code and more information here: how to seed data using sql files

edited May 23 '17 at 12:23

Community

1
1

answered Oct 11 '13 at 08:20

Colin

22,328
17
103
197

Thanks for the tips, i used SqlBulkInsert and its fast, i will put the seed in up migration, but what is the purpose of my overriden seed function, does it have a convenience. – Oct 13 '13 at 19:40
I posted more on this answer - with links to more reading http://stackoverflow.com/a/19267917/150342 – Colin Oct 14 '13 at 07:54

Entity Framework Bulk Load Too Slow Add Seed

1 Answers1