There is no need to try to avoid For Each loops on large amounts of data

[postwick (Paul Ostwick)]

I’ve seen the discussion many times about avoiding For Each (Row) because looping through a lot of rows is inefficient.

So I wrote a test. It creates a datatable of however many rows we want (Name, Age, City). Default values are Mary, 28, Dallas. Every 10th row is Tom, 27, Miami. Goal is to update Tom to Boston.

The first method is simply a single For Each with nested Assign to change Tom’s City to Boston.

This is the method of filtering into two separate DTs so you have one DT that’s just Tom’s rows, then remove Tom from the main dt, then For Each Row to update Miami to Boston, then merge. This method only has to For Each Row through 10% of the rows.

I had to go to 1 million rows before any meaningful processing time difference appeared. Here is the result of 1 million rows:

Here is 5 million rows:

The filter method is significantly slower, as the first filter has to process all 5 million rows, then you update 500,000 rows, then merge 5 million rows.

Also, the speed at which a single For Each Row is processing millions of rows should indicate all this concern over finding other more efficient methods is pointless. For Each Row is very fast.

[jeevith (Jeevith Hegde, Ph.D.)]

Hi @postwick,

Standard UiPath activities are quick enough for most automations. There probably is a linq query in the code which UiPath uses as well. So to see many people avoid using standard loop activities to me is something which I cannot understand.

It would be great if you can share your .xaml files and we could request @Yoichi or @ppr to help us find how much faster linq queries can be on the same data.

Null Hypothesis : Linq query is 25% faster
Alternative Hypothesis : There is negligible difference

[postwick (Paul Ostwick)]

It’s just Build Datatable with 3 columns: Name (str), Age (int32), City (str)

Then Repeat activity to populate with however many rows you want.

Then they can try the Linq query, I’d be interested in seeing how that does. Of course, we’d have to keep in mind differences in the computers we are using. If someone could just tell me the Linq query, I’ll add it to my tests and get the numbers.

[jeevith (Jeevith Hegde, Ph.D.)]

Agreed based on the system resources this test results will differ. Lets hope someone who is good at linq can help with an optimized query so that we can test this out objectively.

But just for the sake of completeness, you could add the .xaml file, people who want to try it will save some time setting it up.

Thank you for starting a discussion on this topic.

[postwick (Paul Ostwick)]

Here’s the XAML for anyone who wants it.

LoopTesting.xaml (20.7 KB)

[ppr (Peter Preuss)]

@postwick thanks for shifting this discussion topic to facts
@jeevith thanks for your involvment

I tried to avoid ongoing discussions from the past as the problem is not LINQ, For Each Defects or dramatic performance issues. It was more about misstaking Learning topics, mixing up LINQ benefits along with no present defects of other options. Also it was ignoring the nature and misuse of LINQ.

However as there are some topics added to the Blog/Tutorial pipeline, a preview already was released (LINQ vs Loops like for, for each and while - #2 by ppr), so its ok to step in within this discussion as an made exception.

First of all: Nothing is wrong, bad or else using For each … Activity

#Case 1

first of all it will not update every fith row:

it will update every record where current item last digit is a 5

Updating every five rows will be done with a mod check

Doing task 1 with a LINQcould look like below. Kindly note: LINQ Suggestion is one of many option

ra1 = {"Mary", 28, Dallas}
ra2 = {"Tom",27, Miami}

dtData =
(From x in Enumerable.Range(1,1000000)
let ra = If(x Mod 5 = 0, ra2, ra1)
Select r=dtData.Rows.Add(ra)).CopyToDataTable

Here we only use CopyToDataTable for psychological reasons and it is not needed

Case 2:

is similar to for each row, filter datatable activities (losing reference to original datatable etc)
approaches, but can be avoided by following:

• feed with LINQ the already filtered rows into a for each (and do not use for each row)
• update City
  

And thats all, just move only the needed rows

Case 3:
is done with Case 2 and a split and merge datatable can be avoided

[postwick (Paul Ostwick)]

Sorry yes I fixed the “every 5th” in another post and forgot to fix it here. It’s every 10th. It doesn’t need to be every 5th, it could be any ratio.

[ppr (Peter Preuss)]

thats absolutely fine and will not bring out an oposite result.
But when we do the discussions we would do it with matching code and description

[ppr (Peter Preuss)]

Cross References

LINQ vs Loops like for, for each and while

@Surya_Narayana_Korivipadu Just a few thoughts on this topic (preview of a howTo topic that is currently in preparation) Task: update data columns in general, we should start with a for each (sometimes also for each row) activity. We can combine it with some LINQ in case we want to filter in advance to reduce the number of rows. If for any reason we want to do it with an invoke code activity we should mirror the for each activity instead of writing LINQ complexes. But for sure we can combine…

Update specific column for specific row value without using for each

I want to update a specific column value for the specific row (same value with more than one row) in excel

Replace blank column value with specific value using linq

we would suggest to do it with a for each row activity loop. For learning purpose a LINQ could look like this For Each row As Datarow In dt.AsEnumerable().Where(Function (r) IsNothing(r("Column1")) OrElse String.IsNullOrEmpty(r("Column1").toString.Trim)) row("Column1") = "Undefined" Next used within Invoke Code Here we only do use LINQ for the filtering and do mirroring the for each activity on code. Updating is stressing the LINQ concept and results quickly to avoidable black box approac…

LINQ query to replace column datatable in uipath

@MarkusDS Feel free to open a seperate Topic on it or reach me by message. The most important factor on an implementation is maintainability and that there is a chance to track the processing. Using a Linq in an Assign statement is forcing that the LINQ Statement is returning something (L-Value assignment). Updating a column will fail: ) dt.AsEnumerable.Where(Function ( r ) r.Field(Of Int32)(0) < 2).Select( <- return is needed ) other tricks e.g. return someting and do col update will not v…

How to Update Data Column Values of a Data Table | Community Blog

[postwick (Paul Ostwick)]

The cross references are good, although the second one is where I first posted the above tests.

The point to my test is performance related. The test proves For Each Row is not slower than the method proposed to avoid it. It’s much faster. And in either event, For Each Row can process 5 million rows in a matter of seconds so worrying about performance is pointless.

[ppr (Peter Preuss)]

@ppr

as the problem is not LINQ, For Each Defects or dramatic performance issues. It was more about misstaking Learning topics, mixing up LINQ benefits along with no present defects of other options. Also it was ignoring the nature and misuse of LINQ.

@ppr

First of all: Nothing is wrong, bad or else using For each … Activity

maybe you reread the post and links again and will pause a while

[postwick (Paul Ostwick)]

I didn’t disagree with you. As your references point out, there are other considerations besides performance when deciding how to achieve a goal. My test was simply looking at processing time for a specific example.

[jeevith (Jeevith Hegde, Ph.D.)]

Hi @ppr ,

Thank you for taking time to give your inputs on this test. I have not read all the linked reference posts yet, but will do in the coming days.

I completely agree with you here about avoiding magical blackboxes. Not everyone can be a magician in other words if a complicated Linq query does great with robot execution time but when the code has to be maintained by someone who does not understand the blackbox, it can be quite a challenge and can lead to downtime.

https://forum.uipath.com/t/linq-vs-loops-like-for-for-each-and-while/341344/2

• avoid BlackBoxes :
  • a LINQ statement is Pass or Fail but if its failling, then we also need to find out what is going wrong.
  • Apply thechniques that will support you on issue analysis
  • Combine essential approaches with LINQ (e.g. Grouping data with LINQ, processing the group members with essential methods

[eeskildsen (Eric Eskildsen)]

The only area where I’ve seen a performance issue with For Each is in Debug mode.

Example: Iterate over 1,000 integers.

• Debug: 248 seconds
• Release: 4 milliseconds

Something to be aware of for those who need to debug a workflow that loops over a large set.