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.