|
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; }
}
|
|
|
|
|
Which lines are taking the longest to execute?
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
I have no idea what you are trying to do or why you need to update so many items. However, Stop calling ToString on items that are already strings. And look at the following line (just one example of a few):
ID = Convert.ToInt32(ds.Tables[1].Rows[p]["ID"].ToString());
Why are you converting a value to a string, just so you can convert it to an integer?
But you need to understand that processing just over 2.5 million items will take some time. The laws of physics are immutable.
|
|
|
|
|
Richard is right:
Quote: Which lines are taking the longest to execute?
And so is Richard:
Quote: you need to understand that processing just over 2.5 million items will take some time.
The first part of any speed improvement exercise is to find out what you have: and that means timing your code to find out what takes a "long time" and what is "quick" - there is no point in trying to squeeze improvements out of fast code because that will make marginal difference; the slow code is teh bit that needs speeding up.
So start with the Stopwatch Class (System.Diagnostics) | Microsoft Docs[^] and start finding out where that code runs quick, and where it runs slow.
We can't do that for you: we don't have any access to your data, and it's likely to be very relevant to any speed analysis.
When you have that, you also have a metric for much improvement you are making as you go.
It's possible that you might be able to multithread it, to spread the load out over the whole processor - but that's not as simple as it may sound: although thinks like Parallel.Foreach exist, using threading blindly is as likely to slow down your whole computer as speed up processing, particularly when you start dealing with 2,000,000 items each of which probably don't take much time so the additional thread setup / processing overhead starts to overweigh the actual task.
You may get some good "throughput" improvements by splitting the for loop into a small number N of threads, each of which does 1/N th of the total task. But remember: List and DataTable are not considered thread safe, so you'll probably need to think very carefully before you start coding that if you don't want really hard to spot and track down data bugs.
As a general rule, I wouldn't recommend trying to use more threads than you have cores ...
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Since it involves a dataset / datatable, I'm guessing there's a database and an SQL query out there that you should be focusing on instead.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
I've had similar situations - large (but not a large as this requesters) datasets going to grid views and have timed them. In my cases, issuing the SQL and getting results was quite quick. The delays was filling in and displaying the grid view.
Things to try (which I admit I haven't actually tried in my cases)
* Switch off rendering of the form whilst writing data to the grid view and switch on once all of the grid view has been populated
* Do partial updates (e.g. write 1000 rows, update the UI, append 1000 more rows, update the UI, etc). This would actually slow down the changes but would appear to the user that things were happening and he / she could do some work with the displayed data whilst the rest was being processed. The only way I know of to do partial updates includes using Application.DoEvents which is not a good method; I am sure that someone here could suggest a better way.
OR: Target a subset of the data, Allow the user to request data by whatever criteria is relevant. We (by default) only supply the current day's data. Users soon learn that if they want more, they will have to ask for it (filling in more selection criteria fields), and they will have to wait longer as a consequence.
|
|
|
|
|
He's not displaying at this point ... he's doing "database operations" on the client.
But, yes, comparatively speaking, the UI is "slow", and will slow down your app if you do UI updates synchronously.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
Thanks - my mistake. It was the business about green headers etc that I had misinterpreted.
|
|
|
|
|