How to speed up my loops

Question

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
    }

Answer 1

IMHO, if you want to speed things up, then stop using the data table, use a custom object/class and use a list/collection.

https://dzone.com/articles/reasons-move-datatables

https://www.codingblocks.net/programming/boxing-and-unboxing-7-deadly-sins/

http://lauteikkehn.blogspot.com/2012/03/datatable-vs-list.html

https://www.monitis.com/blog/improving-net-application-performance-part-14-collections/

https://learn.microsoft.com/en-us/dotnet/csharp/programming-guide/classes-and-structs/auto-implemented-properties

Answer 2

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]