How to improve performance when iterating over the elements of a DataTable?

Question

ASP.NET

The essence of the problem is as follows, using a sql query, I get a table with 19,000 rows.
Next, I need to fill in another table based on the data in this table:

for (int i = 0; i < data.Rows.Count; i++)
            {
                ClearCurrentConsoleLine(menuBaseString.Length, menuBaseString.Length + 1);
                Console.Write(i + "\\" + data.Rows.Count);
                var row = data.Rows[i];
                context.Pages.Add(new Page()
                {
                    //...
                    Catalog_Id = Convert.ToInt32(row.ItemArray[2]),
                    CatalogName = row.ItemArray[3].ToString(),
                    CatalogIdentifer = row.ItemArray[4].ToString(),
                   //...
                });
            }
context.savechanges();

The problem is that the first 3000 lines are processed quite quickly (5 minutes), but then the enumeration process slows down dramatically and in total such an operation takes about 1.5-2 hours.
Is there any way to optimize this code?

Answer 1

well, actually everything turned out to be simple, first you need to create a List with all the objects that need to be added, and then do context.AddRange

var tmp = new List<Page>();
            for (int i = 0; i < data.Rows.Count; i++)
            {
                ClearCurrentConsoleLine(menuBaseString.Length, menuBaseString.Length + 1);
                Console.Write(i + "\\" + data.Rows.Count);
                var row = data.Rows[i];
                tmp.Add(new Page()
                {
                    //...
                    Catalog_Id = Convert.ToInt32(row.ItemArray[2]),
                    CatalogName = row.ItemArray[3].ToString(),
                    CatalogIdentifer = row.ItemArray[4].ToString(),
                   //...
                });
            }
context.Pages.AddRange(tmp);

total instead of 2 hours , 4 min

Answer 2

And what is ClearCurrentConsoleLine(menuBaseString.Length, menuBaseString.Length + 1);
If this is an output of progress or some data to the console, remove this line and performance will increase by 50 times. In general, run the process under the debugger and profiling. Check which libraries and methods are slowing down the process.