none
How to speed up my iteration in list of data and finding data in another datatable RRS feed

  • Question

  • see my code first

            private void UpdateCommentFirst(string strCommentPath, string TickerName)
            {
                int counter = 0;
                bool QcViewAllFileExist = false;
                bool QcCommentFileExist = false;
                bool AllowUpdate = false;
                string savepath = Convert.ToString(ConfigurationManager.AppSettings["OutputPath"]).Trim() + TickerName + "\\" + TickerName + "_QC-ViewwAll.xml";
                DataSet QCCommentstmp = new DataSet();
                DataSet QCViewAlltmp = new DataSet();
    
                if (File.Exists(strCommentPath))
                {
                    QCCommentstmp.ReadXml(strCommentPath);
                    QcCommentFileExist = true;
                }
    
                if (File.Exists(savepath))
                {
                    QCViewAlltmp.ReadXml(savepath);
                    QcViewAllFileExist = true;
                }
    
                if (QcCommentFileExist && QcViewAllFileExist)
                {
                    if (QCCommentstmp.Tables.Count > 0)
                    {
                        if (!QCCommentstmp.Tables[0].Columns.Contains("IgnoreData"))
                        {
                            AllowUpdate = true;
                        }
                    }
    
                    if (AllowUpdate)
                    {
    
                        List<clsCommentPopup> QCCommentlist = QCCommentstmp.Tables[0].AsEnumerable()
                            .Select(row => new clsCommentPopup
                            {
                                //BrokerFor,Formula,LineItem,Section,PeriodCollection
                                bolFollowUP = (row.Field<string>("FollowUP")) == null ? false : Convert.ToBoolean((row.Field<string>("FollowUP"))),
                                bolThisPeriod = (row.Field<string>("ThisPeriod")) == null ? false : Convert.ToBoolean((row.Field<string>("ThisPeriod"))),
                                Formula = (row.Field<string>("Formula")) == null ? string.Empty : (row.Field<string>("Formula")),
                                ModelValue = (row.Field<string>("ModelValue")) == null ? string.Empty : (row.Field<string>("ModelValue")),
                                ExternalComment = (row.Field<string>("ExternalComment")) == null ? string.Empty : (row.Field<string>("ExternalComment")),
                                InternalComment = (row.Field<string>("InternalComment")) == null ? string.Empty : (row.Field<string>("InternalComment")),
                                strEndPeriod = (row.Field<string>("EndPeriod")) == null ? string.Empty : (row.Field<string>("EndPeriod")),
                                strStartPeriod = (row.Field<string>("StartPeriod")) == null ? string.Empty : (row.Field<string>("StartPeriod")),
                                PeriodType = (row.Field<string>("PeriodType")) == null ? string.Empty : (row.Field<string>("PeriodType")),
                                SectionFor = (row.Field<string>("Section")) == null ? string.Empty : (row.Field<string>("Section")),
                                LiFor = (row.Field<string>("LineItem")) == null ? string.Empty : (row.Field<string>("LineItem")),
                                QcPeriodFor = (row.Field<string>("QcPeriod")) == null ? string.Empty : (row.Field<string>("QcPeriod")),
                                BrokerFor = (row.Field<string>("BrokerFor")) == null ? string.Empty : (row.Field<string>("BrokerFor")),
                                PeriodCollection = (row.Field<string>("PeriodCollection")) == null ? string.Empty : (row.Field<string>("PeriodCollection")),
                                boolIgnoreValue = (row.Field<string>("IgnoreValue")) == null ? false : Convert.ToBoolean((row.Field<string>("IgnoreValue"))),
                                IgnoreData = (!QCCommentstmp.Tables[0].Columns.Contains("IgnoreData") ? string.Empty : (row.Field<string>("IgnoreData") == null ? string.Empty : row.Field<string>("IgnoreData")))
                            }).ToList();
    
    
                        if (QCCommentlist != null)
                        {
                            foreach (var comment in QCCommentlist)
                            {
                                string section = comment.SectionFor;
                                string li = comment.LiFor;
                                string broker = comment.BrokerFor;
                                string period = comment.PeriodCollection;
                                string strQCPeriodValue = "";
    
                                if (comment.boolIgnoreValue && period.Trim() != "")
                                {
                                    var QcViewColumnName = QCViewAlltmp.Tables[0].Columns.Cast<DataColumn>().AsParallel()
                                      .Where(x => x.ColumnName.Contains(period))
                                      .Select(x => new { x.ColumnName }).FirstOrDefault();
    
                                    if (QcViewColumnName != null)
                                    {
                                        period = QcViewColumnName.ColumnName;
    
                                        if (period.Trim() != "")
                                        {
                                            var datarow = QCViewAlltmp.Tables[0].AsEnumerable().AsParallel()
                                                .Where(row => row.Field<string>("GroupKey").Split('~')[0].ToUpper() == section.ToUpper()
                                                && row.Field<string>("GroupKey").Split('~')[1].ToUpper() == li.ToUpper()
                                                && row.Field<string>("Section ").ToUpper() == broker.ToUpper());
    
                                            if (datarow != null && datarow.Count() > 0)
                                            {
                                                strQCPeriodValue = (datarow.FirstOrDefault()[period] != null ? datarow.FirstOrDefault()[period].ToString() : string.Empty);
                                                if (strQCPeriodValue.Trim() != string.Empty)
                                                {
                                                    comment.IgnoreData = strQCPeriodValue;
                                                    counter++;
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
    
                        SerializeQcComment(QCCommentlist);
                        toolTip1.Hide(this);
                    }
                }
            }
    

    1) loading data from xml file into dataset.

    2) if ignoredata field is there in QCCommentstmp dataset table  QCCommentstmp.Tables[0].Columns.Contains("IgnoreData") then deserialize QCCommentstmp table data to list List<clsCommentPopup> QCCommentlist

    3) iterate in QCCommentlist data for loop and finding data in QCViewAlltmp.Tables[0]

    4) when QCCommentlist  has 25000 data then i am iterating in all 25000 data and finding data in another datatable. if data found then i am updating data in list. this process is getting very slow and code is taking long time to finish all the iteration and searching data in datatable.

    please review my code and tell me how to restructure my code as a result there will be improvement in code execution speed.

    if my approach is wrong then guide me with right approach and also give me relevant code which i can use in my above code as a result my routine will take minimum time to finish if i iterate in more than 25000 data. looking for suggestion & better code to achieve the same task. thanks

    Saturday, April 6, 2019 7:59 PM

Answers

  • Hi Sudip,

    Something will probably help would be to declare all those string variables outside of the loop, rather than re-declaring them each time the loop is executed:

    if (QCCommentlist != null)
    {
        string section, li, broker, period, strQCPeriodValue;
    
        foreach (var comment in QCCommentlist)
        {
            section = comment.SectionFor;
            li = comment.LiFor;
            broker = comment.BrokerFor;
            period = comment.PeriodCollection;
            strQCPeriodValue = "";
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Marked as answer by Sudip_inn Monday, April 8, 2019 7:39 PM
    Sunday, April 7, 2019 5:06 AM
    Moderator

All replies

  • Hi Sudip,

    Something will probably help would be to declare all those string variables outside of the loop, rather than re-declaring them each time the loop is executed:

    if (QCCommentlist != null)
    {
        string section, li, broker, period, strQCPeriodValue;
    
        foreach (var comment in QCCommentlist)
        {
            section = comment.SectionFor;
            li = comment.LiFor;
            broker = comment.BrokerFor;
            period = comment.PeriodCollection;
            strQCPeriodValue = "";
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Marked as answer by Sudip_inn Monday, April 8, 2019 7:39 PM
    Sunday, April 7, 2019 5:06 AM
    Moderator
  • i do not thik so. i guess if i could equi join QCCommentstmp and QCViewAlltmp these two. one is list and another is datatable. i need to update one list like this way

     var list1 = new List<ItemOne>
                            {
                                new ItemOne {IDItem = 1, OneProperty = "1"},
                                new ItemOne {IDItem = 2, OneProperty = null},
                                new ItemOne {IDItem = 3, OneProperty = "3"},
                                new ItemOne {IDItem = 4, OneProperty = "4"}
                            };
            var list2 = new List<ItemTwo>
                            {
                                new ItemTwo {IDItem = 2, TwoProperty = "2"},
                                new ItemTwo {IDItem = 3, TwoProperty = "3"},
                            };
    
    
            var query = list1.Join(list2, l1 => l1.IDItem, l2 => l2.IDItem, (l1, l2) =>
            {
                l1.OneProperty = l2.TwoProperty;
                return l1;
            });

    https://stackoverflow.com/a/709576/10839668

    https://codereview.stackexchange.com/a/11021/197025

    https://stackoverflow.com/a/40460254/10839668





    • Edited by Sudip_inn Sunday, April 7, 2019 11:38 AM
    Sunday, April 7, 2019 10:52 AM
  • Hi Sudip,

    You marked my reply as an answer … I thought you had said earlier that you didn't think moving those string declarations outside of your loop was going to help much.

    I assumed that you had tried it and it did not help much. But, now that you've marked it as an answer, I'm curious by how much it actually improved the execution speed?


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, April 9, 2019 1:56 PM
    Moderator
  • the point you suggested was right and that is why i marked your answer. my scenario need to handle totally different way i guess.

    i need to left join between this two QCCommentlist & QCViewAlltmp.Tables[0]
    QCCommentlist  is list and QCViewAlltmp.Tables[0] is datatable.

    join condition would be like

    var datarow = QCViewAlltmp.Tables[0].AsEnumerable().AsParallel()
                                                .Where(row => row.Field<string>("GroupKey").Split('~')[0].ToUpper() == section.ToUpper()
                                                && row.Field<string>("GroupKey").Split('~')[1].ToUpper() == li.ToUpper()
                                                && row.Field<string>("Section ").ToUpper() == broker.ToUpper());
    please help if possible. thanks

    Tuesday, April 9, 2019 7:21 PM
  • I see that that you have other threads here asking about using Parallel in your LINQ queries, and unfortunately I'm probably not the one to answer Parallel query questions. You've probably gotten better answers in your other question threads … and since I know very little about it, I don't think I'll contribute anything to this particular thread.

    Besides, your initial question when you first posted this thread did not involve Parallel queries.

    Sorry Sudip!  =0(


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Wednesday, April 10, 2019 1:23 AM
    Moderator