Efficient Data Table Query

Help
1

Hello,

I have a working process to loop through two datatables and if the row of DT1 = row DT2, then assign the variable to a new DT3

The files I’m working with are rather large, approximately 100k and 2k rows. The process takes about 20 minutes to run and essentilly performs a “VLookup” between the two datatables.

I’ve searched the forums on alternative solutions, however, there are a ton of different options and I’m unfamiliar enough with them to make an educated assessment. Can anyone please provide their input on how to make my process more efficient?

My tables are listed below.

DT1
UniqueName, Email Address

DT2
UniqueName, PO#

DT3 - Output
UniqueName, PO#, Email Address

Thanks in advance!

2

Buddy @bradsterling
you can use join datatable activity in this case where you can join two datatable with a single column as a joining criteria, which would make it faster when compared with for each row and if condition
for more info on join datatable activity kindly have a view on this buddy @bradsterling

For example

Kindly try this and let know whether this works or not
Cheers @bradsterling

1 Like
3

When using DataTable, we generally use Find or Select method for searching specific data row.

Find method uses primary key and index, so it’s fastest. But if the filter condition is not in primary key column, there is no option but we have to use Select method or DataView.RowFilter .

Geneally we know DataTable.Select is slower than Find, because DataTable.Select scans all the records.

In here, I’ll explain how to increase DataTable.Select performace.

For the performance test, create sample DataTable instance. it has ID primary key. column.

DataTable Table = new DataTable();

[TestInitialize]
public void Init()
{
var idColumn = Table.Columns.Add(“ID”, typeof(int));
Table.Columns.Add(“ParentID”, typeof(int));
Table.Columns.Add(“Name”, typeof(string));

for (int i = 0; i < 3000; i++)
{
    Table.Rows.Add(i, (int)Math.Floor((double)i / 10), i.ToString());
}

Table.PrimaryKey = new DataColumn[] { idColumn };
Table.AcceptChanges();

}

To know more about this visit at: MS SQL Programming

1 Like
4

Hi @Palaniyappan,

I’m using the Join Activity but there are no results. I referenced the UiPath documentation and want to perform an inner join.

I have a DT1 with 100k rows and am joining a DT2 with 2K rows. Would there be any issue with the Join activity finding the exact row in DT2 to join that the specific line of DT1?

5

No @bradsterling
it wont find any difficulty in joining them…that would work actually
what was the issue you were facing buddy
Cheers @bradsterling

1 Like
6

@Palaniyappan I deleted my original and started over and it worked. Not sure what I had wrong in the first try. Thanks for your help!

1 Like
7

Fantastic
Cheers buddy @bradsterling
Keep going

1 Like
8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.