C# Function uses a lot of memory

Question

I use ASP.NET Core 6 MVC with ClosedXML.Excel reader from Excel via parallel.For, but this function eats more than 1.5 Gb of Ram memory.

Image how much memory uses the function

public static void ReadFile(IFormFile MyFileCollection)
{
        IFormFile MyFile;
        int FileLen;
        System.IO.Stream MyStream;

        MyFile = MyFileCollection;
        FileLen = Convert.ToInt32(MyFile.Length);
        byte[] input = new byte[FileLen];

        if (MyFile.FileName.Contains('~'))
        { 
            NameOfUpload = MyFile.FileName.Split('~')[1].Split('.')[0]; 
        }

        // Initialize the stream.
        MyStream = MyFile.OpenReadStream();

        // Read the file into the byte array.
        MyStream.Read(input, 0, FileLen);
        bool _FindedHead = false;

        var workbook = new XLWorkbook(MyStream, XLEventTracking.Disabled);
        var worksheet = workbook.Worksheet(1);

        Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
        using (var reader = ExcelReaderFactory.CreateReader(MyStream))
        {
            var result = reader.AsDataSet();
            DataSet _DtSet = reader.AsDataSet();
            DataTable dataTable = _DtSet.Tables[0];
            DataView _Workscheet_DataViewData = dataTable.AsDataView();

            var rows = dataTable.Rows.Count;
            int _GlobalRow_Count = dataTable.Rows.Count;
            string[,] _Data_ERS = new string[_GlobalRow_Count + 10, 3];
            int _StartPosition = 1;

            int localSizes = dataTable.Rows.Count;
            string[] _Number_Nakladnoi = new string[localSizes];
            string[] _NumberOrderOf_Sender = new string[localSizes];
            string[] _Partiya = new string[localSizes];
            string[] _NomerRZ = new string[localSizes];
            int[] _PlaceCount = new int[localSizes];
            string[] _MethodDelivery = new string[localSizes];
            string[] _TypeOf_delivery = new string[localSizes];
            string[] _CityDelivery = new string[localSizes];
            string[] _PVZ_Target = new string[localSizes];
            string[] _Kladr_PointDelivery = new string[localSizes];
            DateTime[] _DateFaktDelivery = new DateTime[localSizes];
            string[] _CargoState = new string[localSizes];
            string[] _ReasonDontArrive = new string[localSizes];
            double[] _WeightBySize = new double[localSizes];
            double[] _WeightFakt = new double[localSizes];
            double[] _COD = new double[localSizes];
            string[] _TypeOfPay = new string[localSizes];
            //string[] _LstERs = new string[localSizes];
            string[] _TempCheck = new string[localSizes];

            for (int z = 1; z < dataTable.Rows.Count; z++)
            {
                var _Parametr_Row = worksheet.Row(z);
                _Number_Nakladnoi[z] = _Parametr_Row.Cell(1).GetValue<string>().ToString().Trim();

                if (string.IsNullOrEmpty(_Number_Nakladnoi[z]))
                { 
                    continue; 
                }

                if (_Number_Nakladnoi[z].ToLower() == "номер посылки")
                { 
                    _FindedHead = true; _StartPosition = z + 1; 
                    break; 
                }

                if (!_FindedHead)
                { 
                    continue; 
                }
            }

            ParallelOptions options = new ParallelOptions();
            options.MaxDegreeOfParallelism = 150;

            ConcurrentBag<DataFromFile> ConCurentCollection = new ConcurrentBag<DataFromFile>();
            ConcurrentBag<int> ConCurentTest = new ConcurrentBag<int>();
            DataFromFile[] _BagCollectionArray = new DataFromFile[dataTable.Rows.Count];

            var resultParallel = Parallel.For(_StartPosition, dataTable.Rows.Count, options, (i, state) =>
            {
                var _Parametr_Row = worksheet.Row(i).Cell(4).CachedValue;

                _Number_Nakladnoi[i] = worksheet.Row(i).Cell(1).GetValue<string>().ToString().Trim(); //
                _NumberOrderOf_Sender[i] = worksheet.Row(i).Cell(2).GetValue<string>().ToString().Trim(); //
                _Partiya[i] = worksheet.Row(i).Cell(4).CachedValue.ToString();// worksheet.Row(i).Cell(4).GetValue<string>(); //
                _NomerRZ[i] = worksheet.Row(i).Cell(5).GetValue<string>().ToString().Trim(); //
                _PlaceCount[i] = string.IsNullOrEmpty(worksheet.Row(i).Cell(6).GetValue<string>().ToString()) ? 0 : worksheet.Row(i).Cell(6).GetValue<int>(); //
                _MethodDelivery[i] = worksheet.Row(i).Cell(7).GetValue<string>().ToString().Trim(); //
                _TypeOf_delivery[i] = worksheet.Row(i).Cell(8).GetValue<string>().ToString().Trim();
                _CityDelivery[i] = addresses.GetCorrectCityName(worksheet.Row(i).Cell(10).GetValue<string>().ToString().Trim()); //The city Name
                _PVZ_Target[i] = worksheet.Row(i).Cell(11).GetValue<string>().ToString().Trim();
                _Kladr_PointDelivery[i] = worksheet.Row(i).Cell(12).GetValue<string>().ToString().Trim(); //

                _DateFaktDelivery[i] = !string.IsNullOrEmpty(worksheet.Row(i).Cell(15).GetValue<string>().ToString().Trim()) ? SystemClass.GetDateTimeFromString(worksheet.Row(i).Cell(15).GetValue<string>()) : SystemClass.GetDateTimeFromString(worksheet.Row(i).Cell(13).GetValue<string>()); //
                _CargoState[i] = worksheet.Row(i).Cell(18).GetValue<string>().ToString().Trim();
                _ReasonDontArrive[i] = worksheet.Row(i).Cell(19).GetValue<string>().ToString().Trim();

                _WeightBySize[i] = string.IsNullOrEmpty(worksheet.Row(i).Cell(20).GetValue<string>().ToString()) ? 0 : worksheet.Row(i).Cell(20).GetValue<double>();
                _WeightFakt[i] = string.IsNullOrEmpty(worksheet.Row(i).Cell(21).GetValue<string>().ToString()) ? 0 : worksheet.Row(i).Cell(21).GetValue<double>();
                _COD[i] = string.IsNullOrEmpty(worksheet.Row(i).Cell(23).GetValue<string>().ToString()) ? 0 : worksheet.Row(i).Cell(23).GetValue<double>();

                _BagCollectionArray[i] = new DataFromFile
                {
                    _Number_Nakladnoi = _Number_Nakladnoi[i],
                    _NumberOrderOf_Sender = _NumberOrderOf_Sender[i],
                    _Partiya = _Partiya[i],
                    _NomerRZ = _NomerRZ[i],
                    _PlaceCount = _PlaceCount[i],
                    _MethodDelivery = _MethodDelivery[i],
                    _TypeOf_delivery = _TypeOf_delivery[i],
                    _CityDelivery = _CityDelivery[i],
                    _PVZ_Target = _PVZ_Target[i],
                    _Kladr_PointDelivery = _Kladr_PointDelivery[i],
                    _DateFaktDelivery = _DateFaktDelivery[i],
                    _CargoState = _CargoState[i],
                    _ReasonDontArrive = _ReasonDontArrive[i],
                    _WeightBySize = _WeightBySize[i],
                    _WeightFakt = _WeightFakt[i],
                    _COD = _COD[i]
                };

                ConCurentCollection.Add(AddNewElement(_BagCollectionArray[i]));
                ConCurentTest.Add(i);
            });
        }

        MyStream.Close();
        worksheet.Delete();
        workbook.Dispose();
}

I tried to dispose everything, but everything looks like before. Maybe I forget something or do something wrong.

I try to dispose MyStream, workbook, worksheet - but there is no result in the usage of memory.

I try reduce code and find out that the problem is on ClosedXML. I have tried many approaches to release resources but nothing help me. I also tried use not static function, objects and GC.

GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect();

Maybe somebody have an Idea about how to dispose ClosedXML? Or maybe I do something wrong

Using available memory is not per se a problem. Or do you get an OutOfMemoryException? Or any other problem? — Klaus Gütter, Dec 05 '22 at 05:03
Now everything is ok, but I really worry about when I release it to server - this may be caused OutOfMemoryException — Andrey_G, Dec 05 '22 at 05:06
I think this is a duplicate, but if I close as duplicate it will close your question: https://stackoverflow.com/q/46373895/495455 — Jeremy Thompson, Dec 05 '22 at 05:06
Unfortunately no, My files open very fast everything works very fast (about 20 seconds) with 40 000 rows. I worry about use this approach in release version. I mean If forget something to release then not used memory may accrue. — Andrey_G, Dec 05 '22 at 05:10
Using many arrays with the same element count, sounds like a list of structs/classes/records.. — Jeroen van Langen, Dec 05 '22 at 07:40
Check how many rows are actually in your DataTable. I once made the mistake of every row from the file instead of only used rows (so instead of 400 it was a few million rows in my case). — lordvlad30, Dec 05 '22 at 09:23
Yes, you're right I paid attention to it too) but everything is ok. Like I understand the problem is in ClosedXML because it is not dispose. I tried update library but it's still the same — Andrey_G, Dec 05 '22 at 09:37

score 1 · Answer 1 · answered Dec 05 '22 at 07:37

1

Something that will help slightly (though won't make a massive difference) is to change:

if (_Number_Nakladnoi[z].ToLower() == "номер посылки")

to:

string.Equals(_Number_Nakladnoi[z], "номер посылки",
     StringComparison.InvariantCultureIgnoreCase);

This would help reduce allocations.

Also, is there a need to create so many arrays instead of putting all your variables in a class/struct and have just one?

answered Dec 05 '22 at 07:37

Mark Cilia Vincenti

1,410
8
25

You're right. it will be better to use class. I will change it, thanks :) – Andrey_G Dec 05 '22 at 08:18

score 0 · Answer 2 · answered Dec 05 '22 at 07:35

0

static objects remains in memory and never be collected. one of reasons that this method eats memory is for 'static'.

read this for more info: https://www.c-sharpcorner.com/article/common-memory-leaks-in-c-sharp/

answered Dec 05 '22 at 07:35

Hamidreza Aliyari

26
2

1

I haven't heard about this, I'm going to change it. Thanks :) – Andrey_G Dec 05 '22 at 08:20
1

I tried to use not static method, unfortunately result is the same. Using memory 1.7 Gb for this function and do not release after reading – Andrey_G Dec 05 '22 at 08:51
1

another solution is to avoid global variables. maybe you are setting a huge value to your global variable. try removing them may help to avoid memory leaks. – Hamidreza Aliyari Dec 05 '22 at 19:35
1

also you can write your code in 'try/catch' and then in 'finally' dispose you disposable objects. by this you ensure that your streaming objects are disposed even when getting error – Hamidreza Aliyari Dec 05 '22 at 19:38
1

Thank you for your message. I tried [try. catch, finally]. But the volume of used memory is till the same – Andrey_G Dec 06 '22 at 04:07

Andrey_G · Accepted Answer · 2023-06-13T06:29:13.490

0

Thank you very much for everybody. Last time I updated ClosedXML and continue use

GC.Collect(); 
GC.WaitForPendingFinalizers();

After that memory disposed. I don't know why but in my previous version this approach doesn't work. Thank you again. My current version ClosedXML - 0.97.0 Result work for 14 days

I added screen how it works more than 14 days and uses less 417 Mb.

edited Jun 13 '23 at 06:29

answered Jun 13 '23 at 06:26

Andrey_G

9
5

score -1 · Answer 4 · answered Dec 05 '22 at 05:12

-1

Try reducing reference types objects using, for example, structs

answered Dec 05 '22 at 05:12

w4rcT

180
1
13

1

More in detail explanation would help OP and future users who will have similar problem, can you support your answer with more urls for op to use as a reference ? – RedRose Dec 07 '22 at 19:57

score -1 · Answer 5 · answered Dec 05 '22 at 05:14

You need to use a using statement here to get the ram back

using(MyStream=MyFile.OpenReadStream()){
//Here you put the the code until 
//MyStream.Close();
}
worksheet.Delete
workbook.Dispose

Because the Files and FileStream are not in .Net-Framework they can't be collected by the Garbage collector. By using using(){} it become part of .Net-Framework for this Code-Block and can be collected by the Garbage collector

C# Function uses a lot of memory

5 Answers5