I'm having a .net core console application that reads parts of the JSON files stored in a folder the part of the JSON that I'm reading contains greater than 500,000 records in it, I'm able to read all the data but the only challenge I'm facing is that it is very slow it takes approximately 10 minutes to fetch and insert all the data. how can I modify my code below so I can improve performance and increase the speed to minimize the time it takes to process and insert all the data.
using (var context = new ApplicationDbContext())
{
var dispenseToDelete = context.tblDispense.Where(p => p.HfrCode == facilityName);
if (dispenseToDelete.Any())
{
context.tblDispense.RemoveRange(dispenseToDelete);
context.SaveChanges();
}
}
using (StreamReader sr = new StreamReader(filePath))
using (JsonTextReader reader = new JsonTextReader(sr))
{
while (reader.Read())
{
if (reader.TokenType == JsonToken.StartArray && reader.Path == "tblDispense")
{
using (var context = new ApplicationDbContext())
{
List<TblDispense> tblDispenseBatch = new List<TblDispense>();
int batchSize = 1000;
while (reader.Read() && reader.TokenType != JsonToken.EndArray)
{
if (reader.TokenType == JsonToken.StartObject)
{
JObject item = JObject.Load(reader);
TblDispense tblDispense = item.ToObject<TblDispense>();
tblDispense.UUID = Guid.NewGuid().ToString() + "." + facilityName;
tblDispense.MSDCode = tblDispense.MSDCode + '.' + facilityName;
tblDispense.DispenseID = tblDispense.DispenseID + "." + facilityName;
tblDispense.DispenseRecordId = tblDispense.DispenseRecordId + '.' + facilityName;
tblDispense.HfrCode = facilityName;
tblDispenseBatch.Add(tblDispense);
// Batch insert to avoid memory overload
if (tblDispenseBatch.Count >= batchSize)
{
try
{
context.tblDispense.AddRange(tblDispenseBatch);
context.SaveChanges();
tblDispenseBatch.Clear();
}
catch (DbUpdateException ex)
{
Console.WriteLine($"[{facilityName}] an error occurred while saving the batch: {ex.InnerException?.Message}");
logger.LogError($"[{facilityName}] an error occurred while saving the batch: {ex.InnerException?.Message}");
}
}
}
}
// Insert remaining items in the batch
if (tblDispenseBatch.Count > 0)
{
try
{
context.tblDispense.AddRange(tblDispenseBatch);
context.SaveChanges();
tblDispenseBatch.Clear();
}
catch (DbUpdateException ex)
{
Console.WriteLine($"[{facilityName}] an error occurred while saving the batch: {ex.InnerException?.Message}");
logger.LogError($"[{facilityName}] an error occurred while saving the batch: {ex.InnerException?.Message}");
}
}
}
break;
}
}
}
any suggestions will be much appreciated.