How to enhance foreach list over 1 millions rows select and update

Question

I work on asp.net core 7 . i have list have over than one milion rows when it executed it take too much time to finish

too much time reach to 1 day

var BranchesList = _OsuBranch.GetList(x => x.BRTYPE == "BR" && x.OSU_Is_Active == "Y");

Branches List return 1 millions rows

 if (BranchesList != null && BranchesList.Any())

                {

     //select and update   take too much time

                }

so please what enhancement or technologies can use to make process select and update faster according to my code

or if you have another way can do this code with best way faster so if you know any way please tell me

if another or new technology tel me

my code details

public async Task UpdateCost()

        {

            DateTime Fdate;

            DateTime TDate;

            try

            {

                string JDEsql = "";

                string NAVsql = "";

                DataTable JDEds = new DataTable();

                int totalRows = 0;

                int JDEtotalRows = 0;

                TDate = DateTime.Now;

                Fdate = DateTime.Now.AddDays(-7);

                string date1 = Fdate.ToString("yyyy-MM-dd");

                string date2 = TDate.ToString("yyyy-MM-dd");

                string strBranch = "";

                string TotalJDERows = "";// = "Total JDE Rows: " + JDEtotalRows;

                string TotalUpdatedRows = "";// = "Total Updated Rows: " + totalRows;

                string RunningCost = "";

                string FromDateToDate = "";

                string AppID = "";

              

                var BranchesList = _OsuBranch.GetList(x => x.BRTYPE == "BR" && x.OSU_Is_Active == "Y");

                if (BranchesList != null && BranchesList.Any())

                {

                    foreach (var branch in BranchesList)

                    {

                        strBranch = branch.brcode.ToString();

                       

                        AppID = _OsuBranch.GetFirst(x => x.brcode == strBranch).AppID.ToString(); 

                       

                        string strBranches;

             

                        var strSubUnit = _OsuBranch.GetFirst(x => x.brcode == strBranch && x.DeptNo == "058").JSubUnit.ToString();//.JSubUnit.ToString(); //getSubUnint(strBranch);

                        if (strSubUnit == null || strSubUnit.CompareTo("0") <= 0)

                        {

                            strSubUnit = "0";

                        }

                        else

                        {

                            strSubUnit = _OsuBranch.GetFirst(x => x.brcode == strBranch && x.DeptNo == "058")?.JSubUnit.ToString();

                        }

                        if (strSubUnit.ToString() == "0")

                        {

                            strBranches = string.Format("'{0}'", strBranch);// "'" + strBranch + "'";

                        }

                        else

                        {

                          

                            strBranches = string.Format("'{0}','{1}'", strBranch, strSubUnit.ToString().Trim());

                        }

                        RunningCost = "Running Cost Update for Branch " + strBranches;

                        FromDateToDate = "From Date: " + date1 + " To Date: " + date2;

                        var vdSalesList = _salesDetails.GetList(x => Convert.ToDateTime(x.sldate) >= Convert.ToDateTime(FormatDateAsyyyyMMdd(date1)) && Convert.ToDateTime(x.sldate) <= Convert.ToDateTime(FormatDateAsyyyyMMdd(date2)));

                        DataTable dtvdSales = ConvertListToDataTable<SalesDetails>(vdSalesList);

                        if (dtvdSales.Rows.Count > 0)

                        {

                            foreach (DataRow de in dtvdSales.Rows)

                            {

                                JDEsql = "SELECT SDDOCO, SDITM, SDAITM, SDUOM, (SDUNCS/10000) AS SDUNCS ,SDTRDJ ";

                                JDEsql += "  FROM PRODDTA.F42119 ";

                                JDEsql += "  WHERE  (SDKCOO in ( '00001','00005')) AND (SDDCTO = 'SP')  AND (SDTRDJ >= " + GeneralFunction.DateTimeToJulian(Convert.ToDateTime(date1)) + ") AND (SDTRDJ <= " + GeneralFunction.DateTimeToJulian(Convert.ToDateTime(date2)) + ") "; // AND LTRIM(SDEMCU) IN ( " + strBranches + ")

                                JDEsql += "  and SDDOCO in (" + de["DOCO"] + ") and SDITM=" + de["JDEItemNo"] + " and SDAITM='" + de["itemno"] + "' and  SDUOM ='" + de["SUOM"] + "' ";

                                JDEsql += "   UNION ";

                                JDEsql += "   SELECT SDDOCO, SDITM, SDAITM, SDUOM, (SDUNCS/10000) AS SDUNCS ,SDTRDJ ";

                                JDEsql += "  FROM PRODDTA.F4211 ";

                                JDEsql += "  WHERE  (SDKCOO in ( '00001','00005')) AND (SDDCTO = 'SP')  AND (SDTRDJ >= " + GeneralFunction.DateTimeToJulian(Convert.ToDateTime(date1)) + ") AND (SDTRDJ <= " + GeneralFunction.DateTimeToJulian(Convert.ToDateTime(date2)) + ") "; // AND LTRIM(SDEMCU) IN ( " + strBranches + ")

                                JDEsql += "  and SDDOCO in (" + de["DOCO"] + ") and SDITM=" + de["JDEItemNo"] + " and SDAITM='" + de["itemno"] + "' and  SDUOM ='" + de["SUOM"] + "' ";

                                JDEds = await _ConnectionDatabase.ReadDatabaseUsingClassLibrary(JDEsql);

                                if (JDEds.Rows.Count > 0)

                                {

                                    foreach (DataRow dr in JDEds.Rows)

                                    {

                                        JDEtotalRows++;

                                        int num = 0;

                                        num = await _salesDetails.Update(

          x =>

              x.brcode == strBranch &&

              x.JDEItemNo == dr["SDITM"].ToString() &&

              x.itemno == dr["SDAITM"].ToString().Trim() &&

              x.Doco == dr["SDDOCO"].ToString().Trim() &&

              x.SUOM == dr["SDUOM"].ToString().Trim() &&

              x.UnitCost == 0,

          x =>

          {

              x.UnitCost = Convert.ToDecimal(dr["SDUNCS"].ToString());

          });

                                        totalRows += num;

                                    }

                                }

                            }

                            TotalJDERows = "Total JDE Rows: " + JDEtotalRows;

                            TotalUpdatedRows = "Total Updated Rows: " + totalRows;

                        }

                    }

                }

            }

            catch (Exception ex)

            {

            }

Answer 1

first, when reading an updating millions of rows, you should use a data reader, not load all rows in memory. also you should be using set operations, not a nested loop.

while you could update all rows in one query, updating a million rows at once can cause contention, scaling and performance issues. I'd update a 1k - 10k rows at time. you can select the main keys to a temp table, with auto number column. then just page thru the temp table.

I would expect to be able to update a million rows in a couple minutes if keys are defined correctly.

so the first pass would be read the rows with a data reader and replace all inner loop code with one sql update statement. this should give a fair improvement.

once you have this, replace the read all rows with a create temp table. then page thru the temp table by tuned size (start with 1k). then update the update statement to join to the temp table by the page values.