C# How to optimize my for loop when iterate millions data

Question

I am iterating in 2509693 data in for loop which is taking long time. i am using VS2013 and .Net v4.5.2

so see my code and suggest some approach or changes for my existing code which speed up execution of my for loop.

This ds.Tables[1] has 2509693 data please guide me how to restructure & speed up below code. Thanks

public static List<ElementHierarchy> GetElementHierarchy(DataSet ds)
{
    List<ElementHierarchy> _ElmHierarchy = new List<ElementHierarchy>();

    string StrPrevDisplayInCSM = "", DisplayInCSM = "", Section = "", LineItem = "", LastGroupName = "", BGColor="",
        BlueMatrix1stElementFormulaText = "", Type = "", Period = "", EarningsType = "", ParentGroup = "", HeadingSubheading = "", Box="";
    int row = 6, EarningID = 0, LineItemID = 0, BMID = 0, ID = 0, ParentID=0;
    bool IsNextElementGroup = false;
    List<ListOfSection> lstData = new List<ListOfSection>();
    bool IsGreenHeader = false;

    for (int p = 0; p <= ds.Tables[1].Rows.Count - 1; p++)
    {
        ID = Convert.ToInt32(ds.Tables[1].Rows[p]["ID"].ToString());
        ParentID = Convert.ToInt32(ds.Tables[1].Rows[p]["ParentID"].ToString());

        EarningID = 0; 
        Section = (ds.Tables[1].Rows[p]["Section"] == DBNull.Value ? "" : ds.Tables[1].Rows[p]["Section"].ToString());
        LineItem = (ds.Tables[1].Rows[p]["LineItem"] == DBNull.Value ? "" : ds.Tables[1].Rows[p]["LineItem"].ToString());
        DisplayInCSM = ds.Tables[1].Rows[p]["DisplayInCSM"].ToString();
        Type = ds.Tables[1].Rows[p]["Type"].ToString();
        BlueMatrix1stElementFormulaText = (ds.Tables[1].Rows[p]["BlueMatrix1stElementFormulaText"] == null 
            ? "" : ds.Tables[1].Rows[p]["BlueMatrix1stElementFormulaText"].ToString());
        Period = (ds.Tables[1].Rows[p]["Period"] == DBNull.Value ? "" : ds.Tables[1].Rows[p]["Period"].ToString());
        HeadingSubheading = (ds.Tables[1].Rows[p]["HeadingSubheading"] == null ? "" : ds.Tables[1].Rows[p]["HeadingSubheading"].ToString());
        Box = (ds.Tables[1].Rows[p]["Box"] == DBNull.Value ? "" : ds.Tables[1].Rows[p]["Box"].ToString());
        LineItemID = Convert.ToInt32(ds.Tables[1].Rows[p]["LineItemID"].ToString());
        BMID = Convert.ToInt16(ds.Tables[1].Rows[p]["BMID"].ToString());
        BGColor = (ds.Tables[1].Rows[p]["BGColor"] == null ? "" : ds.Tables[1].Rows[p]["BGColor"].ToString());


        if (BGColor.Contains("ff003300"))
        {
            IsGreenHeader = true;
        }
        else
        {
            IsGreenHeader = false;
        }

        if (StrPrevDisplayInCSM != "" && StrPrevDisplayInCSM != DisplayInCSM && (Type == "LINEITEM" || Type=="BM"))
        {
            row++;
        }

        if (Type == "GROUP")
        {
            if (IsNextElementGroup)
            {
                row++;
            }
            else if (p > 0 && !IsNextElementGroup)
            {
                row++;

                if (p > 0 && HeadingSubheading=="H")
                {
                    row++;
                }

                if (p > 0 && HeadingSubheading == "S")
                {
                    row++;
                }
            }
            else if (p > 0 && IsGreenHeader)
            {
                row++;
            }
            else if (p > 0 && ds.Tables[1].AsEnumerable().Any(a => a.Field<int>("ParentID") == ID && a.Field<string>("Type") == "GROUP"))
            {
                row++;
            }

            ParentGroup = DisplayInCSM;

            if (HeadingSubheading != "")
            {
                if (HeadingSubheading == "H")
                {
                    if (Box != "Y")
                    {

                    }
                }
            }

            if(IsGreenHeader)
            {
                row++;
            }
            else if (ds.Tables[1].AsEnumerable().Any(a => a.Field<int>("ParentID") == ID && a.Field<string>("Type")=="GROUP"))
            {
                row++;
            }
            IsNextElementGroup = true;

        }
        else if (Type == "LINEITEM")
        {
            if (!lstData.Any(a =>
                a.Section == Section
                && a.LineItem == LineItem
                && a.Parent == ParentGroup
                && a.DisplayINCSM == DisplayInCSM
                && a.EarningsID == EarningID
                && a.EarningsType == EarningsType
                && a.Period == Period
                ))
            {

                if (!_ElmHierarchy.Any(z => z.RowIndex == row))
                {
                    _ElmHierarchy.Add(new ElementHierarchy
                    {
                        ID=ID,
                        ParentID=ParentID,
                        RowIndex = row,
                        Section = Section,
                        Lineitem = LineItem,
                        Type = "LI",
                        DisplayInCSM = DisplayInCSM,
                        BMFormula = "",
                        LineitemID = LineItemID,
                        BMID = 0
                    });
                }

                lstData.Add(new ListOfSection
                {
                    Section = Section,
                    LineItem = LineItem,
                    DisplayINCSM = DisplayInCSM,
                    Parent = ParentGroup,
                    EarningsID = EarningID,
                    EarningsType = EarningsType,
                    Period = Period
                });
            }
            IsNextElementGroup = false;
            IsGreenHeader = false;
        }
        else if (Type == "BM")
        {
            IsNextElementGroup = false;
            IsGreenHeader = false;

            if (!lstData.Any(a =>
                a.Section == Section
                && a.LineItem == LineItem
                && a.Parent == ParentGroup
                && a.DisplayINCSM == DisplayInCSM
                && a.EarningsID == EarningID
                && a.EarningsType == EarningsType
                && a.Period == Period
                ))
            {
                if (!_ElmHierarchy.Any(z => z.RowIndex == row))
                {
                    _ElmHierarchy.Add(new ElementHierarchy
                    {
                        ID = ID,
                        ParentID = ParentID,
                        RowIndex = row,
                        Section = Section,
                        Lineitem = LineItem,
                        Type = "BM",
                        DisplayInCSM = DisplayInCSM,
                        BMFormula = BlueMatrix1stElementFormulaText,
                        LineitemID = 0,
                        BMID = BMID
                    });
                }

                lstData.Add(new ListOfSection
                {
                    Section = Section,
                    LineItem = LineItem,
                    DisplayINCSM = DisplayInCSM,
                    Parent = ParentGroup,
                    EarningsID = EarningID,
                    EarningsType = EarningsType,
                    Period = Period
                });
            }
        }

        StrPrevDisplayInCSM = DisplayInCSM;
    }
    return _ElmHierarchy;
}


public class ListOfSection
{
    public string Parent { get; set; }
    public int EarningsID { get; set; }
    public string EarningsType { get; set; }
    public string Section { get; set; }
    public string LineItem { get; set; }
    public string DisplayINCSM { get; set; }
    public string Period { get; set; }
}

Answer 1

Note that the forum software is not properly handling syntax with [] but if you click on the links you will see which property I am referring to.

See DataRow Class. You are using the string version, DataRow.Item, very much. That is convenient for you but when you need to make it easier for the system use the index version DataRow.Item. You need to determine what the correct index is but then it will relieve the system from the requirement of looking it up each time. I think you can also use DataRowExtensions.Field (the index version) instead of DataRowExtensions.Field (the string version).

Also, the loop is doing Convert.ToInt32 twice each iteration. That probably does not require much time but if there is a way you can avoid converting from text to an integer then that might help a little.

Another possibility is to learn Entity Framework. It will take time to learn but after the investment it will be more efficient for both you and the system. It will generate more efficient code and it is easier for you and others.

Answer 2

It was a little hard to follow the code. I extracted some pieces to make it easier to follow, but I don't have your classes to verify that it actually builds and compiles. At this point, there probably aren't any optimizations in here, but if you can trim the logic down like this, it makes it easier to see where optimization can happen. Once I was able to reduce it a bit, I could see a few places that were executing needlessly so I either commented it out or refactored it slightly. I feel like there are more optimizations available, but again without a real instance to test against, it is a little difficult to see. Also, it might be a good idea to implement some of DuaneArnold-0443's suggestions.

public static List<ElementHierarchy> GetElementHierarchy(DataSet ds)
    {
        List<ElementHierarchy> _ElmHierarchy = new List<ElementHierarchy>();

        var pr = new PRecord();
        List<ListOfSection> lstData = new List<ListOfSection>();
        bool IsGreenHeader = false;

        DataRowCollection drc = ds.Tables[1].Rows;
        for (int p = 0; p <= drc.Count - 1; p++)
        {
            pr.LoadRecord(drc[p]);

            IsGreenHeader = pr.BGColor.Contains("ff003300");

            if (pr.StrPrevDisplayInCSM != string.Empty && pr.StrPrevDisplayInCSM != pr.DisplayInCSM && (pr.Type == "LINEITEM" || pr.Type == "BM"))
            {
                pr.Row++;
            }

            if (pr.Type == "GROUP")
            {
                if (pr.IsNextElementGroup)
                {
                    pr.Row++;
                }
                else if (p > 0 && !pr.IsNextElementGroup)
                {
                    pr.Row++;

                    if (p > 0 && (pr.HeadingSubheading == "H" || pr.HeadingSubheading == "S"))
                    {
                        pr.Row++;
                    } 
                }
                else if (p > 0 && IsGreenHeader)
                {
                    pr.Row++;
                }
                else if (p > 0 && ds.Tables[1].AsEnumerable().Any(a => a.Field<int>("ParentID") == pr.ID && a.Field<string>("Type") == "GROUP"))
                {
                    pr.Row++;
                }

                pr.ParentGroup = pr.DisplayInCSM;

                // this block ultimately does nothing, so why have it?
                //if (pr.HeadingSubheading != "")
                //{
                //    if (pr.HeadingSubheading == "H")
                //    {
                //        if (pr.Box != "Y")
                //        {

                //        }
                //    }
                //}

                if (IsGreenHeader)
                {
                    pr.Row++;
                }
                else if (ds.Tables[1].AsEnumerable().Any(a => a.Field<int>("ParentID") == pr.ID && a.Field<string>("Type") == "GROUP"))
                {
                    pr.Row++;
                }
                pr.IsNextElementGroup = true;

            }
            else if (pr.Type == "LINEITEM")
            {
                if (!lstData.Any(a =>
                    a.Section == pr.Section
                    && a.LineItem == pr.LineItem
                    && a.Parent == pr.ParentGroup
                    && a.DisplayINCSM == pr.DisplayInCSM
                    && a.EarningsID == pr.EarningID
                    && a.EarningsType == pr.EarningsType
                    && a.Period == pr.Period
                    ))
                {

                    if (!_ElmHierarchy.Any(z => z.RowIndex == pr.Row))
                    {
                        _ElmHierarchy.Add(new ElementHierarchy
                        {
                            ID = pr.ID,
                            ParentID = pr.ParentID,
                            RowIndex = pr.Row,
                            Section = pr.Section,
                            Lineitem = pr.LineItem,
                            Type = "LI",
                            DisplayInCSM = pr.DisplayInCSM,
                            BMFormula = "",
                            LineitemID = pr.LineItemID,
                            BMID = 0
                        });
                    }

                    lstData.Add(new ListOfSection
                    {
                        Section = pr.Section,
                        LineItem = pr.LineItem,
                        DisplayINCSM = pr.DisplayInCSM,
                        Parent = pr.ParentGroup,
                        EarningsID = pr.EarningID,
                        EarningsType = pr.EarningsType,
                        Period = pr.Period
                    });
                }
                pr.IsNextElementGroup = false;
                IsGreenHeader = false;
            }
            else if (pr.Type == "BM")
            {
                pr.IsNextElementGroup = false;
                IsGreenHeader = false;

                if (!lstData.Any(a =>
                    a.Section == pr.Section
                    && a.LineItem == pr.LineItem
                    && a.Parent == pr.ParentGroup
                    && a.DisplayINCSM == pr.DisplayInCSM
                    && a.EarningsID == pr.EarningID
                    && a.EarningsType == pr.EarningsType
                    && a.Period == pr.Period
                    ))
                {
                    if (!_ElmHierarchy.Any(z => z.RowIndex == pr.Row))
                    {
                        _ElmHierarchy.Add(new ElementHierarchy
                        {
                            ID = pr.ID,
                            ParentID = pr.ParentID,
                            RowIndex = pr.Row,
                            Section = pr.Section,
                            Lineitem = pr.LineItem,
                            Type = "BM",
                            DisplayInCSM = pr.DisplayInCSM,
                            BMFormula = pr.BlueMatrix1stElementFormulaText,
                            LineitemID = 0,
                            BMID = pr.BMID
                        });
                    }

                    lstData.Add(new ListOfSection
                    {
                        Section = pr.Section,
                        LineItem = pr.LineItem,
                        DisplayINCSM = pr.DisplayInCSM,
                        Parent = pr.ParentGroup,
                        EarningsID = pr.EarningID,
                        EarningsType = pr.EarningsType,
                        Period = pr.Period
                    });
                }
            }

            pr.StrPrevDisplayInCSM = pr.DisplayInCSM;
        }
        return _ElmHierarchy;
    }

public class PRecord
{
    public PRecord()
    {

    }
    public string StrPrevDisplayInCSM { get; set; }
    public string DisplayInCSM { get; set; }
    public string Section { get; set; }
    public string LineItem { get; set; }
    public string LastGroupName { get; set; }
    public string BGColor { get; set; }
    public string BlueMatrix1stElementFormulaText { get; set; }
    public string Type { get; set; }
    public string Period { get; set; }
    public string EarningsType { get; set; }
    public string ParentGroup { get; set; }
    public string HeadingSubheading { get; set; }
    public string Box { get; set; }
    public int Row { get; set; } = 6;
    public int EarningID { get; set; } = 0;
    public int LineItemID { get; set; } = 0;
    public int BMID { get; set; } = 0;
    public int ID { get; set; } = 0;
    public int ParentID { get; set; } = 0;
    public bool IsNextElementGroup { get; set; } = false;

internal void LoadRecord(DataRow dataRow)
{
        ID = Convert.ToInt32(dataRow["ID"].ToString());
        ParentID = Convert.ToInt32(dataRow["ParentID"].ToString());
        EarningID = 0;
        Section = (dataRow["Section"] == DBNull.Value ? string.Empty : dataRow["Section"].ToString());
        LineItem = (dataRow["LineItem"] == DBNull.Value ? string.Empty : dataRow["LineItem"].ToString());
        DisplayInCSM = dataRow["DisplayInCSM"].ToString();
        Type = dataRow["Type"].ToString();
        BlueMatrix1stElementFormulaText = (dataRow["BlueMatrix1stElementFormulaText"] == null ? string.Empty : dataRow["BlueMatrix1stElementFormulaText"].ToString());
        Period = (dataRow["Period"] == DBNull.Value ? string.Empty : dataRow["Period"].ToString());
        HeadingSubheading = (dataRow["HeadingSubheading"] == null ? string.Empty : dataRow["HeadingSubheading"].ToString());
        Box = (dataRow["Box"] == DBNull.Value ? string.Empty : dataRow["Box"].ToString());
        LineItemID = Convert.ToInt32(dataRow["LineItemID"].ToString());
        BMID = Convert.ToInt16(dataRow["BMID"].ToString());
        BGColor = (dataRow["BGColor"] == null ? string.Empty : dataRow["BGColor"].ToString());
    }

}

public class ListOfSection
{
    public string Parent { get; set; }
    public int EarningsID { get; set; }
    public string EarningsType { get; set; }
    public string Section { get; set; }
    public string LineItem { get; set; }
    public string DisplayINCSM { get; set; }
    public string Period { get; set; }
}

Answer 3

Your speed problem is the datatable. Maybe, you should consider using a List<T> instead of the datatable if you want to improve performance.

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