Linq query taking too long

Help
, ,
1

Hello guys.
Please help me in optimising the query below or suggest to me an efficient way to remove matching records from two different data tables. The below Linq query is taking 60 minutes for 50 000 transactions. Is there a way to speed it up or use a different query or activity all together

dt1.AsEnumerable.Where(Function(row) dt2.AsEnumerable.Count(Function(r) r(6).ToString+ r(7).ToString = row(6).ToString+ row(7).ToString )=1 ).CopyToDataTable()

Thanks in advance

2

On what condition, you was remove matching record.
Can you explain and share both demo excel file.

Regards
Sagar

3

you can try to put your condition on Filter data table activity

4

Good day,

I hope you are well.

Do you perhaps have the option of moving the data to a database and reading the data from there instead? Database technologies are really good at dealing with the manipulation of large amounts of data in a timely manner. We have used SQL Server tables and views to achieve something similar. The Join Data table activity and LINQ queries took us 5 hours to run in UiPath. When we moved the data to SQL and read the data from the view instead it took 3 minutes.

Hope this helps.

5

Hello, Thank you for your response :grinning:

Screenshot 2020-11-26 at 15.54.40
Screenshot 2020-11-26 at 15.54.40744×304 32.7 KB
!
Gusa
Gusa742×310 26.4 KB

We have the two table above. The result of the linq would be row 2 and 3 as they are showing the two rows where the RRN and Amt don’t match.

6

image
image749×308 33.7 KB

image

Means From both table, you want all highlighted data
and condition either one of data column not same or all are not same

Am i right?

7

The filter activity is taking longer than the linq query

8

dt1.AsEnumerable.Where(Function(row) dt2.AsEnumerable.Count(Function® r(6).ToString+ r(7).ToString = row(6).ToString+ row(7).ToString )=1 ).CopyToDataTable()

Running the above query would give row 2 and 3 from the first table

dt2.AsEnumerable.Where(Function(row) dt1.AsEnumerable.Count(Function® r(6).ToString+ r(7).ToString = row(6).ToString+ row(7).ToString )=1 ).CopyToDataTable()

Running the above query would give row 2 and 3 from the second table.

9

Main.xaml (11.5 KB)

Try to use this :slight_smile:

Here dtfirst is your first datatable
and dtsecond is your datatable from which you are going to compare common value
and in dtoutput you will get only not maching record of dtfirst table from dtsecond.

Let me know if any doubt
and i hope it will take less time

Happy Automation!

1 Like
10

Thank you so much, let me try it out and give you feedback

11

The solution works! but its taking longer than the linq query. Thank you for your help.

12

@Charmaine_Matsaudza Can you check if this Linq is giving you a better execution time.
Datatable1_MatchedRecords.xaml (19.4 KB)

1 Like
13

Hi @supermanPunch . Thank you for response. I have tried using the query you sent, but its taking as long as the initial one. I guess maybe there is no way to optimize the process. Thank you so much for your help