1

Here I have 2 datatables, I want to make some changes runtime, so I used 2 for loops. But when user hit is huge like 20k+, system get down. It took more than 2 minutes every hit, I want to optimize, can anyone help on this?

Datatable BuybackResponse = ResourceCenterDAL.GetBuyBack(2);;
Datatable dt = ResourceCenterDAL.GetBuyBack(4);;
if (BuybackResponse.Count > 0)
{
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        for (int k = 0; k < BuybackResponse.Count; k++)
        {
            var Exch = !string.IsNullOrEmpty(Convert.ToString(dt.Rows[i]["Nsetradingsymbol"])) ? "NSE" : "BSE";

            //if (Exch == BuyBackExchangeMapping[Convert.ToInt32(BuybackResponse[k]["Exchange"])] &&
            if (BuybackResponse[k]["ProductCode"].Trim().ToLower() == Convert.ToString(dt.Rows[i]["Nsetradingsymbol"]).Trim().ToLower() ||
              BuybackResponse[k]["ProductCode"].Trim().ToLower() == Convert.ToString(dt.Rows[i]["Bsetradingsymbol"]).Trim().ToLower())
            {
                if (!Convert.ToString(dt.Rows[i]["isBuyBack"]).Trim().ToLower().Equals("y"))
                    dt.Rows[i]["isBuyBack"] = "Y";
                dt.Rows[i]["productcode"] = BuybackResponse[k]["ProductCode"];
                BBcount++;
            }
            else
            {
                if (string.IsNullOrEmpty(Convert.ToString(dt.Rows[i]["isBuyBack"])))
                    dt.Rows[i]["isBuyBack"] = "N";
            }
        }
        if (BBcount.Equals(BuybackResponse.Count)) { break; }
    }
}
Magnetron
  • 7,495
  • 1
  • 25
  • 41
Tony Walter
  • 36
  • 4
  • Not sure that it will significantly improve anything but you can move all the `dt.Rows[i]....` to variables outside the second loop. – Guru Stron Jun 09 '21 at 19:26
  • If I use list instead of datatable and use linq, will it be helpful.....? @GuruStron – Tony Walter Jun 09 '21 at 19:30
  • Trim().ToLower() should be done when the data is created in the arrays, not when you are looping them. That process drains your CPU like crazy. – Morten Bork Jun 09 '21 at 19:30
  • however, if the problem is the database can't handle the load of the requests, perhaps you need to investigate your database? Did you create indexes? Did you add the values you are requesting to the columns in the index? Is your connection pool spent? how much data are you sending back and fourth? perhaps you are reaching the limit of your bandwith? – Morten Bork Jun 09 '21 at 19:32
  • @MortenBork thank you, I'll do that – Tony Walter Jun 09 '21 at 19:34
  • @MortenBork database indexes are created, database gives me a result in 1-2secs, issue in this loop only – Tony Walter Jun 09 '21 at 19:35
  • You have expressions like this inside the doubly-nested loop: `BuybackResponse[k]["ProductCode"]`. Outside one or both loops, figure out the ordinal value for the `["ProductCode"]` column and do the lookups by ordinal and not name. It may speed things up a lot (given how many times it's executed) – Flydog57 Jun 09 '21 at 20:01
  • Why not just do this all in the database and return the data already as you need it? – Trevor Jun 09 '21 at 20:06
  • @Flydog57 ordinal means index..? do you want me to put [0], [1] instead of ["ProductCode"]...? – Tony Walter Jun 09 '21 at 20:12
  • @zaggler both the source are different – Tony Walter Jun 09 '21 at 20:12
  • They not use the same DB then or? `database indexes are created, database gives me a result in 1-2secs` is my assumption you could. – Trevor Jun 09 '21 at 20:14
  • @zaggler let me check and confirm, 1st set of data I'm getting from database, 2nd I'm getting from API.... – Tony Walter Jun 09 '21 at 20:15
  • 1
    @zaggler: Look up what index is related to `"ProductCode"` before you start. Then use that index instead of `"ProductCode"` when you want to access the ProductCode column. No, don't count columns and just use `5`, that's very fragile. – Flydog57 Jun 09 '21 at 20:21
  • Take a look at: https://stackoverflow.com/questions/11340264/get-index-of-datatable-column-with-name. Up outside the outer loop do something like: `var productCodeIndex = dt.Columns["ProductCode"].Ordinal;` for each of the columns you are interested in. Then, inside the loops, use something like `BuybackResponse[k][productCodeIndex]` – Flydog57 Jun 09 '21 at 21:53
  • In this method: (ResourceCenterDAL.GetBuyBack(X)) Convert the data, as part of the "retrieval" of the data, that you change the value in: BuybackResponse[k]["ProductCode"] to a trim().ToLower() and in dt.Rows[i]["Nsetradingsymbol"] to a trim().ToLower() This way you wont do it on every loop. If then also add dataset as "cached" if that is permittable, you should see a large performance increase. – Morten Bork Jun 10 '21 at 06:27

0 Answers0