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

[Sudip_inn]

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

Continue reading...