I need to insert 1.9 million new records into a MySQL database. To use this I'm using C# Entity Framework, but the process seems incredibly slow. At the current rate, it would take several days to process these records.
What am I doing wrong and how do I speed this up?
In the database I have 2 tables: Hashes and Categories. Each hash should be unique and can have multiple categories, with only 1 category being active per hash.
The process that I need to follow is to first check if the hash exist. If it does, then I need to find the current category, deactivate it and add the new one.
The problem is that my try{ } statement is taking about 150ms and the block that does SaveChanges() takes about 15-30 seconds. So, doing 1.9M records this way will take several days.
using (var reader = new StreamReader(File.OpenRead(filepath)))
using (MySQLContext db = new MySQLContext(options))
{
// Disable auto detect changes
db.ChangeTracker.AutoDetectChangesEnabled = false;
int loopCounter = 0;
string line;
// Load up the db tables in memory
var hashes = db.Hashes.Select(x => x).ToList();
var category = db.Categories.Select(a => a).ToList();
while ((line = reader.ReadLine()) != null)
{
var matches = Regex.Matches(line, "(?<MD5>[a-zA-Z0-9]+)(?<Category>[0-9])");
InputHashModel inputHash = new InputHashModel()
{
MD5 = matches[0].Groups["MD5"].Value,
Category = matches[0].Groups["Category"].Value
};
try
{
// Check if hash already exists
Hash hash = hashes.Where(h => h.MD5 == inputHash.MD5).FirstOrDefault();
// If hash doesn't exist - add it
if (hash == null)
hash = new Hash(inputHash.MD5);
else
{
// Check if category already exists
Category category = categories.Where(a => a.Active == true && a.HashId == hash.Id).FirstOrDefault();
// If it exists - deactivate it
if (category != null)
{
// If the same category already exists - proceed to next hash
if (category.Source == "ThisInput" && category.Category == inputHash.Category)
{
loopCounter++
continue;
}
category.Active = false;
category.DeactivatedTimestamp = DateTime.Now;
}
}
// Add new category
Category new_category = new Category() { Hash = hash, Source = "ThisInput", Category = inputHash.Category, Active = true);
db.Categories.Add(new_category);
// Save changes every 1000
if (loopCounter % 1000 == 0)
{
db.ChangeTracker.DetectChanges();
db.SaveChanges();
}
}
catch (Exception e)
{
Console.WriteLine("Exception: " + e);
}
loopCounter++;
}
db.ChangeTracker.AutoDetectChangesEnabled = true;
db.SaveChanges();
Console.WriteLine("Finished");
}