Move large amount of data from old database to new database using Entity Framework 5

Question

I'm creating an application to move data from old to new database (different schema). I'm using Visual Studio 2013, C#, Entity Framework 5, Microsoft SQL Server 2012. This table, Customer, has more than 40 thousand records.

private void TransferCustomer()
{
    int counter = 0;

    // Load all old customers
    var oldCustomers = _oldRockDale.customers;

    foreach (var oldCustomer in oldCustomers)
    {
        // Create new customer
        ...

        // Modify something
        ...

        // Add to collection
        <New_database_entity>.Customers.Add(newCustomer);

        // Insert to database for each 1000 records
        counter++;
        if (counter % 1000 == 0)
        {
            <New_database_entity>.SaveChanges();
        }
    }

    // Insert the rest to database
    <New_database_entity>.SaveChanges();
}

Here is my problem: this function runs slower and slower. For the first 1000 records, it's just about 20 - 30 seconds. But it becomes much slower as it goes. Then, it takes more than 1 minutes to reach 2000.

My questions are:

• Why does it run slower and slower?
• Is there any better way to transfer a large amount of data like this?

One more information: as I observe in Output window:

• Only 1 line saying that thread exited with code 0.
• After that, there are many lines saying thread exited with code 259.

Thank you so much for your help.

Answer 1

I think this is linked to the growth of the DbContext.

You can take advantage of the following posts:

• Fastest Way of Inserting in Entity Framework
• Improving bulk insert performance in Entity framework

Basically you have to insert by parts of 100 rows for example, and reset (as set again, that is: _context = new SomeContext();) the context between each insert.