When run this function updatecost it take too much time so how to enhance it to be faster ?

Question

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 19-Jul-23 15:57pm

ahmed_sa

Updated 19-Jul-23 23:05pm

Add a Solution

Comments

PIEBALDconsult 20-Jul-23 13:23pm

That definitely sounds like something you need to be doing inside the database.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Dave Kreskowiak · Answer 1 · 2023-07-19T17:18:00

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?

Chris Copeland · Answer 2 · 2023-07-19T22:29:00

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.

Patrice T · Answer 3 · 2023-07-19T23:05:00

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.

When run this function updatecost it take too much time so how to enhance it to be faster ?

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0