Click here to Skip to main content
15,847,903 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more:
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

What I have tried:

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)

            {

            }
Posted
Updated 20-Jul-23 0:05am
Comments
PIEBALDconsult 20-Jul-23 13:23pm    
That definitely sounds like something you need to be doing inside the database.

It's impossible for anyone to tell you what's going to speed this up with any accuracy because, well, there is insufficient information on the code and the backing datastore.

So, we'll start with is any indexing setup on the fields you're using in your conditional statements where you're calling GetList (whatever that is?) Indexing will help with quicker database queries.

You're calling .ToString everywhere, which is probably unnecessary in every case you're using it.

You're iterating over a set of records returned from the database, then you appear to be making additional calls to the database for each individual record. This is going to kill you're performance more than anything else.

You're appear to be calling Trim in a lot of places. This tells me you're not normalizing the data before it's written to the database. If you don't fix up your data before writing it to the database, you're going to be wasting a lot of time fixing it up when you retrieve it.

You're using string concatenation to build SQL queries and you've been told before that's a bad idea.

The more you iterate over sets of records, the worse your code is going to perform. How many loops do you have, and how many of those loops are inside outer loops? Every time you put a loop inside another loop, that's an exponential performance hit for each depth you add. You've got a loop inside a loop, inside another loop, and you wonder why the code is so slow?
 
Share this answer
 
v2
As Dave said, that's a heck of a lot of logic to be performing within a loop that potentially has 1m records. It's absolutely no surprise to anybody that that would be running slow. There's even some logic that I don't understand the purpose of, like:
DataTable dtvdSales = ConvertListToDataTable<SalesDetails>(vdSalesList);

I mean, why create a DataTable when you already have a list you can iterate over? But in any case, if I were you I'd look into how much of the logic of this code can be done natively within your SQL database via something like a stored procedure or function. Having the code execute directly within the database is likely to provide some improvement, but I imagine it'll still take a good while to complete. At the very least moving this logic out of the C# code should help you identify logic that can either be trimmed or optimised.
 
Share this answer
 
Quote:
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

You forgot to tell us the database design, it prevent us to detect any flaw in it.
You forgot to tell us what this code is trying to do, it prevent us from spotting design problems (the way it solve the problem) in your code.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900