Question How to enhance function update cost count over 1 millions rows select and update ?

[ahmedaziz]

I work on asp.net core 7 . i have list have over than one millions 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)

            {

            }

 

[Skydiver]

Instead of GetList() returning all 1 million rows, rename it to GetEnumerable() and have it return an IEnumerable<T> instead. IEnumerable<T> implementations should be lazy and only return items on demand. So Any() will check checks if the enumerable has at least one item. It looks like the rest of your code just deals with one row at a time anyway, so there is no need to get all the rows in one big lump anyway.

 

[Skydiver]

Here is your code reformatted to be easier to read instead of all that extra linefeeds and inconsistent indentation:

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)
    {
    }

 

[Skydiver]

I'm not seeing where ASP.NET Core comes into play here. Are you actually dealing with Entity Framework Core but wrote ASP.NET Core by accident?

 

[cjard]

I don't think it's EFC, guessing by the big block of SQL injection risk toward the end.. Anyways... a million rows is, I guess, going to bash out 5+ million queries with this code.,a day is probably pretty good performance

I don't really have any desire to read the code so I can work out exactly what process you're doing, but if you tell us then maybe we can suggest more optimal ways of doing it. For example, bulk loading the file into the DB and running a query that does whatever this code does