Help to tune the Linq Query for comparing multiple columns which taking 45 mins to execute so kindly help to tune the Query

Help StudioX
, , ,
1

Description

Iam using 3 datatable and comparing 3 columns which are != (NotEqual) with both Datatable and getting the desired Output but Query taking 45 mins to execute due to huge data.Kindly help to tune the Query to execute faster.

DT1 - dt_calero_final_iphone with 180000 records
DT2 - DT_MatchedDelta with 40000 records
DT3 - DT_UnMatchedDelta with 80000 records

QUERY:
(From row In dt_calero_final_iphone.AsEnumerable()
Where Not DT_MatchedDelta.AsEnumerable().Any(Function (r1) r1(“Calero Phone #”).ToString.Trim = row(“Mobile Number”).tostring.Trim) _
AndAlso Not DT_UnMatchedDelta.AsEnumerable().Any(Function (r2) r2(“Calero Phone #”).ToString.trim = row(“Mobile Number”).tostring.Trim)
Select dt_calero_final_iphone.Clone().LoadDataRow({row(“Serial Number -Device”),row(“Mobile Number”),row(“Employee number”)}, False)).CopyToDataTable()

Help to tune the Linq Query for comparing multiple columns which taking 45 mins to execute so kindly help to tune the Query
2

Hello @ppr I know you’re busy, but if you could spare a few minutes, it would mean a lot but I feel I’m missing something your experience would help me a lot to tune this Query to execute faster

3

Instead of using LINQ, you could write the data into a Excel and treat Excel as a database.

This approach might be more efficient, as using Excel as a database is generally faster.

4

@aravinthan.k

Firstly we see 3 datatables instead of two..are those two datatables have same columns? If yes then you can first use merge datatable and then do linq..which reduces a lot

Cheers

5

Yes 3 datables only if we merge we cant get the unmatching columns the above qiery is working but taking more time due to more records I have sort the datables before passing to linq some what time reduced and tring to tune the query

6

@aravinthan.k

May I know why you cant get unmatched?

You can merge the matched and unmatched dt and then use join datatable with full join on first table and filter with empty values in the resultant table to get matched..non empty are matched rows

Cheers

7

Modifying above approach to a hashset of String / tuple(of String…) may can Speed Up the execution time

1 Like
8

I understand the requirement that you need records from “dt_calero_final_iphone” not found in both “DT_MatchedDelta” and “DT_UnMatchedDelta”

So you could merge “DT_MatchedDelta” and “DT_UnMatchedDelta”

  • if “Calero Phone #” is in one of them it will be in merged DT
  • if “Calero Phone #” is not in any of them it will not be in merged DT

Then you could compare just “dt_calero_final_iphone” and the merged DT.

Cheers

9

Description

Iam using 2 datatable and compare 3 not equal columns with 2 table and getting the desired Output but Query taking 45 mins to execute..Kindly help to tune the Query.

DT1 - dt_calero_final_iphone with 180000 records
DT2 - DT_MatchedDelta with 400000 records

QUERY:
(From row In dt_calero_final_iphone.AsEnumerable()
Where Not DT_MatchedDelta.AsEnumerable().Any(Function (r1) r1(“Calero Phone #”).ToString.Trim = row(“Mobile Number”).tostring.Trim) _
AndAlso Not DT_UnMatchedDelta.AsEnumerable().Any(Function (r2) r2(“Calero Phone #”).ToString.trim = row(“Mobile Number”).tostring.Trim)
Select dt_calero_final_iphone.Clone().LoadDataRow({row(“Serial Number -Device”),row(“Mobile Number”),row(“Employee number”)}, False)).CopyToDataTable()

Link

Date

2025-09-13

Related UiPath products

IT Automation
Studio
Studio Web
StudioX

10

we already adressed above by:

11 
' Build exclusion set once'
Dim excludePhones As New HashSet(Of String)(
    DT_MatchedDelta.AsEnumerable().Select(Function(r) r("Calero Phone #").ToString().Trim).
    Union(DT_UnMatchedDelta.AsEnumerable().Select(Function(r) r("Calero Phone #").ToString().Trim))
)

' Filter rows efficiently'
Dim result As DataTable = dt_calero_final_iphone.Clone()

For Each row In dt_calero_final_iphone.AsEnumerable()
    Dim phone As String = row("Mobile Number").ToString().Trim
    If Not excludePhones.Contains(phone) Then
        result.ImportRow(row)
    End If
Next
12

files = (
From f In System.IO.Directory.GetFiles(folderPath, “abc_*.xlsx”, System.IO.SearchOption.TopDirectoryOnly)
Let fileName = System.IO.Path.GetFileName(f)
Where Not fileName.StartsWith(“~$”)
Let name = System.IO.Path.GetFileNameWithoutExtension(f)
Let parts = name.Split("_"c)
Where parts.Length = 3 AndAlso System.Text.RegularExpressions.Regex.IsMatch(parts(2), “^\d+$”)
Let numPart = Integer.Parse(parts(2))
Order By numPart Ascending
Select f
).ToArray()