|
I have a list which has 25000 data and I am iterating in all data and in each iteration, I am searching data in another data table. my whole routine is taking a long time to finish.
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
{
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);
}
}
}
Loading data from XML file into a dataset.
If the ignoredata field is there in QCCommentstmp dataset table
QCCommentstmp.Tables[0].Columns.Contains("IgnoreData") then deserialize QCCommentstmp table data to list List<clscommentpopup> QCCommentlist.
Iterate in QCCommentlist's data using a for loop and finding data in QCViewAlltmp.Tables[0] data table for each iteration.
When QCCommentlist has 25000 data then I am iterating in all 25000 data and finding data in another data table. If data is found then I am updating data in the list. This process is getting very slow and the code is taking a long time to finish all the iteration and searching data in the data table.
Please review my code and tell me how to restructure my code, as a result, there will be the improvement in code execution speed.
If my approach is wrong then guide me with the right approach and also give me the 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. I'm looking for suggestions and better code to achieve the same task. thanks
|
|
|
|
|
Hi,
I'm no expert in LINQ, however I would avoid using it for searching as it is based on IEnumerable, so I expect a search to be basically sequential, and performance to degrade quadratically.
I would recommend first building a Dictionary<keyType, myType> with the search key as key; that would speed up your search considerably. Be careful though, all key values must be different when building the dictionary!
modified 7-Apr-19 22:15pm.
|
|
|
|
|
|
You're welcome.
|
|
|
|
|
Mou_kol wrote: I have a list which has 25000 data and I am iterating in all data and in each iteration, I am searching data in another data table. my whole routine is taking a long time to finish.
XML is not optimized for search, it is an exchange-format. If you want speed, use a real database.
Also, the data needs to be normalized; having to do "Split.ToUpper" 25000 times is nonsense. Finding the data in the other table should be fast if you have the PK and or an indexed field.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
|
You're welcome.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
I define and set indexes (using an initial lookup) for the column names so my methods don't have to do "column lookups" and instead index columns directly in the for loops.
"(I) am amazed to see myself here rather than there ... now rather than then".
― Blaise Pascal
|
|
|
|
|
Hi again,
you should use a database, it might be a temporary one, one that gets created, used, and deleted by your app. You might consider SQLite (see e.g. Getting started with SQLite in C# – Tigran's Blog[^] or Working with SQLite in C# & VB[^] ) , which works with a single data file or can hold everything in memory (see In-Memory Databases[^]).
Have your program create the database, populate it once from the XML file, cleaning the data immediately. Then perform the required operations on the database.
|
|
|
|
|
|
I am not familiar with SQLLite db. so i have few questions. i will highly obliged if you please answer.
1) SQLLite support multi user ?
2) SQLLite is single db file like MS-Access ?
3) does it has high learning curve ?
4) if some one know sql server then he can user sql server related code and knowledge along with SQLLite or all things will be totally separate ?
5) SQLLite support table data indexing, view, store procedure & trigger like sql server ?
6) can we work just copy SQLLite db file from one pc to another pc or do i need to install setup in all pc ?
7) does it have any IDE by which can design table, view & store procedure.
very sorry that i have asked 7 questions in advance. i will be glad if you answer my point wise. Thanks in advance
|
|
|
|
|
Hi,
I only used SQlite a few times, in stand-alone single-user applications. It uses SQL, so standard database operations are very similar tothose on SQLServer.
I don't know all the answers to your questions, I do trust the articles I refered to do contain the necessary information, if not uncle Google knows everything.
However if you need multi-user support, persistent data, stored procedures, IDE design, and/or more, then why not use SQLServer right away? And I fail to see how your earlier use of an XML file fits the bill...
BTW: if you have architectural doubts, I think you'd better provide an overall description of what kind of application and system configuration you are considering, rather than asking a number of technical questions without giving much context...
|
|
|
|
|
i have list1, list2 & list3
i want to do left join between list1 & list2 and equi join between list1 & list3.
looking for sample code. i search google but always getting 2 join example irrespective of equi join or left join.
please share some sample code as per my scenario i discuss here. thanks
|
|
|
|
|
|
My problem is that when there are 50 or 100 iterations in for loop and doing repeated join between list & datatable, then my code takes long time to run about 10 minute.
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;
objBrokerCodeBrokerNameBrokerBogey.IsHistoric = (dtdgvBrokers.AsEnumerable().FirstOrDefault(x => x.Field<string>("Brokers").Equals(objbrokerBrogeyData.Broker)).Field<string>("Historical")).Equals("True") ? true : false;
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];
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"),
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.
Can you review it for performance and best coding practices?
|
|
|
|
|
Perhaps someone else might want to look at that in detail, but...
You are doing a loop and then doing database calls for each iteration. It will be slow because of that.
The solution is not to do a loop. Rather construct a single query that does everything at once.
Also you should note that there is no magic here and if your list gets 'big' then you will need to break the query up. If you are using MS SQL Server a 'in' clause has a limit of 2100 I believe.
|
|
|
|
|
my data store in xml file. i am not using any db. this is requirement of my module. thanks
|
|
|
|
|
Hi,
First of all, I agree with jschell.
Then, I haven't read all that, however I did notice you are wasting cycles by performing complex operations twice.
Instead of
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()) : ""; ;
which is hard to read and understand, you'd better do
string brokerBogeypath = Path.Combine(ConfigurationManager.AppSettings["DBPath"].ToString(), cmbTicker.Text, "###~$$$~Bogey.xml".Replace("###", cmbTicker.Text).Trim().Replace("$$$", objbrokerBrogeyData.Broker).Trim());
if (!File.Exists(brokerBogeypath)) brokerBogeypath="";
which is both more readable and faster.
Next, you have a lot of calls to ToUpper(), Trim(), and Replace(...); each of them will cost a fwe microseconds, which accumulates when executed a lot of times. You probably could reorganise them so they are each required only once, maybe at the expense of some extra storage.
Same for ConfigurationManager.AppSettings["DBPath"].ToString()
Finally, I would suggest shortening some of the identifiers in order to improve readability; this won't change execution speed however it may make it easier for you (or others) to see what is really going on...
|
|
|
|
|
|
You're welcome.
|
|
|
|
|
see the code first
var cfToggleList = (from cf in QCHelperall
join toggle in ToggleDataAll
on new { val = cf.Section.Trim().ToUpper(),
val1 = cf.Li.Trim().ToUpper(),
val2 = cf.Period.Replace("A", "").Replace("E", "").Trim().ToUpper(),
val3 = cf.Broker.Trim().ToUpper() }
equals new { val = toggle.Section.Trim().ToUpper(),
val1 = toggle.Lineitem.Trim().ToUpper(),
val2 = toggle.Period.Trim().ToUpper(),
val3 = toggle.Broker.Trim().ToUpper() }
into tempJoin
from leftJoin in tempJoin.DefaultIfEmpty()
select new QCHelper()
{
Broker = cf.Broker,
BrokerName = (from brk in BrokerCodeName
where brk.BrokerCode.ToUpper() == cf.Broker.Split('-')[0].ToUpper()
select brk.BrokerName).FirstOrDefault(),
Section = cf.Section,
Li = cf.Li,
StandardDate = cf.Period,
Value = cf.Value,
FycheckToggle = leftJoin == null ? string.Empty : (leftJoin.ToggleText.Contains("FYCHECKTOGGLE") ? leftJoin.ToggleText : string.Empty),
QcCheckToggle = leftJoin == null ? string.Empty : (leftJoin.ToggleText.Contains("QCCHECKTOGGLE") ? leftJoin.ToggleText : string.Empty)
}).ToList<QCHelper>();
There is left join between two list called QCHelperall & ToggleDataAll and for each iteration i am fetching data from another list based on match. i am talking about this code
BrokerName = (from brk in BrokerCodeName
where brk.BrokerCode.ToUpper() == cf.Broker.Split('-')[0].ToUpper()
select brk.BrokerName).FirstOrDefault(),
can i join BrokerCodeName with QCHelperall list with equi join. if possible then please give me code where one left join will be between QCHelperall & ToggleDataAll and one equi join between BrokerCodeName with QCHelperall in same place. i believe if i can do it then code execution will be must faster.
thanks
|
|
|
|
|
You forgot the kitchen sink.
"(I) am amazed to see myself here rather than there ... now rather than then".
― Blaise Pascal
|
|
|
|
|
sorry do not understand what you try to mean. please guide me how to restructure my code.
|
|
|
|
|
Look at all the calls to Trim and ToUpper you've got on nearly every field. This causes two string operations to be performed PER FIELD ON EVERY RECORD. Those operations take time and the allocation of new memory for the results of these operations. You've added a LOT of overhead just with those operations.
If you have to "normalize" your data in the query, the design of your database needs to be looked at and modified to normalize data during INSERT, not during the query. The design of the database should be done with querying performance in mind, not just a place to store data.
|
|
|
|
|
sir please guide me how to restructure my code as a result i will get best performance. thanks
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
