How to Optimize Join of Two DataTables Using LINQ In C#

Question

Hi All Currently query is working fine but during high volume data it is taking hours and I need to optimize this query only (Means cannot park the code in sql command) Example query : //joining Product and Tax DataTable var JoinResult = (from p in dt.AsEnumerable() join t in dtTax.AsEnumerable() on p.Field (Tax Id) equals t.Field (Tax Id) select new { ProductName = p.Field (Product Name) BrandName = p.Field (Brand Name) ProductCategory = t.Field (Product Category) TaxCharge = t.Field (Charge) }).ToList(); Regards Shehzad

Anupam Maiti · Answer

Let's try your query by using AsParallel() extension method to make the query run in parallel. However this method can only be used when the source sequence is of type IEnumerable or IQueryable Here is the updated code for you. var JoinResult = (from p in dt.AsEnumerable().AsParallel() join t in dtTax.AsEnumerable() on p.Field (Tax Id) equals t.Field (Tax Id) select new { ProductName = p.Field (Product Name) BrandName = p.Field (Brand Name) ProductCategory = t.Field (Product Category) TaxCharge = t.Field (Charge) }).ToList(); NOTE : If you feel that still it is not working properly you can try below options You can use the AsCached() extension method to enable the query cache for a DataTable. Instead of joining two DataTables if you can create a HashSet or Dictionary of one DataTable and use it to lookup values in the second DataTable. This can be faster than a join operation especially when the second DataTable is small. Exmaple of using HashSet. // Create the first DataTable DataTable firstTable = new DataTable(); firstTable.Columns.Add(ID typeof(int)); firstTable.Columns.Add(Name typeof(string)); firstTable.Rows.Add(1 Anupam M); firstTable.Rows.Add(2 Test Name); // Create the second DataTable DataTable secondTable = new DataTable(); secondTable.Columns.Add(ID typeof(int)); secondTable.Columns.Add(Age typeof(int)); secondTable.Rows.Add(1 30); secondTable.Rows.Add(2 56); // Create a HashSet of the first DataTable's ID column HashSet firstTableIDs = new HashSet (firstTable.AsEnumerable().Select(r => r.Field (ID))); // Use the HashSet to lookup values in the second DataTable var result = secondTable.AsEnumerable() .Where(r => firstTableIDs.Contains(r.Field (ID))) .Select(r => new { ID = r.Field (ID) Age = r.Field (Age) }); Hope this will help you.

Shehzad Khan · Answer

Hi Sachin Singh As mentioned This query is a part of existing program/design and my challenge is to optimize this query. Or Any other approach to join in memory datatables with better performance. Thanks

Sachin Singh · Answer

That is the reason i prefer Dapper over EF. However you can call stored procedure with EF too. So any specific reason of not using SPs?

Shehzad Khan · Answer

Hi @Anupam Maiti Thanks for example HashSet is getting records from one datatable only. I will be needing data from both datatables is there possiblity to get records from both data tables during HashSet approach

Anupam Maiti · Answer

Example added in Original response. DONT FORGET TO ACCEPT MY ANSWER  IF IT WORKS FOR YOU.

Shehzad Khan · Answer

Hi @Anupam Maiti Thanks for sharing can also provide a example for below -- Instead of joining two DataTables if you can create a HashSet of one DataTable and use it to lookup values in the second DataTable. This can be faster than a join operation especially when the second DataTable is small. -- Hi @Jayraj Chhaya Thanks for sharing Let me explore

Shehzad Khan · Answer

Hi All I am now stuck at HashSet compound column search. I have attach sample program wtih sample data. -- var result = firstTable.AsEnumerable() .Where(r => secondTableKeys.Contains(((compondkey) new compondkey { ID = r.Field (ID) Code = r.Field (CODE) }))) .Select(r => new { ID = r.Field (ID) Name = r.Field (Name) }).ToList() ; --

Shehzad Khan · Answer

Hi Anupam Maiti HashSet is showing more than 50% improvement in test query with test data. However now my challenge will be to work with compund columns check in HashSet. Thanks for sharing! Regards Shehzad

Jayraj Chhaya · Answer

You also have to add an index in Tax Id ProductName BrandName ProductCategory and TaxCharge.