0

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.

8
  • 1
    Probably faster to send the json to the DB and parse it there. What DB is in use? Commented Jul 8 at 6:59
  • PostgreSQL DB I'm using @flackoverstow Commented Jul 8 at 7:00
  • To start with: Don't use Newtonsoft, switch to STJ. Don't use interpolated strings in log meassage templates. Ditch Console.WriteLine. Reuse the db context instead of recreating in every iteration ... and that's assuming you cannot to it directly on the db.
    – Fildor
    Commented Jul 8 at 7:01
  • Thanks @Fildor for your suggestion ditch console .writeline and how can I reuse db context instead of recreating in every iteration can you help me to modify my code if don't mind Commented Jul 8 at 7:04
  • 1
    Thanks @Fildor let me try to give it a shot Commented Jul 8 at 7:33

0