Linq Query optimisation

Help
, ,
1

Good day,
Anyone to assist me in optimising the query below or suggest to me an efficient way to remove matching records from two different datatables. The below Linq query is taking 60 minutes for 50 000 transactions

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

2

Are you running the query over a single datatable with 50.000 rows or are you running the query 50.000 times?

Because for the latter it seems quite reasonable.

3

Im running over a single datatable with 50000 rows.

4

Correct me if I’m wrong, but you want to get a result with rows for which column 6 and 7 matches exactly one row in datatable2, right?

So for every row in dt1, you count the rows with the same values in column 6 and 7 in dt2 and if it’s only one row, it should be added to your result table.

So you are running the query

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

50.000 times.

This is why it takes so long.

To be honest, I’m not sure if this can be optimized at all… My queries, when run 50k times, take a long time as well.

Hopefully someone more knowledgeable comes along because I can’t help you :frowning:

Good luck!