Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Tuesday, December 18, 2018 3:00 PM
suppose i have bind my datatable to a 3rd party grid and after bind i am iterating in each row of that grid.
when i am iterating 7500 records then it is taking long time and i debug the code many time but do not understand what is the issue there which slow down the iteration. it is my request that it will be great help if some one look into this whole code and tell how to restructure the code to speed up iteration.
try
{
if (dtMain.Rows.Count > 0)
{
for (int r = 2; r <= totalrowcount - 1; r++)
{
lblrows.BeginInvoke(new Action(() => lblrows.Text = "Rows " + r.ToString()));
if (_colpos == 0)
{
for (int j = 0; j <= numberOfDatColumns - 1; j++)
{
// finding out the column position
if (sheet[0, j] != null)
{
if (sheet[0, j].ToString().ToUpper() == "GROUPKEY")
{
_colpos = j;
break;
}
}
}
}
var lastcolname = GetExcelColumnName(_colpos + 1);
if (sheet[lastcolname + (r + 1)] != null)
{
if (sheet["B" + r] + sheet[lastcolname + r].ToString().Split('~')[5].Trim() == sheet["B" + (r + 1)] + sheet[lastcolname + (r + 1)].ToString().Split('~')[5].Trim())
{
if (startrow == "")
{
startrange = r;
startrow = r.ToString();
}
}
else
{
endrange = r;
_section = sheet["A" + r].ToString();
lineitem = sheet["B" + r].ToString();
var formattingdata = objQcVerticalViewNewProcess.ResultData.Where(x => x.TabName == _section && x.StandardLineItem == lineitem).FirstOrDefault();
startrow = "E" + startrange;
endcolname = GetExcelColumnName(_colpos);
endrow = endcolname + endrange;
rangecoordinate = startrow + ":" + endrow;
_rangecoordinate.Add(rangecoordinate);
endcoordinate = GetExcelColumnName(_colpos + 1) + endrange;
var groupkeyvalue = sheet[endcoordinate].ToString();
var _colorcoordinate = "A" + startrange + ":" + endcoordinate;
_colorrangecoordinate.Add(_colorcoordinate);
// finding if weitage is 2
if (groupkeyvalue.Split('~')[5].Trim() == "2")
{
// if weitage is 2 then setting the row color red
range = sheet.Ranges[_colorcoordinate];
range.Style.BackColor = Color.Tomato;
}
// finding if weitage is 3
else if (groupkeyvalue.Split('~')[5].Trim() == "3")
{
// if weitage is 3 then setting the row color yellow
range = sheet.Ranges[_colorcoordinate];
range.Style.BackColor = Color.Yellow;
}
startrange = r + 1;
if (!String.IsNullOrEmpty(formattingdata.StandardValue))
{
sheet.SetRangeDataFormat(rangecoordinate, CellDataFormatFlag.Number,
new NumberDataFormatter.NumberFormatArgs()
{
// decimal digit places, e.g. 0.1234
DecimalPlaces = Convert.ToInt16(3),
// negative number style, e.g. -123 -> (123)
//NegativeStyle = _celldata.Contains("-") ? NumberDataFormatter.NumberNegativeStyle.RedBrackets : NumberDataFormatter.NumberNegativeStyle.Default,
NegativeStyle = NumberDataFormatter.NumberNegativeStyle.RedBrackets,
// use separator, e.g.123,456
UseSeparator = formattingdata.AllowComma,
});
}
if (!String.IsNullOrEmpty(formattingdata.CurrencySign))
{
sheet.SetRangeDataFormat(rangecoordinate, CellDataFormatFlag.Currency,
new CurrencyDataFormatter.CurrencyFormatArgs()
{
// decimal digit places, e.g. 0.1234
DecimalPlaces = Convert.ToInt16(3),
// negative number style, e.g. -123 -> (123)
//NegativeStyle = _celldata.Contains("-") ? NumberDataFormatter.NumberNegativeStyle.RedBrackets : NumberDataFormatter.NumberNegativeStyle.Default,
NegativeStyle = NumberDataFormatter.NumberNegativeStyle.RedBrackets,
// use separator, e.g.123,456
UseSeparator = formattingdata.AllowComma,
PrefixSymbol = formattingdata.CurrencySign
});
}
if (formattingdata.AllowPercentageSign)
{
sheet.SetRangeDataFormat(rangecoordinate, CellDataFormatFlag.Percent,
new NumberDataFormatter.NumberFormatArgs()
{
// decimal digit places, e.g. 0.1234
DecimalPlaces = Convert.ToInt16(3),
// negative number style, e.g. -123 -> (123)
//NegativeStyle = _celldata.Contains("-") ? NumberDataFormatter.NumberNegativeStyle.RedBrackets : NumberDataFormatter.NumberNegativeStyle.Default,
NegativeStyle = NumberDataFormatter.NumberNegativeStyle.RedBrackets,
// use separator, e.g.123,456
UseSeparator = formattingdata.AllowComma
});
}
startrow = "";
}
}
else
{
if (sheet["A" + (r)] != null)
{
startrange = r;
endrange = r;
_section = sheet["A" + r].ToString();
lineitem = sheet["B" + r].ToString();
var formattingdata = objQcVerticalViewNewProcess.ResultData.Where(x => x.TabName == _section && x.StandardLineItem == lineitem).FirstOrDefault();
startrow = "E" + startrange;
endcolname = GetExcelColumnName(_colpos);
endrow = endcolname + endrange;
rangecoordinate = startrow + ":" + endrow;
_rangecoordinate.Add(rangecoordinate);
endcoordinate = GetExcelColumnName(_colpos + 1) + endrange;
var groupkeyvalue = sheet[endcoordinate].ToString();
var _colorcoordinate = "A" + startrange + ":" + endcoordinate;
_colorrangecoordinate.Add(_colorcoordinate);
// finding if weitage is 2
if (groupkeyvalue.Split('~')[5].Trim() == "2")
{
// if weitage is 2 then setting the row color red
range = sheet.Ranges[_colorcoordinate];
range.Style.BackColor = Color.Tomato;
}
// finding if weitage is 3
else if (groupkeyvalue.Split('~')[5].Trim() == "3")
{
// if weitage is 3 then setting the row color yellow
range = sheet.Ranges[_colorcoordinate];
range.Style.BackColor = Color.Yellow;
}
if (!String.IsNullOrEmpty(formattingdata.StandardValue))
{
sheet.SetRangeDataFormat(rangecoordinate, CellDataFormatFlag.Number,
new NumberDataFormatter.NumberFormatArgs()
{
// decimal digit places, e.g. 0.1234
DecimalPlaces = Convert.ToInt16(3),
// negative number style, e.g. -123 -> (123)
//NegativeStyle = _celldata.Contains("-") ? NumberDataFormatter.NumberNegativeStyle.RedBrackets : NumberDataFormatter.NumberNegativeStyle.Default,
NegativeStyle = NumberDataFormatter.NumberNegativeStyle.RedBrackets,
// use separator, e.g.123,456
UseSeparator = formattingdata.AllowComma,
});
}
if (!String.IsNullOrEmpty(formattingdata.CurrencySign))
{
sheet.SetRangeDataFormat(rangecoordinate, CellDataFormatFlag.Currency,
new CurrencyDataFormatter.CurrencyFormatArgs()
{
// decimal digit places, e.g. 0.1234
DecimalPlaces = Convert.ToInt16(3),
// negative number style, e.g. -123 -> (123)
//NegativeStyle = _celldata.Contains("-") ? NumberDataFormatter.NumberNegativeStyle.RedBrackets : NumberDataFormatter.NumberNegativeStyle.Default,
NegativeStyle = NumberDataFormatter.NumberNegativeStyle.RedBrackets,
// use separator, e.g.123,456
UseSeparator = formattingdata.AllowComma,
PrefixSymbol = formattingdata.CurrencySign
});
}
if (formattingdata.AllowPercentageSign)
{
sheet.SetRangeDataFormat(rangecoordinate, CellDataFormatFlag.Percent,
new NumberDataFormatter.NumberFormatArgs()
{
// decimal digit places, e.g. 0.1234
DecimalPlaces = Convert.ToInt16(3),
// negative number style, e.g. -123 -> (123)
//NegativeStyle = _celldata.Contains("-") ? NumberDataFormatter.NumberNegativeStyle.RedBrackets : NumberDataFormatter.NumberNegativeStyle.Default,
NegativeStyle = NumberDataFormatter.NumberNegativeStyle.RedBrackets,
// use separator, e.g.123,456
UseSeparator = formattingdata.AllowComma
});
}
}
}
for (int j = 4; j <= numberOfDatColumns - 1; j++)
{
var groupkeycolumnname = GetExcelColumnName(_colpos + 1);
if (sheet[groupkeycolumnname + r] != null)
{
var gpkeyvalue = sheet[groupkeycolumnname + r].ToString();
var colname = GetExcelColumnName(j + 1);
if (gpkeyvalue != "")
{
if (gpkeyvalue.Split('~')[5].Trim() == "3")
{
if (sheet[colname + 1].ToString().ToUpper() != "GROUPKEY")
{
if (sheet[colname + r] != null)
{
if (sheet[colname + r].ToString() != "")
{
if (double.Parse(sheet[colname + r].ToString()) != 0)
{
//range = sheet.Ranges[colname + r];
//range.Style.BackColor = Color.Red;
sheet.Cells[colname + r].Style.BackColor = Color.Red;
}
}
}
}
}
}
if (sheet[colname + 1].ToString().Contains("FYCheck"))
{
if (sheet[colname + r] != null)
{
if (sheet[colname + r].ToString() != "")
{
if (double.Parse(sheet[colname + r].ToString()) != 0)
{
//range = sheet.Ranges[colname + r];
//range.Style.BackColor = Color.Red;
sheet.Cells[colname + r].Style.BackColor = Color.Red;
}
}
}
}
}
}
}
}
}
catch(Exception ex)
{
var errmsg = ex.Message;
}
i am using 3rd party reogrid for winform application. looking for suggestion. thanks
All replies (10)
Wednesday, December 19, 2018 3:06 PM ✅Answered
To be very clear here, tasking/threading will not speed up code in any way. In fact it'll slow it down. The only thing tasking/threading allows you to (potentially) do is work in parallel. This is where you will see performance gains. So you need to evaluate your code and determine if any portion of it can be run in parallel. If the answer is no then you aren't going to be able to break out work across threads. The next question would be what work you can eliminate. Ultimately if everything you're doing is needed then you have no choice but to do it and so you'll take the hit.
Looking at your code the thing that stands out to me is the for loop where you're calling back to the UI thread. Each call to the UI thread is going to slow things down and you're doing it once for each row. I would recommend that you instead use data binding and have that value bound automatically when the data table you're working with is bound to the grid. Using a virtual grid would speed things up if you have a lot of rows. Furthermore you are mixing a BWC with BeginInvoke which is almost always wrong. To update the UI from a BWC use the progress indicator. This triggers an event on the UI which allows you to update the UI. Will this speed up your code? Most likely not but it is more consistent with how BWC works.
I think you're going to have to hook the profiler up to your code to see what is taking too long. It can be anywhere in your code so you're going to have to do focused optimizations. Ultimately you may find there is nothing that is taking any amount of time and it is just the # of rows involved. Again using virtual grid would help with that. Additionally trying to do as much work in memory before binding to the grid tends to also speed things up. UI interactions are always serialized so anything that doesn't require going back to the UI is good.
Michael Taylor http://www.michaeltaylorp3.net
Wednesday, December 19, 2018 9:40 PM ✅Answered
class Program
{
static void Main ( string[] args )
{
var values = new MyData[100];
for (var index = 0; index < values.Length; ++index)
values[index] = new MyData() { Id = index };
var results = Parallel.ForEach(values, DoWork);
while (!results.IsCompleted)
System.Threading.Thread.Yield();
Console.WriteLine("Done");
Console.ReadLine();
}
static void DoWork ( MyData data )
{
//Do something lengthy
System.Threading.Thread.Sleep(500);
Console.WriteLine(data.Id);
}
}
class MyData
{
public int Id { get; set; }
}
It can only get more complex from there. The biggest thing to pay attention to is that if you're going to run this stuff in parallel then you cannot rely on shared data (aka arrays, non-thread safe types, etc). Otherwise you'll have parallel threads overwriting the work of other threads. It can be tricky if you're not careful. Unfortunately you won't know about those scenarios until you try to apply it to your actual code.
"can i use Parallel for loop inside backgroundWorker1_RunWorkerCompleted event"
You can but RunWorkerCompleted runs on the UI thread so you'll be triggering the parallel work on the UI thread which defeats the purpose of using BWC. Just do the parallel work on the UI thread without bothering with BWC.
Michael Taylor http://www.michaeltaylorp3.net
Tuesday, December 18, 2018 9:18 PM | 1 vote
If you are working with the UI then you aren't going to be able to do much optimizing in terms of threading or async because all the work has to happen on the UI thread. I would recommend that you modify your app to do the heavy work before updating the UI. Then you can move the heavy work to a worker thread. Doing most anything with the UI is going to be slow.
Another alternative is to use a virtual grid. Unfortunately you're using a third party component so you'll have to talk to them about what is possible there.
Finally you can use a profiler to determine what is taking the most time in the code you posted. Then try to optimize or remove it from your code.
Michael Taylor http://www.michaeltaylorp3.net
Wednesday, December 19, 2018 9:40 AM
my above code will execute under background worker. could it be a issue for which loop taking time ?
Wednesday, December 19, 2018 7:09 PM
sir you said.... Furthermore you are mixing a BWC with BeginInvoke which is almost always wrong. To update the UI from a BWC use the progress indicator. This triggers an event on the UI which allows you to update the UI. Will this speed up your code
please share a small sample code which show me how to update UI from BWC using progress indicator. also like to know how to trigger event on UI which allow to update UI. i am using background worker component in winform project.
share a good article which would show me how to use profiler to see which area in code taking long time. i am using VS2013 IDE.
looking for reply. thanks
Wednesday, December 19, 2018 7:21 PM
You can see a full example of how to update the UI in MSDN here. The backgroundWorker1_ProgressChanged handler is called on the UI thread whenever the BWC triggers it. This method can access the UI. All you have to decide is how to get the data there. We generally use a simple type for one value and a struct for multiple values. The main form (or whoever cares about the event) would associate the handler with the ProgressChanged event on the BWC (generally via the designer). To allow the events to be raised set the BWC's WorkerReportsProgress property to true (again, generally via the designer). That's all you have to do to update the UI.
For profiling you can use a number of tools. If you have VS 2017 Enterprise then use IntelliTrace as documented here. If you need detailed logging then refer to the guide in MSDN. Some extension, like CodeRush, can also do it if you already have them. For a quick and dirty profiling session around a block of code I would just use Stopwatch. MSDN has an example of how to wrap some code to see how long it takes to run. This is very useful for gut checking performance.
Michael Taylor http://www.michaeltaylorp3.net
Wednesday, December 19, 2018 7:49 PM
what VS2013 has for profiler option ?
one more question when we do heavy intensive task like huge iteration in for loop and nested for loop. in this situation if i use Parallel.For instead of for loop inside backgroundWorker1_DoWork event does it speed up my job.....does it make sense to use Parallel.For inside backgroundWorker1_DoWork event ?
please guide me.
Wednesday, December 19, 2018 7:59 PM
For VS2013 you're going to be limited to Stopwatch. Most extensions aren't going to support that far back and the newer features aren't available.
It doesn't make sense to use BWC in combination with Parallel in most cases. Parallel is going to spawn separate threads to do the processing. BWC is doing the same thing so you're spawning a thread just so it can spawn other threads. BWC brings the ability to update the UI that Parallel cannot do. So if you had a bunch of work that needed to be done in parallel then just use Parallel.
My recommendation is to create a service class that is responsible for doing your parallel work. This class can use parallel (or whatever) to do the work async. When it completes then you can update the UI by using BWC (or similar).
Michael Taylor http://www.michaeltaylorp3.net
Wednesday, December 19, 2018 8:16 PM
can u please provide code example which show me how to use Parallel for to do bunch of work that needed to be done in parallel ? please provide example code bit real life scenario where multiple job is executing Parallel of code or give me some link.
Wednesday, December 19, 2018 8:20 PM
can i use Parallel for loop inside backgroundWorker1_RunWorkerCompleted event ? does it make sense ?