none
How to speed up my code where i am doing repeated join in for loop RRS feed

  • Question

  • see my code first and i will explain at end what i am doing.

                                    if (lst_Broker_BrokerBogey_collection.Any())
                                    {
                                        foreach (var objbrokerBrogeyData in lst_Broker_BrokerBogey_collection)
                                        {
    
                                            string brokerBogeypath = File.Exists(@Path.Combine(ConfigurationManager.AppSettings["DBPath"].ToString(), cmbTicker.Text, "###~$$$~Bogey.xml".Replace("###", cmbTicker.Text).Trim().Replace("$$$", objbrokerBrogeyData.Broker).Trim())) ?
                                            @Path.Combine(ConfigurationManager.AppSettings["DBPath"].ToString(), cmbTicker.Text, "###~$$$~Bogey.xml".Replace("###", cmbTicker.Text).Trim().Replace("$$$", objbrokerBrogeyData.Broker).Trim()) : ""; ;
                                            
                                            if (!String.IsNullOrEmpty(brokerBogeypath))
                                            {
                                                bool selectedBroker = (dtdgvBrokers.AsEnumerable().FirstOrDefault(x => x.Field<string>("Brokers").Equals(objbrokerBrogeyData.Broker)).Field<string>("Select")).Equals("True") ? true : false;
                                                if (selectedBroker)
                                                {
                                                    RDSS_Workbench.QcVerticalViewNew.QcVerticalViewNewProcess.BrokerCodeBrokerNameBrokerBogey objBrokerCodeBrokerNameBrokerBogey
                                                        = new QcVerticalViewNew.QcVerticalViewNewProcess.BrokerCodeBrokerNameBrokerBogey();
    
                                                    objBrokerCodeBrokerNameBrokerBogey.BrokerCode = objbrokerBrogeyData.Broker;
                                                    objBrokerCodeBrokerNameBrokerBogey.IsAllowEstimate = (dtdgvBrokers.AsEnumerable().FirstOrDefault(x => x.Field<string>("Brokers").Equals(objbrokerBrogeyData.Broker)).Field<string>("Select Estim.")).Equals("True") ? true : false; //objbrokerBrogeyData._willpassEstimatePeriods;
                                                    objBrokerCodeBrokerNameBrokerBogey.IsHistoric = (dtdgvBrokers.AsEnumerable().FirstOrDefault(x => x.Field<string>("Brokers").Equals(objbrokerBrogeyData.Broker)).Field<string>("Historical")).Equals("True") ? true : false; //objbrokerBrogeyData._followHistoricalData;
                                                    objBrokerCodeBrokerNameBrokerBogey.ReviseDate = (dtdgvBrokers.AsEnumerable().FirstOrDefault(x => x.Field<string>("Brokers").Equals(objbrokerBrogeyData.Broker)).Field<string>("Model Date"));
                                                    objBrokerCodeBrokerNameBrokerBogey.BrokerEarnings = (dtdgvBrokers.AsEnumerable().FirstOrDefault(x => x.Field<string>("Brokers").Equals(objbrokerBrogeyData.Broker)).Field<string>("Earnings"));
    
                                                    DataTable GeneralBrokerDt = new DataTable();
                                                    GeneralBrokerDt = objBrokerCodeBrokerNameBrokerBogey.brokerbogeycontainer.GetBrokerBogey(@brokerBogeypath).Tables[2]; //objBrokerCodeBrokerNameBrokerBogey.brokerbogeycontainer.GneralBorgeyDt.Tables[2];
    
                                                    List<QCHelper> qclist = new List<QCHelper>();
    
                                                    qclist = (from cf in cfToggleList
                                                              join broker in GeneralBrokerDt.AsEnumerable()
                                                           on new { val = cf.Section.Trim().ToUpper(), val1 = cf.Li.Trim().ToUpper(), val2 = cf.StandardDate.Replace("A", "").Replace("E", "").Trim().ToUpper() }
                                                           equals new { val = broker.Field<string>("TabName").Trim().ToUpper(), val1 = broker.Field<string>("StandardLineitem").Trim().ToUpper(), val2 = broker.Field<string>("StandardDate").Replace("A", "").Replace("E", "").Trim().ToUpper() }
                                                           into tempJoin
                                                              from leftJoin in tempJoin.DefaultIfEmpty()
                                                              where cf.Broker == objBrokerCodeBrokerNameBrokerBogey.BrokerCode
                                                              select new QCHelper()
                                                              {
                                                                  Broker = cf.Broker,
                                                                  BrokerName = cf.BrokerName,
                                                                  Section = cf.Section,
                                                                  BrokerEarnings = objBrokerCodeBrokerNameBrokerBogey.BrokerEarnings,
                                                                  Li = cf.Li,
                                                                  StandardDate = leftJoin == null ? cf.StandardDate : leftJoin.Field<string>("StandardDate"),
                                                                  CrossCalc1Q = cf.CrossCalc1Q,
                                                                  CrossCalc2Q = cf.CrossCalc2Q,
                                                                  CrossCalc3Q = cf.CrossCalc3Q,
                                                                  CrossCalc4Q = cf.CrossCalc4Q,
                                                                  CrossCalcFY = cf.CrossCalcFY,
                                                                  Value = cf.Value,
                                                                  QCFormula = cf.QCFormula,
                                                                  DataAsOfComment = cf.DataAsOfComment,
                                                                  BrokernameComment = cf.BrokernameComment,
                                                                  OnlyLiComment = cf.OnlyLiComment,
                                                                  SelectivePeriodComment = cf.SelectivePeriodComment,
                                                                  AvgComment = cf.AvgComment,
                                                                  IgnoreValue = cf.IgnoreValue,
                                                                  FormulaQcComment = cf.FormulaQcComment,
                                                                  AllowComma = cf.AllowComma,
                                                                  AllowedDecimalPlace = cf.AllowedDecimalPlace,
                                                                  AllowPercentageSign = cf.AllowPercentageSign,
                                                                  CurrencySign = cf.CurrencySign,
                                                                  IsQcCheck = cf.IsQcCheck,
                                                                  QcType = cf.QcType,
                                                                  FormulaLiConfig = cf.FormulaLiConfig,
                                                                  CustomFormula = cf.CustomFormula,
                                                                  Historical = cf.Historical,
                                                                  DeriveCrossCalc = cf.DeriveCrossCalc,
                                                                  FycheckToggle = cf.FycheckToggle,
                                                                  QcCheckToggle = cf.QcCheckToggle,
                                                                  xFundCode = cf.xFundCode,
                                                                  StandardValue = leftJoin == null ? string.Empty : leftJoin.Field<string>("StandardValue"),
                                                                  IsAllowEstimate = objBrokerCodeBrokerNameBrokerBogey.IsAllowEstimate,
                                                                  ReviseDate = objBrokerCodeBrokerNameBrokerBogey.ReviseDate
                                                              }).ToList<QCHelper>();
    
                                                    GeneralBrokerDt.Dispose();
                                                    GeneralBrokerDt = null;
                                                    objBrokerCodeBrokerNameBrokerBogey.brokerbogeycontainer.GneralBorgeyDt.Dispose();
                                                    objBrokerCodeBrokerNameBrokerBogey.brokerbogeycontainer.GneralBorgeyDt = null;
    
                                                    if (qclist.Count > 0)
                                                        lstBorkerWiseData.AddRange(qclist);
    
                                                    qclist = null;
                                                    objBrokerCodeBrokerNameBrokerBogey = null;
                                                }
                                            }
                                        }

    this is my list lst_Broker_BrokerBogey_collection in which i need to iterate. brokerBogeypath will have multiple different file location in loop.

    this line 

    bool selectedBroker = (dtdgvBrokers.AsEnumerable().FirstOrDefault(x => x.Field<string>("Brokers").Equals(objbrokerBrogeyData.Broker)).Field<string>("Select")).Equals("True") ? true : false;

    dtdgvBrokers is datagridview and in selectedBroker variable will have true false value based on grid select value.

    if selected broker has true value then load datatable GeneralBrokerDt. GetBrokerBogey() function will load data from different xml file in each for loop iteration.

    in next line 

    qclist = (from cf in cfToggleList
    join broker in GeneralBrokerDt.AsEnumerable()

    i am joining list cfToggleList and datatable GeneralBrokerDt

    my problem is when there 50 or 100 iteration in for loop and doing reprated join between list & datatable then my above code taking long time. so please guide me how to restructure my code which speed up the thing and take less time, the above code some time taking 10 minute.

    looking for guidance. thanks

    Saturday, April 6, 2019 7:13 PM

Answers

  • if i use parallel for in my code then does it increase performance ?

    https://www.dotnetperls.com/parallel-for

    A summary. With Parallel.For we can do certain methods in parallel. This does not always help, but in tasks where the CPU usage is intensive, and no dependencies are present, it may help.Parallel.Invoke

    https://www.c-sharpcorner.com/UploadFile/efa3cf/parallel-foreach-vs-foreach-loop-in-C-Sharp/

    I am giving 2 examples, in the first example traditional foreach loop is faster than Parallel.foreach loop where as in second example traditional foreach loop is very slow as compared to Parallel.foreach.

    • Marked as answer by Sudip_inn Monday, April 8, 2019 7:31 PM
    Sunday, April 7, 2019 11:04 PM

All replies

  • I don't see where you can improve performance, because you are using Linq-2-Objec. Linq-2-Entities where Entity Framework is involved, you could use a SQL Profiler to look at the generated T-SQL, changed the Linq query to generate better T-SQL and that could have given you better performance possibly.
    Saturday, April 6, 2019 7:43 PM
  • Its not easy to figure out whats causing the performance problem just by looking at the code. However this code is doing too many things in one place, I would suggest to break it down into smaller functions that can be called from within the loop. Data-tables are known for not being good for performance in general, but not sure what is the size of data you dealing with.

    Fouad Roumieh

    Saturday, April 6, 2019 8:09 PM
  • in the above code i have not used Entity Framework. i am just querying data by LINQ.
    Saturday, April 6, 2019 8:42 PM
  • in the above code i have not used Entity Framework. i am just querying data by LINQ.

    Yeah, you're using Linq-2-Object, and you are not using Linq-2-Entities. Linq-2-Entities would involve using Entity Framework where you could possibly speed up a Linq query. There is nothing you can do to speed up a Linq query against objects in a collection, which is Linq-2-Object queries. 

    https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/linq-to-objects

    https://www.entityframeworktutorial.net/querying-entity-graph-in-entity-framework.aspx

    Saturday, April 6, 2019 10:11 PM
  • please see my code and tell me can i use parallel for there instead of normal for?

    if i use parallel for then what are the area i need to keep inside lock to prevent any data overlap ?

    if i use parallel for in my code then does it increase performance ?

    should i use AsParallel in my above join...does it increase performance ?

    looking for your guide line. thanks

    Sunday, April 7, 2019 6:30 PM
  • please see my code and tell me can i use parallel for there instead of normal for?

    if i use parallel for then what are the area i need to keep inside lock to prevent any data overlap ?

    if i use parallel for in my code then does it increase performance ?

    should i use AsParallel in my above join...does it increase performance ?

    looking for your guide line. thanks

    Sunday, April 7, 2019 6:31 PM
  • if i use parallel for in my code then does it increase performance ?

    https://www.dotnetperls.com/parallel-for

    A summary. With Parallel.For we can do certain methods in parallel. This does not always help, but in tasks where the CPU usage is intensive, and no dependencies are present, it may help.Parallel.Invoke

    https://www.c-sharpcorner.com/UploadFile/efa3cf/parallel-foreach-vs-foreach-loop-in-C-Sharp/

    I am giving 2 examples, in the first example traditional foreach loop is faster than Parallel.foreach loop where as in second example traditional foreach loop is very slow as compared to Parallel.foreach.

    • Marked as answer by Sudip_inn Monday, April 8, 2019 7:31 PM
    Sunday, April 7, 2019 11:04 PM