question

TZacks-2728 avatar image
0 Votes"
TZacks-2728 asked DanielZhang-MSFT commented

How to speed up my loops

Here is my code.dtQCViewAll is datatable and i am iterate in all rows and columns of the data table and populate another empty datatable with data but based on some condition. code is working fine. the issue is when dtQCViewAll datatable has 40,00,000 data it is taking long time to complete the loops. please suggest some best option to increase the speed of the whole code if there is huge data. thanks

     tbl_CSMTuner = new DataTable();
     tbl_CSMTuner.TableName = "CSMTuner";
     tbl_CSMTuner.Columns.Add("OrderID");
     tbl_CSMTuner.Columns.Add("EarningID");
     tbl_CSMTuner.Columns.Add("Ticker");
     tbl_CSMTuner.Columns.Add("Broker");
     tbl_CSMTuner.Columns.Add("Section");
     tbl_CSMTuner.Columns.Add("LineItem");
     tbl_CSMTuner.Columns.Add("Period");
     tbl_CSMTuner.Columns.Add("ItemValue");
     tbl_CSMTuner.Columns.Add("ItemValue_NoFormat");
     tbl_CSMTuner.Columns.Add("BM_NBM");
     tbl_CSMTuner.Columns.Add("BM_Element");
     tbl_CSMTuner.Columns.Add("File_date");
     tbl_CSMTuner.Columns.Add("Code");
     tbl_CSMTuner.Columns.Add("XfundCode");
    
     ds.ReadXml(QCViewPath_savepath);
     dtQCViewAll = ds.Tables[0];
     dr_CSMTuner = null;
     for (int iRow = 0; iRow < dtQCViewAll.Rows.Count; iRow++)
     {
         strBroker = Convert.ToString(dtQCViewAll.Rows[iRow]["Section "]);
         strSection = Convert.ToString(dtQCViewAll.Rows[iRow]["Section "]);
         strLineItem = Convert.ToString(dtQCViewAll.Rows[iRow]["LineItem"]);
         strReviseDate = Convert.ToString(dtQCViewAll.Rows[iRow]["Revise Date"]);
         strGroupKey = Convert.ToString(dtQCViewAll.Rows[iRow]["GroupKey"]);
         strGroup_Section = strGroupKey.Split('~')[0];
         strGroup_LineItem = strGroupKey.Split('~')[1];
         strGroup_BMNBM = strGroupKey.Split('~')[3];
         strXFundCode = strGroupKey.Split('~')[2];
    
         #region "NBM"
         for (int iCol = 0; iCol < dtQCViewAll.Columns.Count; iCol++)
         {
    
             strPeriod = dtQCViewAll.Columns[iCol].ColumnName;
             strValue = dtQCViewAll.Rows[iRow][iCol].ToString();
             // For Line Item
             if (strSection.ToString().Trim().ToUpper() == strGroup_Section.ToString().Trim().ToUpper())
             {
                 strBroker = "";
             }
    
             if ((strPeriod.Contains("1Q")
                 || strPeriod.Contains("2Q")
                 || strPeriod.Contains("3Q")
                 || strPeriod.Contains("4Q")
                 || strPeriod.Contains("FY")
                 || strPeriod.Contains("1H")
                 || strPeriod.Contains("2H")) && strGroup_BMNBM == "NBM")
             {
                 strInsert_EarningID = PrevEarningID;
                 strInsert_Ticker = strTicker;
                 strInsert_Broker = strBroker;
                 strInsert_Section = strGroup_Section;
                 strInsert_LineItem = strGroup_LineItem;
                 strInsert_Period = strPeriod;
                 strInsert_ItemValue = strValue;
                 strInsert_BM_NBM = strGroup_BMNBM;
                 strInsert_BM_Element = "";
                 strInsert_File_date = strReviseDate;
                 strInsert_XFundCode = strXFundCode;
    
                 if (strInsert_ItemValue.GetWholeNumber().Length <= 18)
                 {
                     dr_CSMTuner = tbl_CSMTuner.NewRow();
                     dr_CSMTuner["OrderID"] = iOrderID;
                     iOrderID = iOrderID + 1;
                     dr_CSMTuner["EarningID"] = strInsert_EarningID;
                     dr_CSMTuner["Ticker"] = strInsert_Ticker;
                     dr_CSMTuner["Broker"] = strInsert_Broker;
                     dr_CSMTuner["Section"] = strInsert_Section;
                     dr_CSMTuner["LineItem"] = strInsert_LineItem;
                     dr_CSMTuner["Period"] = strInsert_Period;
                     dr_CSMTuner["ItemValue"] = strInsert_ItemValue;
                     if (strInsert_ItemValue.Contains("%") == true)
                     {
                         dr_CSMTuner["ItemValue_NoFormat"] = (Convert.ToDecimal(strInsert_ItemValue.GetWholeNumber()) / 100).ToString();
                     }
                     else
                     {
                         dr_CSMTuner["ItemValue_NoFormat"] = strInsert_ItemValue.GetWholeNumber();
                     }
                     dr_CSMTuner["BM_NBM"] = strInsert_BM_NBM;
                     dr_CSMTuner["BM_Element"] = strInsert_BM_Element;
    
                     if (strInsert_File_date.Trim() != "")
                         dr_CSMTuner["File_date"] = DateTime.Parse(strInsert_File_date).Date.ToString("MM-dd-yyyy");
                     else
                         dr_CSMTuner["File_date"] = string.Empty;
    
                     dr_CSMTuner["Code"] = "";
                     dr_CSMTuner["XfundCode"] = strInsert_XFundCode;
    
                     tbl_CSMTuner.Rows.Add(dr_CSMTuner);
    
                     strInsert_EarningID = "";
                     strInsert_Ticker = "";
                     strInsert_Broker = "";
                     strInsert_Section = "";
                     strInsert_LineItem = "";
                     strInsert_Period = "";
                     strInsert_ItemValue = "";
                     strInsert_BM_NBM = "";
                     strInsert_BM_Element = "";
                     strInsert_File_date = "";
                     strInsert_XFundCode = "";
                 }
             }
         }
         #endregion
     }
dotnet-csharp
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi TZacks-2728,
You can try to use Parallel.For method instead of for loop which iterations may run in parallel.
The code looks like below

 Parallel.For(0, table.Rows.Count, rowIndex => {
     var row = table.Rows[rowIndex];
     // put your per-row here
 });

Best Regards,
Daniel Zhang


0 Votes 0 ·

i did not use Parallel.For because there is high chance to overlap the value. the datatable tbl_CSMTuner where i am adding data which is global table. please suggest how to use Parallel.For in my scenario where working with global datatable for adding rows. if i use lock mechanism then parallel.for would make no sense. please suggest something good for my code. thanks

0 Votes 0 ·

Hi @TZacks-2728,
Based on this situation, you can refer to DuaneArnold-0443's answer. Traversing the data table is much slower than traversing a collection of custom objects (such as the List<T> of objects).
Best Regards,
Daniel Zhang

0 Votes 0 ·
DuaneArnold-0443 avatar image
0 Votes"
DuaneArnold-0443 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cheong00 avatar image
0 Votes"
cheong00 answered

Consider employing LocalDB or ACE Engine to store/filter your data if the dataset is huge and would be accessed in global scope.

IMO no filtering technique would be faster than properly configured index.

If not, then you can store your data in Dictionary of DataRow, and then mimic index by having multiple list of rowIdentifier (GUID or Tuple of all keys that constitutes as primary key of data) that contains the identifier of all rows that matches your pre-built criteria. When user select that criteria then you load data with that list and calling Dictionary[rowIdentifier]

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.