How to insert 6,500,000 records into a table that contains 126 columns?

Question

My application is running on .NET Core 6. I am using the BulkExtensions library which allows me to enter 2,400,000 records, then I get this message

The node was low on resource: memory container fifi-web using 36045744Ki, which exceeds its request of 0

and the server restarts.

The records are obtained from a .cat file that is traversed line by line to obtain each parameter and the method I am using is the following:

public void InsertCatsFromFileWithBulkInsertAzure(List<string> lines, string name, string username, Guid id, DateTime? dateCat)
{
    DateTime now = DateTime.Now;
    long pendingLines = 0;
    var executionStrategy = _context.Database.CreateExecutionStrategy();            

    executionStrategy.Execute(
        () =>
        {
            List<Cat> data = new List<Cat>();

            for (var i = 1; i < lines.Count; i++)
            {                        
                try
                {                       
                    Cat parsedCat = ParseCat(i, lines);
                    parsedCat.CatGeneralId = id;
                    parsedCat.Date = now;
                    parsedCat.DateCat = dateCat;
                    data.Add(parsedCat);
                    pendingLines++;

                    if (pendingLines == 150000)
                    {
                        _context.Cats.AddRange(data);
                        _context.BulkSaveChanges();
                        data.Clear();
                        pendingLines = 0;
                    }
                }
                catch (Exception ex)
                {
                    var line = new Cat
                    {
                        Type = lines[i].Substring(0, 2),
                        CadastralParcel = lines[i].Substring(30, 14),
                    };
                    Console.WriteLine($"Se presento un error con el codigo CadastralParcel: {line.CadastralParcel}, Type: {line.Type} en la linea: {i}. mensaje de error: {ex.Message}");
                }
            }
            _context.Cats.AddRange(data);
            _context.BulkSaveChanges();
        });
}

We tried the process of uploading a csv file into the database but it gave the same error and takes longer than expected.

Answer 1

Clear you context after save changes. The change tracker keeps a copy of your entities.

_context.ChangeTracker.Clear();