1

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.

Community
  • 1
  • 1
Triet Doan
  • 11,455
  • 8
  • 36
  • 69
  • It gets slower and slower because of change tracking. – ta.speot.is Jan 26 '15 at 10:16
  • I'll try disabling it and wait the result. Thanks for your suggestion :) – Triet Doan Jan 26 '15 at 10:18
  • Change tracking is mostly useful. You might prefer to create more db contexts instead (one per 1,000 entities). – ta.speot.is Jan 26 '15 at 10:21
  • Fortythousand rows is nowhere near "large" in my eyes... BTW: You can move and convert data easily with the SQL Server Management Studio (if your DBMS is SQL Server, which you didn't tell us yet). – Rob Jan 26 '15 at 10:26
  • Yes, I'm using SQL Server. I use C# application because I'm not good at SQL and some data need modification. And I have the same thought with you, 40k rows is not large. But why does my application run slower and slower? – Triet Doan Jan 26 '15 at 10:32
  • @ta.speot.is: you mean doing it parallel? – Triet Doan Jan 26 '15 at 10:38

1 Answers1

1

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

You can take advantage of the following posts:

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.

Community
  • 1
  • 1
tschmit007
  • 7,559
  • 2
  • 35
  • 43